一 簡介: Xtrabackup是一個對InnoDB做資料備份的工具，支援線上熱備份（備份時不影響資料讀寫），是商業備份工具InnoDB Hotbackup的一個很好的替代品。它能對InnoDB和XtraDB存儲引擎的資料庫非阻塞地備份（對于MyISAM的備份同樣需要加表鎖）。XtraBackup支援所有的Percona Server、MySQL、MariaDB和Drizzle。幾年前使用過，但現在忘記的差不多了，是以就重新拾起看看。
xtrabackup有兩個主要的工具：xtrabackup、innobackupex
(1).xtrabackup隻能備份InnoDB和XtraDB 兩種資料表
(2).innobackupex則封裝了xtrabackup,同時可以備份MyISAM資料表 Innobackupex完整備份後生成了幾個重要的檔案：
xtrabackup_binlog_info：記錄目前最新的LOG Position
xtrabackup_binlog_pos_innodb:innodb log postion
xtrabackup_checkpoints: 存放備份的起始位置beginlsn和結束位置endlsn,增量備份需要這個lsn[增量備份可以在這裡面看from和to兩個值的變化] Xtrabackup特點：
(1)備份過程快速、可靠
(2)備份過程不會打斷正在執行的事務
(3)能夠基于壓縮等功能節約磁盤空間和流量
(4)自動實作備份檢驗
(5)還原速度快 二 安裝: http://www.percona.com/downloads/
下載下傳安裝：
1）直接下載下傳二進制包，不編譯，編譯需要和MySQL源碼包配合。
wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz 1）tar zxvf percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz
2）ls -lh
drwxr-xr-x 2 root root 4.0K 5月 2 2014 bin
drwxr-xr-x 4 root root 4.0K 5月 2 2014 share
3）
ls -lh bin/
-rwxr-xr-x 1 root root 165K 5月 2 2014 innobackupex
lrwxrwxrwx 1 root root 12 5月 2 2014 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbcrypt
-rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbstream
-rwxr-xr-x 1 root root 13M 5月 2 2014 xtrabackup
-rwxr-xr-x 1 root root 16M 5月 2 2014 xtrabackup_55
-rwxr-xr-x 1 root root 79M 5月 2 2014 xtrabackup_56
4）
cp innobackupex-1.5.1 /usr/bin/innobackupex
cp xtrabackup_55 /usr/bin/xtrabackup
#cp xtrabackup /usr/bin 2）Ubuntu 下的安裝:http://www.percona.com/doc/percona-xtrabackup/2.1/installation/apt_repo.html
1：執行
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
2：在 vi /etc/apt/sources.list 添加
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main
3：安裝
$ apt-get update
$ apt-get install percona-xtrabackup 支援的版本有：
Debian
- 6.0 (squeeze)
- 7.0 (wheezy)
Ubuntu
- 10.04LTS (lucid)
- 12.04LTS (precise)
- 12.10 (quantal)
- 13.04 (raring)
- 13.10 (saucy)
把2中的 VERSION 按照自己的系統把上面的代入。 deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main # precise是Ubuntu-12.04的版本代号，如果是其它系統版本，需要更換。
三 使用說明:
安裝完之後會生成幾個工具：http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html
innobackupex：這個是其實是下面三個工具的一個perl腳本封裝，可以備份MyISAM, InnoDB, XtraDB表。但在處理Myisam時需要加一個讀鎖。
xtrabackup：一個由C編譯而來的二進制檔案，隻能備份InnoDB和XtraDB資料。
xbcrypt：用來加密或解密備份的資料。
xbstream：用來解壓或壓縮xbstream格式的壓縮檔案。 innobackupex ：
xtrabackup指令隻備份資料檔案,并不備份資料表結構(.frm)，是以使用xtrabackup恢複的時候必須有對應表結構檔案(.frm)。用innobackupex指令，此指令相當于冷備份，複制資料目錄的索引，資料，結構檔案，但會有短暫的鎖表（時間依賴于MyISAM大小）。
參數：
--defaults-file：指定my.cnf參數檔案的位置[此配置檔案裡必須指定datadir]
--apply-log：同xtrabackup的--prepare參數,一般情況下,在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料 檔案仍處理不一緻狀态。--apply-log的作用是通過復原未送出的事務及同步已經送出的事務至資料檔案使資料檔案處于一緻性狀态。
--copy-back：做資料恢複時将備份資料檔案拷貝到MySQL伺服器的datadir
--remote-host=HOSTNAME： 通過ssh将備份資料存儲到程序伺服器上
--stream=[tar]：備份檔案輸出格式, 該檔案可在XtarBackup binary檔案中獲得. 在使用參數stream=tar備份的時候,你的xtrabackup_logfile可能會臨時放在/tmp目錄下,如果你備份的時候并發寫入較大的話,xtrabackup_logfile可能會很大(5G+),很可能會撐滿你的/tmp目錄,可以通過參數--tmpdir指定目錄來解決這個問題.
--tmpdir=DIRECTORY：當有指定--remote-host or --stream時, 事務日志臨時存儲的目錄, 預設采用MySQL配置檔案中所指定的臨時目錄tmpdir
--redo-only --apply-log：強制備份日志時隻redo,跳過rollback,這在做增量備份時非常必要
--use-memory=*：該參數在prepare的時候使用,控制prepare時innodb執行個體使用的記憶體
--databases=LIST：列出需要備份的databases,如果沒有指定該參數,所有包含MyISAM和InnoDB表的database都會被備份
--slave-info：備份從庫, 加上--slave-info備份目錄下會多生成一個xtrabackup_slave_info 檔案, 這裡會儲存主日志檔案以及偏移, 檔案内容類似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--socket=SOCKET：指定mysql.sock所在位置，以便備份程序登入mysql. 更多參數見：http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html
四 測試：
1)全量備份&還原
#初始化
mysql> create database xtra_test default charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use xtra_test
Database changed
mysql> create table M(id int,name varchar(10))engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table I(id int,name varchar(10))engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into M values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into I values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from M;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
5 rows in set (0.00 sec)
mysql> select * from I;
+------+------+
| id | name |
+------+------+
| 11 | A |
| 22 | B |
| 33 | C |
| 44 | D |
| 55 | E |
+------+------+
5 rows in set (0.00 sec) #備份
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test /home/zhoujy/xtrabackup/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
141111 11:56:58 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.200.51' as 'zjy' (using password: YES).
141111 11:56:58 innobackupex: Connected to MySQL server
141111 11:56:58 innobackupex: Executing a version check against the server...
141111 11:56:58 innobackupex: Done.
141111 11:56:58 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using server version 5.5.38-0ubuntu0.12.04.1-log
innobackupex: Created backup directory /home/zhoujy/xtrabackup/2014-11-11_11-56-58
141111 11:56:58 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='xtra_test'
innobackupex: Waiting for ibbackup (pid=10539) to suspend
innobackupex: Suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2'
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (7363097930)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/zhoujy/xtrabackup/2014-11-11_11-56-58/ibdata1
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
[01] ...done
>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2' with pid '10540'
141111 11:57:11 innobackupex: Continuing after ibbackup has suspended
141111 11:57:11 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
141111 11:57:11 innobackupex: All tables locked and flushed to disk
141111 11:57:11 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/db.opt'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYI'
>> log scanned up to (7363097930)
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/I.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYD'
141111 11:57:11 innobackupex: Finished backing up non-InnoDB tables and files
141111 11:57:11 innobackupex: Executing FLUSH ENGINE LOGS...
141111 11:57:11 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '7363097930'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_log_copied' with pid '10540'
xtrabackup: Transaction log of lsn (7363097930) to (7363097930) was copied.
141111 11:57:12 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/zhoujy/xtrabackup/2014-11-11_11-56-58'
innobackupex: MySQL binlog position: filename 'mysql-bin51.000001', position 967
141111 11:57:12 innobackupex: Connection to database server closed
141111 11:57:12 innobackupex: completed OK! 檢視備份出來的檔案：
ls -lh 2014-11-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 11月 11 11:57 ibdata1
-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info
-rw-r----- 1 root root 95 11月 11 11:57 xtrabackup_checkpoints
-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.5K 11月 11 11:57 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test /home/zhoujy/xtrabackup/ 備份存放的位置，備份會在該目錄下生成一個按照時間命名的檔案夾。用--no-timestamp參數可以指定到自己想要的備份檔案夾，不受時間命名的檔案夾限制。
利用 --apply-log的作用是通過復原未送出的事務及同步已經送出的事務至資料檔案使資料檔案處于一緻性狀态。
root@ha1:/home/zhoujy/xtrabackup# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_11-56-58/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
141111 12:02:42 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
141111 12:02:42 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(7363097930)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3.4
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 7363094028 and 7363094028 in ibdata files do not match the log sequence number 7363097930 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 7363097930
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7363100603
141111 12:02:44 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file="/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58
for creating ib_logfile*
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3.4
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=7363100603
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 7363100684
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7363103417
141111 12:02:45 innobackupex: completed OK! 應用完之後再檢視備份檔案看是否有變化：
root@ha1:/home/zhoujy/xtrabackup# ls -lh 2014-11-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 11月 11 12:02 ibdata1
-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile0
-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile1
-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info
-rw-r--r-- 1 root root 38 11月 11 12:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 95 11月 11 12:02 xtrabackup_checkpoints
-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.0M 11月 11 12:02 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test 看到redo log已經出現。xtrabackup_開頭的幾個檔案記錄了一些日志偏移量的資訊和日志名和時間等資訊。在xtrabackup_checkpoints中記錄了備份的模式：
backup_type = full-backuped。
xtrabackup_binlog_info：記錄目前最新的LOG Position
xtrabackup_binlog_pos_innodb:innodb log postion
xtrabackup_checkpoints: 存放備份的起始位置beginlsn和結束位置endlsn,增量備份需要這個lsn[增量備份可以在這裡面看from和to兩個值的變化
xtrabackup_info:備份的一些具體資訊日志 #還原
mysql> drop database xtra_test;
Query OK, 2 rows affected (0.00 sec) 1) 删除原始目錄裡的檔案：
root@ha1:/var/lib/mysql# rm ib_logfile0 ib_logfile1 ibdata1 關閉資料庫：
1) root@ha1:/var/lib/mysql# /etc/init.d/mysql stop
開始恢複
2) 1：innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/zhoujy/xtrabackup/2014-11-11_11-56-58/
執行上面的指令需要保證資料庫目錄是空的，否則會失敗，因為目錄裡有binlog日志，要麼移走，要麼就用手動複制需要的檔案。是以說日志不要和資料放在一起！
2：cp ib_logfile0 ib_logfile1 ibdata1 /var/lib/mysql/
cp -R xtra_test /var/lib/mysql/
修改權限和開啟資料庫
3) root@ha1:/var/lib/mysql# chown -R mysql.mysql ib*
root@ha1:/var/lib/mysql# chown -R mysql.mysql xtra_test/
開啟資料庫
root@ha1:/var/lib/mysql# /etc/init.d/mysql start
...
mysql start/running, process 15937 資料已恢複：
mysql> use xtra_test
Database changed
mysql> select * from I;
+------+------+
| id | name |
+------+------+
| 11 | A |
| 22 | B |
| 33 | C |
| 44 | D |
| 55 | E |
+------+------+
5 rows in set (0.00 sec) 2)增量備份&還原
#備份
在做增量備份的時候需要做一次全量備份，和上面一樣，隻是針對所有庫的備份：
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf /home/zhoujy/xtrabackup/
...
innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 107
141111 13:57:24 innobackupex: Connection to database server closed
141111 13:57:24 innobackupex: completed OK! 增量備份這裡開始
1.資料庫操作：
mysql> select * from I;
+------+------+
| id | name |
+------+------+
| 11 | A |
| 22 | B |
| 33 | C |
| 44 | D |
| 55 | E |
+------+------+
5 rows in set (0.00 sec)
mysql> insert into I values(111,'A'),(222,'B'),(333,'C'),(444,'D'),(555,'E');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from M;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
5 rows in set (0.00 sec)
mysql> update M set name = upper(name);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> create table X(name varchar(20))default charset utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into X values('zjy');
Query OK, 1 row affected (0.00 sec) 2：增量備份（基于全量的增量備份）
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/xtrabackup/2014-11-11_13-57-11/ /home/zhoujy/increment_data/
...
...
trabackup: Creating suspend file '/home/zhoujy/increment_data/2014-11-11_14-14-56/xtrabackup_log_copied' with pid '19492'
xtrabackup: Transaction log of lsn (7363106612) to (7363106612) was copied.
141111 14:15:06 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/zhoujy/increment_data/2014-11-11_14-14-56'
innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 981
141111 14:15:06 innobackupex: Connection to database server closed
141111 14:15:06 innobackupex: completed OK! 其中，--incremental指明是增量備份，--incremental-basedir指定上次完整備份或者增量備份檔案的位置。這裡的增量備份其實隻針對的是InnoDB，對于MyISAM來說，還是完整備份。
增量備份的檔案：
root@ha1:/home/zhoujy/increment_data# ls -lh 2014-11-11_14-14-56/
total 440K
-rw-r--r-- 1 root root 188 11月 11 14:14 backup-my.cnf
-rw-r----- 1 root root 400K 11月 11 14:15 ibdata1.delta
-rw-r----- 1 root root 44 11月 11 14:14 ibdata1.meta
drwxr-xr-x 2 root root 4.0K 11月 11 14:15 mha_test
drwxr-xr-x 2 root root 4.0K 11月 11 14:15 mysql
drwxr-xr-x 2 root root 4.0K 11月 11 14:15 performance_schema
-rw-r--r-- 1 root root 25 11月 11 14:15 xtrabackup_binlog_info
-rw-r----- 1 root root 102 11月 11 14:15 xtrabackup_checkpoints
-rw-r--r-- 1 root root 738 11月 11 14:15 xtrabackup_info
-rw-r----- 1 root root 2.5K 11月 11 14:15 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 11月 11 14:15 xtra_test
root@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-14-56/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7363103427 對應增量中的to_lsn
to_lsn = 7363106612
last_lsn = 7363106612
compact = 0 3:繼續增量備份
資料庫操作
mysql> insert into X values('dxy');
Query OK, 1 row affected (0.01 sec)
mysql> insert into X values('浙江');
Query OK, 1 row affected (0.01 sec) 增量備份（基于增量的增量備份）
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/increment_data/2014-11-11_14-14-56/ /home/zhoujy/increment_data/
141111 14:25:46 innobackupex: Connection to database server closed
141111 14:25:46 innobackupex: completed OK! 增量備份的檔案
root@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-25-36/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7363106612 對應上一個備份的to_lsn
to_lsn = 7363107216
last_lsn = 7363107216
compact = 0 要是在第2次做增量備份的時候 --incremental-basedir 指向全量備份，則第一次增量備份中的資料會被第2次包含，隻需要還原一次就可以恢複，現在則需要還原2次增量備份。
#還原
#還原全量備份：
root@ha1:/home/zhoujy# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/
#第一次的增量備份應用日志,應用完日志後,将合并到全備上,恢複使用全備恢複
root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-14-56/
#第二次的增量備份應用日志,應用完日志後,将合并到全備上,恢複使用全備恢複
root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-25-36/
此時兩次增量備份其實都合并到全備上了,恢複是隻需要使用全備進行恢複就可以了 還原操作執行完之後，檢視其lsn的資訊：
root@ha1:/home/zhoujy/xtrabackup/2014-11-11_13-57-11# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 7363107216
last_lsn = 7363107216
compact = 0 說明了，還原操作執行沒問題。按照之前還原操作的步驟來恢複資料：先删除資料，再把檔案複制過去，看看是否成功。測試下來，還原成功。 3）壓縮備份：必須使用-i參數拆包解壓
打包(Tar)備份 innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ 1>/home/zhoujy/xtrabackup/xtra_test.tar 拆包還原 root@ha1:/home/zhoujy/xtrabackup# tar ixvf xtra_test.tar
./backup-my.cnf
ibdata1
xtra_test/db.opt
xtra_test/M.MYI
xtra_test/M.frm
xtra_test/I.frm
xtra_test/M.MYD
./xtrabackup_binlog_info
xtrabackup_logfile
xtrabackup_checkpoints
./xtrabackup_info 還原方法和上面一樣，就不多做說明了。
打包壓縮：
root@ha1:/home/zhoujy/xtrabackup# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ | gzip >/home/zhoujy/xtrabackup/xtra_test.tar.gz 解壓：
root@ha1:/home/zhoujy/xtrabackup# tar izxvf xtra_test.tar.gz
./backup-my.cnf
ibdata1
xtra_test/db.opt
xtra_test/M.MYI
xtra_test/M.frm
xtra_test/I.frm
xtra_test/M.MYD
./xtrabackup_binlog_info
xtrabackup_logfile
xtrabackup_checkpoints
./xtrabackup_info 還原方法和上面一樣。
更多資訊見：
http://mysql.taobao.org/monthly/2016/03/07/
http://op.baidu.com/2014/07/xtrabackup%E5%8E%9F%E7%90%86%E5%8F%8A%E5%AE%9E%E6%96%BD/
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)