MySQL Learning Note VII: Backup and recovery of data

We know that data is at the heart of an enterprise IT architecture, and in order to prevent data loss or some other special purpose for some accidental reasons, it is especially important to make backups of the data at the usual times.

First, why to back up

1. Disaster recovery: Hardware failure, software failure, natural disaster, hacker attack, mis-operation, etc.

2, Audit: sometimes need to know what the data in the past a point of time what is

3. Testing: One of the simplest methods to test the actual data is to update the test server regularly with the latest production environment data, as long as the backup files are restored to the test server

Ii. key points to be aware of for backup and recovery

1, can tolerate the loss of how much data

2. How long does it take to complete the recovery?

3. What to restore

Third, Backup type:

1. Full Backup and partial backup

Partial backup: Backs up only one or more of the tables;

2. Full backup, incremental backup, differential backup

Incremental backup: Backup for all modifications made after any type of last backup

Differential backup: A backup of all changes made after the last full backup

For example, with a full backup every Sunday and a Monday backup of all changes since Sunday, there are two options in Tuesday: Back up all changes since Sunday (difference), or just back up all changes (increments) since Monday.

3, hot backup, warm backup and cold backup

Hot backup: Online backup, read and write operation is not affected; "InnoDB MVCC"

Warm backup: Online backup, read operation can continue, but the write operation is not allowed;

Cold backup: Offline backup, database server offline, during backup can not provide read and write services for the business;

InnoDB support hot-standby, MyISAM can only support to Win Bei

4. Physical Backup and logical backup

Physical Backup: Copy the backup of the original file directly, and it recovers much faster than the logical backup

Advantages:

Logical backup: A backup of the "Export" Data saved (for a text file) from the database;

Advantages:

① is not related to the storage engine because it is generated from the data extracted from the MySQL server, eliminating the difference in the underlying data store

② enables flexible backup definitions with mysqldump

③ can use text processing tools (such as Sed,awk) to perform two processing of exported text files;

④ recovery process is simple, directly into the MySQL program can be

⑤ enables backup and recovery over the network

Disadvantage: The backup file may be larger than the original file, compression can solve the problem, there is no guarantee that the recovered data is identical to the original, the recovery from the logical backup requires MySQL load and interpret statements, converted to storage format, and rebuild the index, so these are slow

Iv. factors to consider when backing up a rule:

Duration of the lock, length of backup process, backup load, duration of recovery process

Five, backup what

Data, additional data (transaction logs for binary logs and InnoDB), code (stored procedures and stored functions, triggers, event schedulers, etc.), server configuration files

Six, design Backup solution: Full backup + incremental backup;

Vii. Backup Tools

Mysqldump: Logical Backup tool for all storage engines, Win Bei, full backup, partial backup, hot standby for InnoDB storage engine (implemented using MVCC mechanism), but not practical;

CP, Tar,rsync and other file system Tools: Physical Backup tool for all storage engines, cold standby, full backup, partial backup;

LVM2 snapshot: Almost hot standby, with the help of file system tools to achieve physical backup;

Xtrabackup: MySQL database backup tool provided by Percona, an open source tool for hot provisioning of InnoDB and XTRADB databases

Mysqlhotcopy: almost cold; only applicable to MyISAM storage engine;

Eight, backup mode and the choice of backup tools:

1. Logical Backup

⑴ Logical Backup tool: Mysqldump (Common), Mydumper, PhpMyAdmin

⑵ Common backup method: Mysqldump+binlog

Make a full backup with mysqldump, and make incremental backup by Mysqlbinlog exporting the added binary log;

For example:

Mysqldump--lock-all-tables--all-dabases--flush logs-u bkpuser-h 192.168.30.10 >/backup/allbac-' Date +%f '. SQL #完全 Backup

mysqlbinlog/mydata/data/mysql-bin.000003 >/backup/incre-' Date +%f '. SQL #增量备份

⑶mysqldump: Logical Backup tool for all storage engines, Win Bei, full backup, partial backup, hot standby for InnoDB storage engine (implemented using MVCC mechanism), but not practical;

Mysqldump the database in the MySQL server as a standard SQL language and saves it to a file. It is a client-side tool that connects to Mysqld via the MySQL protocol.

① three ways to use:

