Use xtrabackup for MySQL backup:
First, installation
1, Introduction Xtrabackup is provided by the Percona MySQL database backup tool, according to the official introduction, this is the world's only one by one open source of the InnoDB and XTRADB database can be hot prepared tools. Characteristics: (1) The backup process is fast and reliable, (2) The backup process does not interrupt the executing transaction, (3) can save disk space and traffic based on functions such as compression, (4) automatically implement backup inspection; (5) Fast restore speed;
2, install the latest version of the software can be obtained from http://www.percona.com/software/percona-xtrabackup/. This article is based on the RHEL5.8 system, so just download the appropriate version of the RPM package to install it and no longer demonstrate its process.
Second, the implementation of backup
1. Full backup
# Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/
If you want to back up with a user with a minimum of permissions, you can create such a user based on the following command::mysql> create users ' bkpuser ' @ ' localhost ' identified by ' S3cret ';mysql> REVOKE All privileges, grant OPTION from ' Bkpuser ';mysql> Grant RELOAD, LOCK TABLES, REPLICATION CLIENT on * * to ' bkpuser ' @ ' L Ocalhost ';mysql> FLUSH privileges;
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 directory in the time command.
At the same time as the backup, Innobackupex also creates the following files in the backup directory: (1) xtrabackup_checkpoints--backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log sequence number) Scope information;
Each InnoDB page (typically 16k size) contains a log sequence number, the LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.
(2) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.
(3) xtrabackup_binlog_pos_innodb--binary log files and the current position for binary log files for InnoDB or xtradb tables.
(4) Xtrabackup executable files used in xtrabackup_binary--backup;
(5) backup-my.cnf--configuration option information used for backup command;
When using Innobackupex for backup, you can also use the--NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.
2. Prepare (prepare) a full 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 still handles the 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. As in the following command:
# Innobackupex--apply-log/path/to/backup-dir If executed correctly, the last few lines of the output of the message are usually as follows: Xtrabackup:starting shutdown with Innodb_fast_ shutdown = 1120407 9:01:36 innodb:starting shutdown ... 120407 9:01:40 Innodb:shutdown completed; Log sequence number 92036620120407 09:01:40 innobackupex:completed ok!
In the process of "preparing," Innobackupex can typically use the--use-memory option to specify the amount of memory it can use, which is typically 100M by default. If there is enough memory available, you can partition some memory into the prepare process to improve its completion speed.
3. Recovering data from a full backup
Note: Recovery does not start MySQL
The--copy-back option of the Innobackupex command is used to perform recovery operations, which perform the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.
# Innobackupex--copy-back/path/to/backup-dir If executed correctly, the last lines of its output information are usually as follows: innobackupex:starting to copy InnoDB log Filesinnobackupex:in '/backup/2012-04-07_08-17-03 ' innobackupex:back to original InnoDB log directory '/mydata/data ' Innobackupex:finished copying back files.
120407 09:36:10 innobackupex:completed ok!
Make sure that "innobackupex:completed ok!" appears on the top line of the information above.
When the data is restored to the DataDir directory, it is also necessary to ensure that all data files belong to the owner and the group are the correct users, such as MySQL, otherwise, before starting the mysqld, you need to modify the data file's owner and host groups beforehand. Such as:
# Chown-r mysql:mysql/mydata/data/
4. Use Innobackupex for incremental backup
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.
To implement the first incremental backup, you can use the following command:
# Innobackupex--incremental/backup--incremental-basedir=basedir
Where Basedir refers to the directory where the full backup is located, the Innobackupex command creates a new time-named directory in the/backup directory to hold all the incremental backup data after the command execution is completed. Also, when incremental backups are performed after an incremental backup, their--incremental-basedir should point to the same directory as the last incremental backup.
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" the committed transaction on each backup, including full and incremental backups. After replay, all backup data is merged into a full backup. (2) "Roll Back" uncommitted transactions based on all backups.
So, the operation becomes: # Innobackupex--apply-log--redo-only Base-dir
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 implemented 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) The Export Table 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 the 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 into the:mysql> CREATE table MyTable (...) Engine=innodb;
The table space for this table is then removed: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":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) When using--include with--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--tables-file the parameter of this option needs to be a file name, each row in this file contains a full name for 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--databases This option accepts parameters as data names, and if multiple databases are to be specified, spaces are separated from each other, and only one of the tables can be specified when a database is specified. 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 prepare partial backup is similar to the process of exporting a table, to use 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) Restoring a partial backup restores part of the backup process the same as importing the 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.
From for notes (Wiz)
Use xtrabackup for MySQL backup:
