Mysql xtrabackup Backup recovery implementation sharing

Introduction
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, Xtrabackup is the only open-source tool in the world that can perform hot backup for innodb and xtradb databases. Features:
(1) The backup process is fast and reliable;
(2) The backup process will not interrupt ongoing transactions;
(3) Saving disk space and traffic based on compression and other functions;
(4) automatic backup check;
(5) Fast Restoration;
Xtrabackup contains two tools:
* Xtrabackup-A Tool for Hot Backup of innodb and xtradb tables. Other tables cannot be backed up.
* Innobackupex-perl scripts encapsulated in xtrabackup provide the MyISAM Table backup capability. (Supports full-database and data table backup ).
When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables,
At the same time, files related to the trigger and database configuration will be backed up. These files are saved to a time command directory.
During backup, innobackupex 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 in prepared status), And LSN (log serial number) range information; each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.
(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.
(3) xtrabackup_binlog_pos_innodb -- the current position of the binary log file used for InnoDB or XtraDB tables.
(4) xtrabackup_binary -- The xtrabackup executable file used in backup;
(5) backup-my.cnf-the configuration option information used by the BACKUP command;
When using innobackupex for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a time-based directory; The innobackupex command will create a BACKUP-DIR directory to store 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 installation of mysql5.5.28 is not described here
Create a directory for Data Backup
Mkdir-p/opt/mysql_bak/
Modify Database Configuration File:
Vim/etc/my. cnf
Datadir =/home/mysql/data/
Mysqladmin-uroot password 123456
Full-Database Backup:
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
Back up and compress:
Innobackupex -- user = root -- password = 123456 -- defaults-file =/etc/my. cnf -- database = test -- stream = tar/opt/mysql_bak/| gzip>/opt/mysql_bak/testdb.tar.gz
Timestamp
Innobackupex -- user = root -- password = 123456 -- defaults-file =/etc/my. cnf -- database = test -- stream = tar/opt/mysql_bak/| gzip>/opt/mysql_bak/'date when using f'_testdb.tar.gz