mysqldump [Options] db_name [tbl_name ...] : Backing up a single library, or one or more tables in a library

mysqldump [options]--databases db_name ... : Backing up one or more libraries

mysqldump [Options]--all-databases: Backing Up all libraries

②mysqldump db_name and mysqldump--databases db_name The difference between the two usages:

The latter inserts the CREATE database if not EXISTS db_name and use db_name in the output before each new database, and the former does not, so if you backed up in the previous way, you need to create the library manually before you restore

③ Common options:

-A,--all-databases: backing Up all databases

-B,--databases db_name1 db_name2 ... : Backing up the specified database

-X,--lock-all-tables: Lock All tables

-L,--lock-tables: When backing up multiple databases,--lock-tables locks the table separately for each database. Therefore, this option does not guarantee logical consistency between the tables in the backup file and the database. Only recommended when backing up a single table

--single-transaction: Start a large single transaction implementation backup; The storage engine for the table to be backed up must be InnoDB, which is only valid. Hot spares, through the MVCC mechanism of InnoDB

--tables: Overwrite---database or-b option. All parameters after the option are treated as table names

Example: mysqldump--lock-all-tables--databases testdb--tables Students

-C,--compress: Compressed transfer, increased CPU load

-u,--user: Default is Root

-H,--host: localhost by default

-P,--password

④ Other options:

-F,--flush-logs: Scrolls the log after the table is locked, this option requires Reload permissions; This option is recommended for incremental backups.

-E,--events: Back up the event scheduler for the specified library;

-R,--routines: backup stored procedures and storage functions;

--triggers: Backup Trigger

--master-data[=#]: Record the location of the binary event where the backup moment was made in the backup file

1: Record change MASTER to statement; This statement is not commented;

2: Record as comment statement;

2. Physical Backup

⑴xtrabackup: Support for InnoDB, full backup and incremental backup, Win Bei for MyISAM engine, full backup

⑵ uses CP, Tar,rsync and other file system tools to copy files directly: For all storage engines, cold standby, full backup, partial backup;

⑶LVM2 snapshot: Almost hot standby, physical backup with file system tools provided that the original data is on a logical volume

Full backup:

① requests Lock All tables:

Mysql> FLUSH TABLES with READ LOCK;

② recording binary log files and event locations or scrolling logs

mysql> SHOW MASTER STATUS;

Or mysql-e ' Show Master status ' >/backup/pos

or MySQL > FLUSH LOGS;

③ to create a snapshot:

Lvcreate-l size-s-P r-n name/dev/vg_name/lv_name

④ Release Lock:

Mysql> UNLOCK TABLES;

⑤ mount the snapshot volume and copy the data for backup;

command to copy data from CP, rsync, TAR, etc.

⑥ Deleting a snapshot volume after the backup is complete

Incremental backup: Use Mysqlbinlog to export the added binary log

Nine, Xtrabackup

Xtrabackup is a MySQL database backup tool provided by Percona, an open source tool for hot provisioning of InnoDB and XTRADB databases.

Xtrabackup support for InnoDB, full backup and incremental backup, Win Bei support for MyISAM engine, full backup

1. Features:

① backup process is fast and reliable;

② The backup process does not interrupt the transaction being performed;

③ can save disk space and traffic based on functions such as compression;

④ Automatic implementation of backup inspection;

④ fast restore speed;

2, Xtrabackup Backup principle

We know that InnoDB will maintain a redo log file (that is, the transaction log files), which records the modification of each INNODB table data.

Xtrabackup will open a log-copying thread in the background that will monitor the redo log file and turn the slave LSN (xtrabackup from redo log at startup). The file that gets the most recent checkpoint corresponding to the log sequence number) begins to increase the number of chunks (logging) that are constantly copied to the files called Xtrabackup_logfile. This operation will continue throughout the backup process, which is why xtrabackup can make incremental backups and hot spares for InnoDB tables

After the backup is complete, the data cannot be used directly for the recovery operation because the data in the backup may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file, and there may be changes to the data during the backup process, at which point the xtrabackup_logfile can come in handy. Xtrabackup will use the file to commit the transaction, but the data is not yet written.
Redo Redo; The data file is already written to, but uncommitted transactions are rolled back by undo. This feature is implemented by using the--apply-log option of the Innobakupex command, and a transaction is not committed in the previous backup, but may be committed in a subsequent backup, in which case

