Xtrabackup Backup for MySQL

########################################################################################  ###### When recovering a database, you need to apply the backup log to the backup file so that it can be restored ##### #备份: innobackupex --user=root --password=banma -- defaults-file=/etc/my.cnf  --database=testinnodb  /tmp/data/##### #恢复2步骤  innobackupex  --user=root --password=banma --apply-log /tmp/data/2013-08-23_16-09-10/[[email  Protected] 2013-08-23_16-09-10]# rsync -avz testinnodb ib* /usr/local/mysql_data /data/chown -r mysql:mysql /usr/local/mysql_data/data/######################################### ################################################################ #一, installation deployment, and Procedures 1. Download and install wget http:// www.percona.com/redir/downloads/xtrabackup/xtrabackup-1.6.7/binary/linux/x86_64/xtrabackup-1.6.7.tar.gz  2. Install the required library yum install cmake gcc gcc-c++ libaio libaio-devel automake  Autoconf bzr   bison&nbsP;LIBTOOL NCURSES-DEVEL ZLIB-DEVEL3, unzip tar zxvf xtrabackup-1.6.7.tar.gz  4, enter directory CD  xtrabackup-1.6.7 5, copy the cd bin  cp * /usr/bin   and then the installation is complete, start the backup below    Note: File interpretation Innobackupex is the backup tool we want to use, Xtrabackup is encapsulated in Innobackupex, Innobackupex runtime needs to call it; Xtrabackup_ 51 is a tool that needs to be called when the xtrabackup is run; TAR4IBD is a tool that is used to package a backup in the form of a tar stream. Second, backup and restore 6, the command to make a full backup of a database is introduced Innobackupex --user=root --password=123456 --defaults-file=/etc/mysql /my.cnf --database=test --stream=tar /tmp/data/ 2>/tmp/data/info.log|gzip 1>/ tmp/data/test.tar.gz  Description:      --database=test  The test database to do a separate backup   If you do not add this parameter, then that is the whole library do backup       2>/tmp/data/info.log  output information written in the log        1>/tmp/data/test.tar.gz  Packaging compressed storage into this file   ( mysql> create  table test  (id int);   query ok, 0 rows affected  (0.06 SEC)   mysql> insert into test values (1);   Query  OK, 1 row affected  (0.04 sec)   )  7, recover database   (1) Create a folder   Mkdir /tmp/restore cd /tmp/data tar -izxvf test.tar.gz -c /tmp/restore [[email protected] data]# ll /tmp/restore/Total dosage  131096-rw-r--r-- 1 root   root        263  8 Month  23 14:38  BACKUP-MY.CNF-RW-RW---- 1 mysql mysql 134217728  8 Month  23 14:22  Ibdata1drwxr-xr-x 2 root  root       4096  8 Month  23 14:53 test-rw-r--r-- 1 root  root          13  8 Month  23 14:38 xtrabackup_binary-rw-r--r-- 1 root   root   &nBsp;     21  8 Month  23 14:38 xtrabackup_binlog_info-rw-r--r--  1 root  root         73  8 month   23 14:38 xtrabackup_checkpoints-rw-r--r-- 1 root  root        2560  8 month  23 14:38 xtrabackup_logfile (2) Start the Application log (the log in the backup file, Applied to the data file in the backup file,--apply-log indicates that the log is applied to the data file and the data in the backup file is restored to the database after completion [[email protected] restore]#  innobackupex --user=root --password=banma --apply-log /tmp/restore/ [[email  Total dosage of Protected] restore]# ll  526356-rw-r--r-- 1 root  root         263  8 Moon  23 14:38 BACKUP-MY.CNF-RW-RW---- 1  Mysql mysql 134217728  8 Month  23 14:59 ibdata1-rw-r--r-- 1 root   root  134217728 &Nbsp;8 Month  23 14:59 ib_logfile0-rw-r--r-- 1 root  root  134217728   8 Month  23 14:59 ib_logfile1-rw-r--r-- 1 root  root   134217728  8 Month  23 14:59 ib_logfile2drwxr-xr-x 2 root  root        4096  8 Month  23 14:53 test-rw-r--r-- 1 root   root         13  8 Month  23 14:38  xtrabackup_binary-rw-r--r-- 1 root  root          21  8 Month  23 14:38 xtrabackup_binlog_info-rw-r--r-- 1 root   root         73  8 Month  23 14:59  Xtrabackup_checkpoints-rw-r--r-- 1 root  root    2097152  8 Month  23 14:59 xtrabackuP_logfile (3) restore formally start and then copy the test directory to the/usr/local/mysql_data/data directory [[Email protected] restore]# rsync  -avz test ib* /usr/local/mysql_data/datasending incremental file listib_ Logfile0ib_logfile1ib_logfile2ibdata1test/test/test. Mydtest/test. myitest/test.frmsent 529692 bytes  received 149 bytes  13762.10  Bytes/sectotal size is 536880534  speedup is 1013.29[[email protected]  restore] #chown  -r mysql:mysql /usr/local/mysql_data/data Three, incremental backup and recovery (1) Add a few data first insert  Into test values (insert into test values);insert into test  Values (insert into test values); Insert into test values (+);insert  Into test values (15); (2) incremental backup [[Email protected] data]# innobackupex --user=root  --password=banma --database=test --incremental --Incremental-basedir=/tmp/restore/ /tmp/data which,--incremental indicates an incremental backup,-- INCREMENTAL-BASEDIR Specifies the location of the last full backup or incremental backup file. The incremental backup here is in fact only for InnoDB, and for MyISAM, it is a full backup. Before the recovery of an incremental backup, close the database, and then delete the database Test (3) Incremental backup recovery [[Email protected] data]# innobackupex -user=root  --password=banma --defaults-file=/etc/my.cnf --apply-log /tmp/restore/ -- INCREMENTAL-DIR=/TMP/DATA/2013-08-23_15-22-54/then enter the recovered directory/tmp/data[[email protected] 2013-08-23_ 15-22-54]# ll Total dosage  172-rw-r--r-- 1 root root    263  8 Month  23 15:22 backup-my.cnf-rw-r--r-- 1 root root 147456  8 Month  23  15:22 ibdata1.delta-rw-r--r-- 1 root root     18   8 Month  23 15:22 ibdata1.metadrwxr-xr-x 2 root root   4096   8 Month  23 15:23 test-rw-r--r-- 1 root root    &nbsP;13  8 Month  23 15:23 xtrabackup_binary-rw-r--r-- 1 root root      21  8 Month  23 15:23 xtrabackup_binlog_info-rw-r--r-- 1 root  root     75  8 Month  23 15:23 xtrabackup_ Checkpoints-rw-r--r-- 1 root root   2560  8 Month  23 15:23  xtrabackup_logfile[[email protected] 2013-08-23_15-22-54]# rsync -avz test ib*  /usr/local/mysql_data/data/

This article is from the "Record Drip" blog, please be sure to keep this source http://caozm.blog.51cto.com/1118764/1622108

Xtrabackup Backup for MySQL