xtrabackup增量備份方案，備份恢復全過程記錄 [html] mysql> select * from mm; +------+ | a | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) mysql> show tables; +----------------+ | Tables_in_inno | +----------------+ | mm | | t | +----------------+ 2 rows in set (0.01 sec) mysql> insert into mm select 9999; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 9999; Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> select * from t; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> select * from mm; +------+ | a | +------+ | 1 | | 2 | | 3 | | 4 | | 9999 | +------+ 5 rows in set (0.00 sec) mysql> select * from t; +------+ | a | +------+ | 1 | | 2 | | 3 | | 9999 | +------+ 4 rows in set (0.00 sec) mysql> 以上是准備相關的環境，以下開始進行備份恢復相關的記錄； [html] 增量備份及模擬恢復步驟: 先做一次全量備份，增量備份需要以此為基礎 [root@Slave02 inno]# mkdir -p /data/back_up/delta ---建立增量備份文件夾 [root@Slave02 inno]# cd /data/back_up/ [root@Slave02 back_up]# ls delta ibdata1 inno xtrabackup_checkpoints xtrabackup_logfile [root@Slave02 back_up]# cd delta/ [root@Slave02 delta]# ls [root@Slave02 delta]# [root@Slave02 delta]# ls -la total 16 drwxr-xr-x 2 root root 4096 Mar 27 14:35 . drwxr-xr-x 4 root root 4096 Mar 27 14:35 .. [root@Slave02 delta]# [root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf --backup --tar get-dir=/data/back_up/delta --incremental-basedir=/data/back_up xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined) incremental backup from 893330211 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /usr/local/mysql/data xtrabackup: Target instance is assumed as followings. 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 130327 14:38:04 InnoDB: Using Linux native AIO 130327 14:38:04 InnoDB: Warning: allocated tablespace 2, old maximum was 0 >> log scanned up to (893330821) [01] Copying ./ibdata1 to /data/back_up/delta/ibdata1.delta [01] ...done [01] Copying ./inno/mm.ibd to /data/back_up/delta/inno/mm.ibd.delta [01] ...done [01] Copying ./inno/t.ibd to /data/back_up/delta/inno/t.ibd.delta [01] ...done >> log scanned up to (893330821) xtrabackup: The latest check point (for incremental): '893330821' >> log scanned up to (893330821) xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (893330821) to (893330821) was copied. [root@Slave02 delta]# [root@Slave02 delta]# ls /data/back_up/delta/ -生成的增量備份數據文件 ibdata1.delta ibdata1.meta inno xtrabackup_checkpoints xtrabackup_logfile [root@Slave02 delta]# [root@Slave02 delta]# cd inno/ [root@Slave02 inno]# ls mm.ibd.delta mm.ibd.meta t.ibd.delta t.ibd.meta [root@Slave02 inno]# ll total 88 -rw-r--r-- 1 root root 32768 Mar 27 14:38 mm.ibd.delta -rw-r--r-- 1 root root 18 Mar 27 14:38 mm.ibd.meta -rw-r--r-- 1 root root 32768 Mar 27 14:38 t.ibd.delta -rw-r--r-- 1 root root 18 Mar 27 14:38 t.ibd.meta [root@Slave02 inno]# 注:在增量備份目錄下，數據文件都是以.delta結尾的。增量備份只備份上一次全量備份後被修改過的page，所以增量備份只暫用較少的空 間。增量備份可以在增量備份的基礎上增量。增量備份目錄每次都需修改的。比如第二次增量就改成/data/back_up/delta2/) 備份恢復過程，對增量和全量各種一次prepare即可； 全量的prepare； [root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf --prepare --target-dir=/data/back_up xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined) xtrabackup: cd to /data/back_up xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(893330211) xtrabackup: Temporary instance for recovery is set as followings. 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 130327 14:40:53 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 130327 14:40:53 InnoDB: The InnoDB memory heap is disabled 130327 14:40:53 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130327 14:40:53 InnoDB: Compressed tables use zlib 1.2.3 130327 14:40:53 InnoDB: Using Linux native AIO 130327 14:40:53 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 130327 14:40:53 InnoDB: Initializing buffer pool, size = 100.0M 130327 14:40:53 InnoDB: Completed initialization of buffer pool 130327 14:40:53 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 130327 14:40:53 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Last MySQL binlog file position 0 1747, file name ./mysql-bin.000004 130327 14:40:54 InnoDB: Waiting for the background threads to start 130327 14:40:55 InnoDB: 1.1.5 started; log sequence number 893330211 [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 1747, file name ./mysql-bin.000004 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130327 14:40:55 InnoDB: Starting shutdown... 130327 14:40:56 InnoDB: Shutdown completed; log sequence number 893330449 [root@Slave02 delta]# 增量的prepare； [root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf --prepare --target-dir=/data/back_up --incremental-dir=/data/back_up/delta/ xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined) incremental backup from 893330211 is enabled. xtrabackup: cd to /data/back_up xtrabackup: This target seems to be already prepared. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(893330821) xtrabackup: page size for /data/back_up/delta//ibdata1.delta is 16384 bytes Applying /data/back_up/delta//ibdata1.delta ... xtrabackup: page size for /data/back_up/delta//inno/mm.ibd.delta is 16384 bytes Applying /data/back_up/delta//inno/mm.ibd.delta ... xtrabackup: page size for /data/back_up/delta//inno/t.ibd.delta is 16384 bytes Applying /data/back_up/delta//inno/t.ibd.delta ... xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = /data/back_up/delta/ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 130327 14:42:49 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 130327 14:42:49 InnoDB: The InnoDB memory heap is disabled 130327 14:42:49 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130327 14:42:49 InnoDB: Compressed tables use zlib 1.2.3 130327 14:42:49 InnoDB: Using Linux native AIO 130327 14:42:49 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 130327 14:42:49 InnoDB: Initializing buffer pool, size = 100.0M 130327 14:42:49 InnoDB: Completed initialization of buffer pool 130327 14:42:49 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 130327 14:42:49 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Last MySQL binlog file position 0 474, file name ./mysql-bin.000005 130327 14:42:49 InnoDB: Waiting for the background threads to start 130327 14:42:50 InnoDB: 1.1.5 started; log sequence number 893330821 [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 474, file name ./mysql-bin.000005 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130327 14:42:50 InnoDB: Starting shutdown... 130327 14:42:51 InnoDB: Shutdown completed; log sequence number 893330821 [root@Slave02 delta]# [root@Slave02 data]# rm -rf inno/ ----刪除數據目錄中的inno庫的所有文件 [root@Slave02 data]# ls book mysql mysql-bin.000005 prod ibdata1 mysql-bin.000001 mysql-bin.index Slave02.err ib_logfile0 mysql-bin.000002 mysqld_multi.log Slave02.pid ib_logfile1 mysql-bin.000003 mysql.pid slow.log log.1 mysql-bin.000004 performance_schema test [root@Slave02 data]# cp -r /data/back_up/inno/ /usr/local/mysql/data/ ---從備份目錄中拷貝全備的相關文件到數據庫目錄中去 [root@Slave02 data]# cd inno/ [root@Slave02 inno]# ll total 2096 -rw-r----- 1 root root 8554 Mar 27 14:49 mm.frm -rw-r--r-- 1 root root 1048576 Mar 27 14:49 mm.ibd -rw-r----- 1 root root 8554 Mar 27 14:49 t.frm -rw-r--r-- 1 root root 1048576 Mar 27 14:49 t.ibd [root@Slave02 inno]# chown -R mysql:mysql /usr/local/mysql/data/inno/ ----修改權限和全備的走的流程一樣 [root@Slave02 inno]# ls -la total 2112 drwx------ 2 mysql mysql 4096 Mar 27 14:49 . drwxr-xr-x 8 mysql mysql 4096 Mar 27 14:49 .. -rw-r----- 1 mysql mysql 8554 Mar 27 14:49 mm.frm -rw-r--r-- 1 mysql mysql 1048576 Mar 27 14:49 mm.ibd -rw-r----- 1 mysql mysql 8554 Mar 27 14:49 t.frm -rw-r--r-- 1 mysql mysql 1048576 Mar 27 14:49 t.ibd [root@Slave02 inno]# service mysqld restart -----重新啟動數據庫即可 Shutting down MySQL... [ OK ] Starting MySQL.. [ OK ] [root@Slave02 inno]# mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.13-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use inno Database changed mysql> show tables; ---嚴重增量備份的數據是否有效 +----------------+ | Tables_in_inno | +----------------+ | mm | | t | +----------------+ 2 rows in set (0.00 sec) mysql> select * from mm; +------+ | a | +------+ | 1 | | 2 | | 3 | | 4 | | 9999 | +------+ 5 rows in set (0.01 sec) mysql> select * from t; +------+ | a | +------+ | 1 | | 2 | | 3 | | 9999 | +------+ 4 rows in set (0.00 sec) mysql> -------經校驗無誤，增量備份成功； 增量備份優點： 1、數據庫太大沒有足夠的空間全量備份，作增量備份有效節省空間，且效率高。 2、支持熱備份。備份過程不鎖表，不受時間限制，不影響用戶使用。 3、每日備份只產生少量數據，遠程備份傳輸更方便。同時節省空間。 4、備份恢復基於文件操作，降低直接對數據庫操作風險。 5、備份效率更高，恢復效率更高。 6、現在的增量備份可以通道來進行備份比以前老版本的速度快很多，大家可以使用，目前測試，生產上可以12個通道一起開通，上百G的數據文件，增備很快便可完成
