MySQL xtrabackup backup recovery implementation, MySQL command backup database, package compression database

Introduction
Xtrabackup is a MySQL database backup tool provided by Percona, officially described, which is the world's only one by one open source tools that enable hot provisioning of InnoDB and XTRADB databases. 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) Automatic implementation of backup inspection;
(5) Fast restore speed;
The xtrabackup contains two tools：
* Xtrabackup-Tool for Hot backup innodb, XTRADB table, cannot back up other tables.
* Innobackupex-A Perl script for the Xtrabackup package that provides the ability to MyISAM table backups. (Full library and data table backup is possible).
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) range information, each InnoDB page (usually 16k size) contains a log sequence number, That is, 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; the Innobackupex command creates a Backup-dir directory to store the backup data.
Binary version
http://www.percona.com/downloads/XtraBackup/
wget http://www.percona.com/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.0.3-470.tar.gz
Tar zxvf percona-xtrabackup-2.0.3-470.tar.gz
CD Percona-xtrabackup-2.0.3/bin
CP */usr/bin/
The use of this mysql5.5.28 installation is not described here
Create a directory for data backup
Mkdir-p/opt/mysql_bak/
To Modify a database configuration file：
Vim/etc/my.cnf
DataDir =/home/mysql/data/
mysqladmin-uroot Password 123456
Backup of the whole library:
Innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf/opt/mysql_bak/
Separate backup:
Innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--database=test/opt/mysql_bak
Backup and package compression：
Innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--database=test--stream=tar/opt/mysql_ bak/| gzip >/opt/mysql_bak/testdb.tar.gz
with time stamp
Innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--database=test--stream=tar/opt/mysql_ bak/| gzip >/opt/mysql_bak/' Date +%f ' _testdb.tar.gz

MySQL xtrabackup backup recovery implementation, MySQL command backup database, package compression database