2. Installation:

The latest version of the software can be obtained from http://www.percona.com/software/percona-xtrabackup/

Yum-y Install percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

After installing Xtrabackup, several tools are generated:

Innobackupex: This is actually a Perl script package for the following three tools, which can be backed up MyISAM, InnoDB, xtradb tables. However, a global read lock is required to handle the MyISAM table. Typically use this tool for backup

Xtrabackup: Only InnoDB and XTRADB data can be backed up.

Xbcrypt: Data used to encrypt or decrypt a backup.

Xbstream: A compressed file used to decompress or compress the Xbstream format.

3. Common options for Innobackupex

--

3, the implementation of backup

⑴ Full Backup (note: Xtrabackup does not back up binary files)

Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir

When you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. Files related to triggers and database configuration information are also backed up. These files are saved to a time-named directory.

⑵ Incremental backup using Innobackupex

Each InnoDB page contains an LSN information, and the LSN of the associated page automatically grows whenever the relevant data changes. This is the basis for an incremental backup of the InnoDB table, that is, Innobackupex is implemented by backing up pages that have changed since the last full backup.

Innobackupex--incremental/backup--incremental-basedir=dir

--incremental:

--incremental-basedir: Incremental backup based on which backup directory, the first incremental backup should point to the full backup directory, and subsequent incremental backups should point to the last incremental backup directory

When this command finishes, the Innobackupex command creates a new time-named directory in the/backup directory to hold all the incremental backup data

Note: Incremental backups can only be applied to InnoDB or xtradb tables, and for MyISAM tables, a full backup is actually performed when performing an incremental backup.

⑶ "Prepare" (prepare) Backup

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file remains in an inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

The--apply-log option of the Innobakupex command can be used to implement the above functions

It is important to note that incremental backups can only be applied to InnoDB or xtradb tables, and for MyISAM tables, a full backup is actually performed when performing an incremental backup.

The Prepare incremental backup is a bit different from organizing a full backup, especially to note that:

(1) You need to "replay" a transaction that has already been committed on each backup, including full and individual incremental backups. After replay, all backup data is merged into a full backup.

(2) "Roll Back" uncommitted transactions based on all backups.

As a result, the operation becomes:

# Innobackupex--apply-log--redo-only base-dir "-redo-only means only quasi-submissions"

Then execute:

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-1 #合并第一个增量

And then the second increment:

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-2

Where Base-dir refers to the directory where the full backup is located, and Incremental-dir-1 refers to the first incremental backup of the directory, incremental-dir-2 refers to the second incremental backup of the directory, and so on, that is, if there are multiple incremental backups, Each time to perform the above operation;

5, Xtrabackup "stream" and "Backup compression" function

Xtrabackup supports the "stream" feature for backed-up data files, which means that the backed up data can be transferred to the TAR program through stdout instead of being saved directly to a backup directory by default. To use this feature, you only need to use the--stream option. Such as:

# Innobackupex--stream=tar/backup | gzip >/backup/' Date +%f_%h-%m-%s '. tar.gz

You can even use a command similar to the following to back up data to other servers:

# Innobackupex--stream=tar/backup | SSH [email protected] "cat->/backups/' Date +%f_%h-%m-%s '. Tar"

In addition, when performing a local backup, you can also use the--PARALLEL option to replicate multiple files in parallel. This option specifies the number of threads to start when replication occurs. Of course, to take advantage of this feature when actually making a backup, you also need to enable the innodb_file_per_table option or the shared tablespace is stored in multiple Ibdata files through the Innodb_data_file_path option. Replication of multiple files for a database is not available to this feature. The simple way to use it is as follows:

# Innobackupex--parallel/path/to/backup

At the same time, Innobackupex backed up data files can also be stored to a remote host, which can be achieved using the--remote-host option:

# Innobackupex [email protected]/path/in/remote/host/to/backup

6. Import or export a single sheet

By default, the InnoDB table cannot be ported between MySQL servers by directly replicating table files, even if the innodb_file_per_table option is used. This is possible with the Xtrabackup tool, but the MySQL server that needs the export table now has the innodb_file_per_table option enabled (strictly speaking, the table to be "exported" is enabled by the MySQL server before it is created InnoDB _file_per_table option), and the server for the import table has both the innodb_file_per_table and Innodb_expand_import options enabled.

(1) "Export" table

The export table is in the prepare phase of the backup, so once the full backup is complete, you can export a table through the--export option during the prepare process:

# Innobackupex--apply-log--export/path/to/backup

This command creates a file that ends with. exp for each table space in the InnoDB table, and these files that end with. Exp can be used to import to other servers.

(2) "Import" table

To import a InnoDB table from another server on a MySQL server, you need to create a table on the current server that is consistent with the original table structure before you can import the table:

Mysql> CREATE TABLE mytable (...) Engine=innodb;

Then delete the table space for this table:

mysql> ALTER TABLE mydatabase.mytable DISCARD tablespace;

Next, copy the MYTABLE.IBD and Mytable.exp files from the server's MyTable table from the export table to the current server's data directory, and then use the following command to "import" them:

"Chown mysql.mysql mytable.ibd mytable.exp"

mysql> ALTER TABLE mydatabase.mytable IMPORT tablespace;

7. Use Xtrabackup to make a partial backup of the database

Xtrabackup can also implement partial backups, that is, to back up only one or some of the specified databases or some or some of the tables in a database. However, to use this feature, you must enable the innodb_file_per_table option, which is to save each table as a separate file. It also does not support the--stream option, which means that data is not supported to be piped to other programs for processing.

In addition, restoring a partial backup is also different from restoring all of the data, that is, you cannot directly copy back to the data directory by simply prepared a partial backup using the--copy-back option, but rather by importing the direction of the table to achieve the restore. Of course, in some cases, partial backups can also be restored directly through--copy-back, but the majority of the data that is restored in this way results in inconsistent data, so it is not recommended in any way.

(1) Create a partial backup

There are three ways to create a partial backup: a regular expression (--include), an enumeration table file (--tables-file), and a list of databases to back up (--databases).

(a) Use of--include

When using--include, it is required to specify the full name of the table to be backed up, i.e., the form databasename.tablename, such as:

# Innobackupex--include= ' ^mageedu[. Tb1 '/path/to/backup

(b) Use of--tables-file

The parameter for this option needs to be a file name with each row containing the full name of the table to be backed up, such as:

# echo-e ' mageedu.tb1\nmageedu.tb2 ' >/tmp/tables.txt

# Innobackupex--tables-file=/tmp/tables.txt/path/to/backup

(c) Use of--databases

This option takes a data name, and if you want to specify multiple databases, you need a space between them, and when you specify a database, you can specify only one of the tables. In addition, this option can also accept a file as a parameter, and each action in the file is an object to be backed up. Such as:

# Innobackupex--databases= "mageedu testdb"/path/to/backup

(2) Finishing (preparing) Partial backup

The process of prepare partial backups is similar to the process of exporting tables, using the--export option:

# Innobackupex--apply-log--export/pat/to/partial/backup

During the execution of this command, Innobackupex calls the Xtrabackup command to remove the missing table from the data dictionary, so there are many warning messages about the "table does not exist" class. It also shows information about creating an. exp file for the table that exists in the backup file.

(3) Restore partial backup

The process of restoring a partial backup is the same as importing a table. Of course, you can also restore directly to the data directory by copying a backup of the prepared state directly, not requiring the data directory to be in a consistent state at this time.

12. Precautions

⑴ data and binaries are placed on different devices, and binary logs should be backed up periodically;

⑵ data and backup storage separately, it is recommended not the same device, the same host, the same computer room, the same region;

⑶ a full backup should be done immediately after each disaster recovery;

⑷ the data after backup should be done periodically to restore the test;

13. Steps you should follow to recover from a backup

① stop MySQL server;

② record server configuration and file permissions;

③ Restore the backup to the MySQL data directory; This step relies on a specific backup tool;

④ change configuration and file permissions;

⑤ start the MySQL server in a limited way: for example, through network access;

[Mysqld]

Skip-networking

Socket=/tmp/mysql-recovery.sock

⑥ load additional logical backups while checking and replaying the binary logs;

⑦ Check the data that has been restored;

⑧ Restart the server in full access mode;

MySQL Learning Note VII: Backup and recovery of data