MySQL 利用xtrabackup进行增量备份详细过程汇总 (转)

Xtrabackup下载、安装以及全量备份请参考：http://blog.itpub.net/26230597/viewspace-1465772/

1，创建mysql备份用户

mysql -uroot --password="" -e"CREATE USER 'backup'@'192.168.%' IDENTIFIED BY '123456'";

mysql -uroot --password="" -e"GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPERON *.* TO 'backup'@'192.168.%'";

2，xtrabackup进行全量备份

备份命令：

xtrabackup --defaults-file=/etc/my.cnf--user=backup --password="123456" --port=3306 --backup--target-dir=/data/backups/mysql/full_incre_$(date +%Y%m%d)

PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。

 备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 ~]# xtrabackup--defaults-file=/etc/my.cnf --user=backup --password="123456"--port=3306 --backup --target-dir=/data/backups/mysql/full_incre_$(date+%Y%m%d)  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. xtrabackup: uses posix_fadvise().  
  4. xtrabackup: cd to /home/data/mysql/data  
  5. xtrabackup: open files limit requested 0,set to 1024  
  6. xtrabackup: using the following InnoDBconfiguration:  
  7. xtrabackup:   innodb_data_home_dir = ./  
  8. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  9. xtrabackup:   innodb_log_group_home_dir = ./  
  10. xtrabackup:   innodb_log_files_in_group = 3  
  11. xtrabackup:   innodb_log_file_size = 67108864  
  12. >> log scanned up to (423920168)  
  13. xtrabackup: Generating a list of tablespaces  
  14. [01] Copying ./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1  
  15. >> log scanned up to (423920168)  
  16. >> log scanned up to (423920168)  
  17. [01]       ...done  
  18. [01] Copying ./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2  
  19. >> log scanned up to (423920168)  
  20. >> log scanned up to (423920168)  
  21. [01]       ...done  
  22. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd  
  23. [01]       ...done  
  24. [01] Copying ./mysql/slave_relay_log_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd  
  25. [01]       ...done  
  26. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd  
  27. [01]       ...done  
  28. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd  
  29. [01]       ...done  
  30. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd  
  31. [01]       ...done  
  32. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd  
  33. [01]       ...done  
  34. [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd  
  35. [01]       ...done  
  36. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd  
  37. [01]       ...done  
  38. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd  
  39. [01]       ...done  
  40. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd  
  41. [01]       ...done  
  42. [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd  
  43. [01]       ...done  
  44. [01] Copying ./sbtest/sbtest7.ibd to /data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd  
  45. [01]       ...done  
  46. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd  
  47. [01]       ...done  
  48. [01] Copying ./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd  
  49. [01]       ...done  
  50. [01] Copying ./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd  
  51. [01]       ...done  
  52. [01] Copying ./test/t.ibd to/data/backups/mysql/full_incre_20150319/test/t.ibd  
  53. [01]       ...done  
  54. [01] Copying ./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd  
  55. [01]       ...done  
  56. [01] Copying ./user_db/test.ibd to/data/backups/mysql/full_incre_20150319/user_db/test.ibd  
  57. [01]       ...done  
  58. [01] Copying ./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd  
  59. [01]       ...done  
  60. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd  
  61. [01]       ...done  
  62. [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd  
  63. >> log scanned up to (423920168)  
  64. [01]       ...done  
  65. [01] Copying ./user_db/t.ibd to/data/backups/mysql/full_incre_20150319/user_db/t.ibd  
  66. [01]       ...done  
  67. xtrabackup: The latest check point (forincremental): '423920168'  
  68. xtrabackup: Stopping log copying thread.  
  69. .>> log scanned up to (423920168)  
  70.    
  71. xtrabackup: Transaction log of lsn(423920168) to (423920168) was copied.  
  72. [root@data01 ~]#  



3，xtrabackup进行增量备份

3.1先录入一张表，实现第一次增量数据：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> create table t4 select 'aaa' asa, 1 as b;  
  2. Query OK, 1 row affected (0.36 sec)  
  3. Records: 1 Duplicates: 0  Warnings: 0  
  4.    
  5. mysql>  
  6. mysql> insert into t4 select 'bbb',2;  
  7. Query OK, 1 row affected (0.05 sec)  
  8. Records: 1 Duplicates: 0  Warnings: 0  
  9.    
  10. mysql>  



3.2 再进行增量备份1

xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150320/--incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log

查看备份日志信息：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 ~]# more/tmp/incre_backup_1.log  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. incremental backup from 423920168 isenabled.  
  4. xtrabackup: uses posix_fadvise().  
  5. xtrabackup: cd to /home/data/mysql/data  
  6. xtrabackup: open files limit requested 0,set to 1024  
  7. xtrabackup: using the following InnoDBconfiguration:  
  8. xtrabackup:   innodb_data_home_dir = ./  
  9. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  10. xtrabackup:   innodb_log_group_home_dir = ./  
  11. xtrabackup:   innodb_log_files_in_group = 3  
  12. xtrabackup:   innodb_log_file_size = 67108864  
  13. >> log scanned up to (423925813)  
  14. xtrabackup: Generating a list oftablespaces  
  15. xtrabackup: using the full scan forincremental backup  
  16. [01] Copying ./IBdata1 to/data/backups/mysql/incre_20150320/IBdata1.delta  
  17. >> log scanned up to (423925813)  
  18. [01]       ...done  
  19. [01] Copying ./IBdata2 to/data/backups/mysql/incre_20150320/IBdata2.delta  
  20. >> log scanned up to (423925813)  
  21. [01]       ...done  
  22. >> log scanned up to (423925813)  
  23. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta  
  24. [01]       ...done  
  25. [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta  
  26. [01]       ...done  
  27. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta  
  28. [01]       ...done  
  29. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta  
  30. [01]       ...done  
  31. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta  
  32. [01]       ...done  
  33. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta  
  34. [01]       ...done  
  35. [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta  
  36. [01]       ...done  
  37. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta  
  38. [01]       ...done  
  39. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta  
  40. [01]       ...done  
  41. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta  
  42. [01]       ...done  
  43. [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta  
  44. [01]       ...done  
  45. [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta  
  46. [01]       ...done  
  47. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta  
  48. [01]       ...done  
  49. [01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150320/test/t1.ibd.delta  
  50. [01]       ...done  
  51. [01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150320/test/t4.ibd.delta  
  52. [01]       ...done  
  53. [01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150320/test/t3.ibd.delta  
  54. [01]       ...done  
  55. [01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150320/test/t.ibd.delta  
  56. [01]       ...done  
  57. [01] Copying ./user_db/t1.ibd to /data/backups/mysql/incre_20150320/user_db/t1.ibd.delta  
  58. [01]       ...done  
  59. [01] Copying ./user_db/test.ibd to/data/backups/mysql/incre_20150320/user_db/test.ibd.delta  
  60. [01]       ...done  
  61. >> log scanned up to (423925813)  
  62. [01] Copying ./user_db/test2.ibd to /data/backups/mysql/incre_20150320/user_db/test2.ibd.delta  
  63. [01]       ...done  
  64. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta  
  65. [01]       ...done  
  66. [01] Copying ./user_db/UC_USER.ibd to /data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta  
  67. [01]       ...done  
  68. [01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150320/user_db/t.ibd.delta  
  69. [01]       ...done  
  70. xtrabackup: The latest check point (forincremental): '423925813'  
  71. xtrabackup: Stopping log copying thread.  
  72. .>> log scanned up to (423925813)  
  73.    
  74. xtrabackup: Transaction log of lsn(423925813) to (423925813) was copied.  
  75.    



3.3 再建立表2，实现第二天增量数据

建立一张表t_incre_2：
[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> use test;  
  2. Database changed  
  3. mysql> create table t_incre_2 select'cc' as a,2 as b;  
  4. Query OK, 1 row affected (0.25 sec)  
  5. Records: 1 Duplicates: 0  Warnings: 0  
  6.    
  7. mysql> insert into t_incre_2 select'dd',2;  
  8. Query OK, 1 row affected (0.05 sec)  
  9. Records: 1 Duplicates: 0  Warnings: 0  
  10.    
  11. mysql> select * from t_incre_2;  
  12. +----+---+  
  13. | a | b |  
  14. +----+---+  
  15. | cc | 2 |  
  16. | dd | 2 |  
  17. +----+---+  
  18. 2 rows in set (0.05 sec)  
  19.    
  20. mysql>  



3.4 进行第二天的增量备份

备份命令：

xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150321/--incremental-basedir=/data/backups/mysql/incre_20150320 2>/tmp/incre_backup_2.log

备份信息记录如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 ~]# more /tmp/incre_backup_2.log  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. incremental backup from 423925813 isenabled.  
  4. xtrabackup: uses posix_fadvise().  
  5. xtrabackup: cd to /home/data/mysql/data  
  6. xtrabackup: open files limit requested 0,set to 1024  
  7. xtrabackup: using the following InnoDBconfiguration:  
  8. xtrabackup:   innodb_data_home_dir = ./  
  9. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  10. xtrabackup:   innodb_log_group_home_dir = ./  
  11. xtrabackup:   innodb_log_files_in_group = 3  
  12. xtrabackup:   innodb_log_file_size = 67108864  
  13. >> log scanned up to (423931610)  
  14. xtrabackup: Generating a list oftablespaces  
  15. xtrabackup: using the full scan forincremental backup  
  16. [01] Copying ./IBdata1 to/data/backups/mysql/incre_20150321/IBdata1.delta  
  17. >> log scanned up to (423931610)  
  18. >> log scanned up to (423931610)  
  19. >> log scanned up to (423931610)  
  20. >> log scanned up to (423931610)  
  21. >> log scanned up to (423931610)  
  22. [01]       ...done  
  23. >> log scanned up to (423931610)  
  24. [01] Copying ./IBdata2 to/data/backups/mysql/incre_20150321/IBdata2.delta  
  25. >> log scanned up to (423931610)  
  26. >> log scanned up to (423931610)  
  27. [01]       ...done  
  28. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta  
  29. [01]       ...done  
  30. [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta  
  31. [01]       ...done  
  32. >> log scanned up to (423931610)  
  33. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta  
  34. [01]       ...done  
  35. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta  
  36. [01]       ...done  
  37. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta  
  38. [01]       ...done  
  39. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta  
  40. [01]       ...done  
  41. [01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta  
  42. [01]       ...done  
  43. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta  
  44. [01]       ...done  
  45. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta  
  46. [01]       ...done  
  47. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta  
  48. [01]       ...done  
  49. [01] Copying ./sbtest/sbtest6.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta  
  50. [01]       ...done  
  51. [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta  
  52. [01]       ...done  
  53. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta  
  54. [01]       ...done  
  55. [01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150321/test/t1.ibd.delta  
  56. [01]       ...done  
  57. [01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150321/test/t4.ibd.delta  
  58. [01]       ...done  
  59. [01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150321/test/t3.ibd.delta  
  60. [01]       ...done  
  61. >> log scanned up to (423931610)  
  62. [01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150321/test/t.ibd.delta  
  63. [01]       ...done  
  64. [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta  
  65. [01]       ...done  
  66. [01] Copying ./user_db/t1.ibd to/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta  
  67. [01]       ...done  
  68. [01] Copying ./user_db/test.ibd to /data/backups/mysql/incre_20150321/user_db/test.ibd.delta  
  69. [01]       ...done  
  70. [01] Copying ./user_db/test2.ibd to/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta  
  71. [01]       ...done  
  72. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta  
  73. [01]       ...done  
  74. [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta  
  75. [01]       ...done  
  76. [01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150321/user_db/t.ibd.delta  
  77. [01]       ...done  
  78. >> log scanned up to (423931610)  
  79. xtrabackup: The latest check point (forincremental): '423931610'  
  80. xtrabackup: Stopping log copying thread.  
  81. .>> log scanned up to (423931610)  
  82.    
  83. xtrabackup: Transaction log of lsn(423931610) to (423931610) was copied.  


 

4，xtrabackup进行增量恢复

4.1为了验证比对，先删除两个增量备份前表里面的数据

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> delete from t4;  
  2. Query OK, 2 rows affected (0.05 sec)  
  3.    
  4. mysql> delete from t_incre_2;  
  5. Query OK, 2 rows affected (0.04 sec)  
  6.    
  7. mysql> select * from t4;  
  8. Empty set (0.00 sec)  
  9.    
  10. mysql> select * from t_incre_2;  
  11. Empty set (0.00 sec)  
  12.    
  13. mysql>  



4.2开始全备份恢复PREPARE THE BASE BACKUP

备份命令如下：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"  --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/full_restore.log

 

备份信息记录如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --prepare --user=backup--password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. xtrabackup: cd to/data/backups/mysql/full_incre_20150319  
  4. xtrabackup: This target seems to be alreadyprepared.  
  5. xtrabackup: notice: xtrabackup_logfile wasalready used to '--prepare'.  
  6. xtrabackup: using the following InnoDBconfiguration for recovery:  
  7. xtrabackup:   innodb_data_home_dir = ./  
  8. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  9. xtrabackup:   innodb_log_group_home_dir = ./  
  10. xtrabackup:   innodb_log_files_in_group = 3  
  11. xtrabackup:   innodb_log_file_size = 67108864  
  12. xtrabackup: using the following InnoDBconfiguration for recovery:  
  13. xtrabackup:   innodb_data_home_dir = ./  
  14. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  15. xtrabackup:   innodb_log_group_home_dir = ./  
  16. xtrabackup:   innodb_log_files_in_group = 3  
  17. xtrabackup:   innodb_log_file_size = 67108864  
  18. xtrabackup: Starting InnoDB instance forrecovery.  
  19. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)  
  20. InnoDB: Using atomics to ref count bufferpool pages  
  21. InnoDB: The InnoDB memory heap is disabled  
  22. InnoDB: Mutexes and rw_locks use GCC atomicbuiltins  
  23. InnoDB: Memory barrier is not used  
  24. InnoDB: Compressed tables use zlib 1.2.3  
  25. InnoDB: Using CPU crc32 instructions  
  26. InnoDB: Initializing buffer pool, size =100.0M  
  27. InnoDB: Completed initialization of bufferpool  
  28. InnoDB: Setting log file ./ib_logfile101size to 64 MB  
  29. InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB  
  30. InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB  
  31. InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0  
  32. InnoDB: New log files created,LSN=423920168  
  33. InnoDB: Highest supported file format isBarracuda.  
  34.    
  35. [notice (again)]  
  36.   Ifyou use binary log and don't use any hack of group commit,  
  37.   thebinary log position seems to be:  
  38. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  39.    
  40. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1  
  41. InnoDB: Starting shutdown...  
  42. InnoDB: Shutdown completed; log sequencenumber 423920652  
  43. [root@data01 mysql]#  


 

4.3 恢复到第一次增量的时刻

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"  --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_20150320 2>/tmp/restore_incre_1.log

恢复过程：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 mysql]# more /tmp/restore_incre_1.log  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. incremental backup from 423920168 isenabled.  
  4. xtrabackup: cd to/data/backups/mysql/full_incre_20150319  
  5. xtrabackup: This target seems to be alreadyprepared.  
  6. xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423925813)  
  7. xtrabackup: using the following InnoDBconfiguration for recovery:  
  8. xtrabackup:   innodb_data_home_dir = ./  
  9. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  10. xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320  
  11. xtrabackup:   innodb_log_files_in_group = 1  
  12. xtrabackup:   innodb_log_file_size = 2097152  
  13. xtrabackup: Generating a list oftablespaces  
  14. xtrabackup: page size for /data/backups/mysql/incre_20150320/IBdata2.deltais 16384 bytes  
  15. Applying/data/backups/mysql/incre_20150320/IBdata2.delta to ./IBdata2...  
  16. xtrabackup: page size for/data/backups/mysql/incre_20150320/IBdata1.delta is 16384 bytes  
  17. Applying /data/backups/mysql/incre_20150320/IBdata1.deltato ./IBdata1...  
  18. xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta is 16384bytes  
  19. Applying/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...  
  20. xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta is 16384bytes  
  21. Applying/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...  
  22. xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta is 16384bytes  
  23. Applying/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...  
  24. xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta is16384 bytes  
  25. Applying/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...  
  26. xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta is 16384bytes  
  27. Applying/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta to./mysql/slave_master_info.ibd...  
  28. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta is 16384 bytes  
  29. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...  
  30. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta is 16384 bytes  
  31. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...  
  32. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta is 16384 bytes  
  33. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...  
  34. xtrabackup: page size for /data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.deltais 16384 bytes  
  35. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...  
  36. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta is 16384 bytes  
  37. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...  
  38. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta is 16384 bytes  
  39. Applying /data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.deltato ./sbtest/sbtest8.ibd...  
  40. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta is 16384 bytes  
  41. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...  
  42. xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta is 16384 bytes  
  43. Applying/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...  
  44. xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t3.ibd.delta is 16384 bytes  
  45. Applying/data/backups/mysql/incre_20150320/test/t3.ibd.delta to ./test/t3.ibd...  
  46. xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t.ibd.delta is 16384 bytes  
  47. Applying/data/backups/mysql/incre_20150320/test/t.ibd.delta to ./test/t.ibd...  
  48. xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t4.ibd.delta is 16384 bytes  
  49. Applying/data/backups/mysql/incre_20150320/test/t4.ibd.delta to ./test/t4.ibd...  
  50. xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t1.ibd.delta is 16384 bytes  
  51. Applying/data/backups/mysql/incre_20150320/test/t1.ibd.delta to ./test/t1.ibd...  
  52. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta is 16384 bytes  
  53. Applying /data/backups/mysql/incre_20150320/user_db/t3.ibd.deltato ./user_db/t3.ibd...  
  54. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t.ibd.delta is 16384 bytes  
  55. Applying/data/backups/mysql/incre_20150320/user_db/t.ibd.delta to ./user_db/t.ibd...  
  56. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta is 16384 bytes  
  57. Applying/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta to./user_db/test2.ibd...  
  58. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test.ibd.delta is 16384 bytes  
  59. Applying/data/backups/mysql/incre_20150320/user_db/test.ibd.delta to./user_db/test.ibd...  
  60. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta is 16384 bytes  
  61. Applying/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta to ./user_db/t1.ibd...  
  62. xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta is 16384 bytes  
  63. Applying/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...  
  64. xtrabackup: using the following InnoDBconfiguration for recovery:  
  65. xtrabackup:   innodb_data_home_dir = ./  
  66. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  67. xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320  
  68. xtrabackup:   innodb_log_files_in_group = 1  
  69. xtrabackup:   innodb_log_file_size = 2097152  
  70. xtrabackup: Starting InnoDB instance forrecovery.  
  71. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)  
  72. InnoDB: Using atomics to ref count bufferpool pages  
  73. InnoDB: The InnoDB memory heap is disabled  
  74. InnoDB: Mutexes and rw_locks use GCC atomicbuiltins  
  75. InnoDB: Memory barrier is not used  
  76. InnoDB: Compressed tables use zlib 1.2.3  
  77. InnoDB: Using CPU crc32 instructions  
  78. InnoDB: Initializing buffer pool, size = 100.0M  
  79. InnoDB: Completed initialization of bufferpool  
  80. InnoDB: Highest supported file format isBarracuda.  
  81. InnoDB: The log sequence numbers 423920652and 423920652 in ibdata files do not match the log sequence number 423925813 inthe ib_logfiles!  
  82. InnoDB: Database was not shutdown normally!  
  83. InnoDB: Starting crash recovery.  
  84. InnoDB: Reading tablespace information fromthe .ibd files...  
  85. InnoDB: Restoring possible half-writtendata pages  
  86. InnoDB: from the doublewrite buffer...  
  87. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  88.    
  89. [notice (again)]  
  90.   Ifyou use binary log and don't use any hack of group commit,  
  91.   thebinary log position seems to be:  
  92. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  93.    
  94. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1  
  95. InnoDB: Starting shutdown...  
  96. InnoDB: Shutdown completed; log sequencenumber 423925813  


 

4.4 恢复到第二次增量备份前面：

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"  --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log

恢复过程：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 mysql]# more/tmp/restore_incre_2.log  
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  3. incremental backup from 423925813 isenabled.  
  4. xtrabackup: cd to/data/backups/mysql/full_incre_20150319  
  5. xtrabackup: This target seems to be alreadyprepared.  
  6. xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423931610)  
  7. xtrabackup: using the following InnoDBconfiguration for recovery:  
  8. xtrabackup:   innodb_data_home_dir = ./  
  9. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  10. xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321  
  11. xtrabackup:   innodb_log_files_in_group = 1  
  12. xtrabackup:   innodb_log_file_size = 2097152  
  13. xtrabackup: Generating a list oftablespaces  
  14. xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata2.delta is 16384 bytes  
  15. Applying /data/backups/mysql/incre_20150321/IBdata2.deltato ./IBdata2...  
  16. xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata1.delta is 16384 bytes  
  17. Applying/data/backups/mysql/incre_20150321/IBdata1.delta to ./IBdata1...  
  18. xtrabackup: page size for /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.deltais 16384 bytes  
  19. Applying/data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...  
  20. xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta is 16384bytes  
  21. Applying/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...  
  22. xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta is 16384bytes  
  23. Applying/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...  
  24. xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta is16384 bytes  
  25. Applying/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...  
  26. xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta is 16384bytes  
  27. Applying /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.deltato ./mysql/slave_master_info.ibd...  
  28. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta is 16384 bytes  
  29. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...  
  30. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta is 16384 bytes  
  31. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...  
  32. xtrabackup: page size for /data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.deltais 16384 bytes  
  33. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...  
  34. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta is 16384 bytes  
  35. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...  
  36. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta is 16384 bytes  
  37. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...  
  38. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta is 16384 bytes  
  39. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta to./sbtest/sbtest8.ibd...  
  40. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta is 16384 bytes  
  41. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...  
  42. xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta is 16384 bytes  
  43. Applying/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...  
  44. xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t3.ibd.delta is 16384 bytes  
  45. Applying /data/backups/mysql/incre_20150321/test/t3.ibd.deltato ./test/t3.ibd...  
  46. xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta is 16384 bytes  
  47. Applying/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta to./test/t_incre_2.ibd...  
  48. xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t.ibd.delta is 16384 bytes  
  49. Applying/data/backups/mysql/incre_20150321/test/t.ibd.delta to ./test/t.ibd...  
  50. xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t4.ibd.delta is 16384 bytes  
  51. Applying/data/backups/mysql/incre_20150321/test/t4.ibd.delta to ./test/t4.ibd...  
  52. xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t1.ibd.delta is 16384 bytes  
  53. Applying/data/backups/mysql/incre_20150321/test/t1.ibd.delta to ./test/t1.ibd...  
  54. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta is 16384 bytes  
  55. Applying/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta to ./user_db/t3.ibd...  
  56. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t.ibd.delta is 16384 bytes  
  57. Applying/data/backups/mysql/incre_20150321/user_db/t.ibd.delta to ./user_db/t.ibd...  
  58. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta is 16384 bytes  
  59. Applying/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta to./user_db/test2.ibd...  
  60. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test.ibd.delta is 16384 bytes  
  61. Applying/data/backups/mysql/incre_20150321/user_db/test.ibd.delta to./user_db/test.ibd...  
  62. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta is 16384 bytes  
  63. Applying/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta to ./user_db/t1.ibd...  
  64. xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta is 16384 bytes  
  65. Applying/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...  
  66. xtrabackup: using the following InnoDBconfiguration for recovery:  
  67. xtrabackup:   innodb_data_home_dir = ./  
  68. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  69. xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321  
  70. xtrabackup:   innodb_log_files_in_group = 1  
  71. xtrabackup:   innodb_log_file_size = 2097152  
  72. xtrabackup: Starting InnoDB instance forrecovery.  
  73. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)  
  74. InnoDB: Using atomics to ref count bufferpool pages  
  75. InnoDB: The InnoDB memory heap is disabled  
  76. InnoDB: Mutexes and rw_locks use GCC atomicbuiltins  
  77. InnoDB: Memory barrier is not used  
  78. InnoDB: Compressed tables use zlib 1.2.3  
  79. InnoDB: Using CPU crc32 instructions  
  80. InnoDB: Initializing buffer pool, size =100.0M  
  81. InnoDB: Completed initialization of bufferpool  
  82. InnoDB: Highest supported file format isBarracuda.  
  83. InnoDB: The log sequence numbers 423925813and 423925813 in ibdata files do not match the log sequence number 423931610 inthe ib_logfiles!  
  84. InnoDB: Database was not shutdown normally!  
  85. InnoDB: Starting crash recovery.  
  86. InnoDB: Reading tablespace information fromthe .ibd files...  
  87. InnoDB: Restoring possible half-writtendata pages  
  88. InnoDB: from the doublewrite buffer...  
  89. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  90.    
  91. [notice (again)]  
  92.   Ifyou use binary log and don't use any hack of group commit,  
  93.   thebinary log position seems to be:  
  94. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  95.    
  96. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1  
  97. InnoDB: Starting shutdown...  
  98. InnoDB: Shutdown completed; log sequencenumber 423931610  
  99. [root@data01 mysql]#  


 

4.5 恢复整个库

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/restore_end_3.log

停止mysql服务：

service mysqld5612 stop

开始rsync数据文件：

cd /data/backups/mysql/full_incre_20150319

rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile'  ./ /home/data/mysql/data

授予mysql访问权限：

chown -R mysql:mysql /home/data/mysql/data

启动mysql服务：

service mysqld5612 start

4.6，验证

登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> select * from t4;  
  2. +-----+---+  
  3. | a  | b |  
  4. +-----+---+  
  5. | aaa | 1 |  
  6. | bbb | 2 |  
  7. +-----+---+  
  8. 2 rows in set (0.05 sec)  
  9.    
  10. mysql> select * from t_incre_2;  
  11. +----+---+  
  12. | a | b |  
  13. +----+---+  
  14. | cc | 2 |  
  15. | dd | 2 |  
  16. +----+---+  
  17. 2 rows in set (0.06 sec)  
  18.    
  19. mysql>  



5，innobackupex先做全量备份

命令如下：

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/ --no-timestamp 2>/tmp/innobackupex_full.log

备份过程信息：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 mysql]# more/tmp/innobackupex_full.log  
  2.    
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy  
  4. and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.  
  5.    
  6. This software is published under  
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.  
  8.    
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:  
  10. http://www.percona.com/xb/p  
  11.    
  12. 150319 02:31:18  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.52.129'as 'backup'  (using  
  13.  password: YES).  
  14. 150319 02:31:18  innobackupex: Connected to MySQL server  
  15. 150319 02:31:18  innobackupex: Executing a version checkagainst the server...  
  16. 150319 02:31:18  innobackupex: Done.  
  17. 150319 02:31:18  innobackupex: Starting the backup operation  
  18.    
  19. IMPORTANT: Please check that the backup runcompletes successfully.  
  20.           At the end of a successful backup run innobackupex  
  21.           prints "completed OK!".  
  22.    
  23. innobackupex:  Using server version 5.6.12-log  
  24.    
  25. innobackupex: Created backup directory/data/backups/mysql/innobackupex_full_20150319  
  26.    
  27. 150319 02:31:18  innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/  
  28. innobackupex_full_20150319 --tmpdir=/tmp--extra-lsndir='/tmp'  
  29. innobackupex: Waiting for ibbackup(pid=13118) to suspend  
  30. innobackupex: Suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2'  
  31.    
  32. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  33. xtrabackup: uses posix_fadvise().  
  34. xtrabackup: cd to /home/data/mysql/data  
  35. xtrabackup: open files limit requested 0, setto 1024  
  36. xtrabackup: using the following InnoDBconfiguration:  
  37. xtrabackup:   innodb_data_home_dir = ./  
  38. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  39. xtrabackup:   innodb_log_group_home_dir = ./  
  40. xtrabackup:   innodb_log_files_in_group = 3  
  41. xtrabackup:   innodb_log_file_size = 67108864  
  42. >> log scanned up to (423920964)  
  43. xtrabackup: Generating a list oftablespaces  
  44. [01] Copying ./IBdata1 to/data/backups/mysql/innobackupex_full_20150319/IBdata1  
  45. >> log scanned up to (423920964)  
  46. [01]       ...done  
  47. [01] Copying ./IBdata2 to/data/backups/mysql/innobackupex_full_20150319/IBdata2  
  48. >> log scanned up to (423920964)  
  49. >> log scanned up to (423920964)  
  50. [01]       ...done  
  51. >> log scanned up to (423920964)  
  52. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.ibd  
  53. [01]       ...done  
  54. [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.ibd  
  55. [01]       ...done  
  56. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.ibd  
  57. [01]       ...done  
  58. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.ibd  
  59. [01]       ...done  
  60. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.ibd  
  61. [01]       ...done  
  62. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.ibd  
  63. [01]       ...done  
  64. [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.ibd  
  65. [01]       ...done  
  66. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.ibd  
  67. [01]       ...done  
  68. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.ibd  
  69. [01]       ...done  
  70. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.ibd  
  71. [01]       ...done  
  72. [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.ibd  
  73. [01]       ...done  
  74. [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.ibd  
  75. [01]       ...done  
  76. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.ibd  
  77. [01]       ...done  
  78. [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t1.ibd  
  79. [01]       ...done  
  80. [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t4.ibd  
  81. [01]       ...done  
  82. [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t3.ibd  
  83. [01]       ...done  
  84. [01] Copying ./test/t.ibd to /data/backups/mysql/innobackupex_full_20150319/test/t.ibd  
  85. [01]       ...done  
  86. [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.ibd  
  87. [01]       ...done  
  88. [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t1.ibd  
  89. [01]       ...done  
  90. [01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test.ibd  
  91. [01]       ...done  
  92. [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test2.ibd  
  93. [01]       ...done  
  94. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t3.ibd  
  95. [01]       ...done  
  96. [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.ibd  
  97. >> log scanned up to (423920964)  
  98. [01]       ...done  
  99. [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t.ibd  
  100. [01]       ...done  
  101. xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2' withpid '13118'  
  102.    
  103. 150319 02:31:24  innobackupex: Continuing after ibbackup hassuspended  
  104. 150319 02:31:24  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...  
  105. 150319 02:31:24  innobackupex: All tables locked and flushedto disk  
  106.    
  107. 150319 02:31:24  innobackupex: Starting to backup non-InnoDBtables and files  
  108. innobackupex: in subdirectories of'/home/data/mysql/data/'  
  109. innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)  
  110. >> log scanned up to (423920964)  
  111. innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)  
  112. >> log scanned up to (423920964)  
  113. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'  
  114. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'  
  115. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'  
  116. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'  
  117. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'  
  118. innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'  
  119. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'  
  120. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'  
  121. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'  
  122. innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'  
  123. innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'  
  124. innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'  
  125. innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'  
  126. innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'  
  127. innobackupex: Backing up file '/home/data/mysql/data//user_db/t.frm'  
  128. innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'  
  129. innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'  
  130. innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'  
  131. innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'  
  132. innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'  
  133. 150319 02:31:26  innobackupex: Finished backing up non-InnoDBtables and files  
  134.    
  135. 150319 02:31:26  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...  
  136. 150319 02:31:26  innobackupex: Waiting for log copying tofinish  
  137.    
  138. >> log scanned up to (423920964)  
  139. xtrabackup: The latest check point (forincremental): '423920964'  
  140. xtrabackup: Stopping log copying thread.  
  141. .>> log scanned up to (423920964)  
  142.    
  143. xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_log_copied' with pid'13118'  
  144. xtrabackup: Transaction log of lsn(423920964) to (423920964) was copied.  
  145. 150319 02:31:27  innobackupex: All tables unlocked  
  146.    
  147. innobackupex: Backup created in directory'/data/backups/mysql/innobackupex_full_20150319'  
  148. innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120  
  149. 150319 02:31:27  innobackupex: Connection to database serverclosed  
  150. 150319 02:31:27  innobackupex: completed OK!  
  151. [root@data01 mysql]#  


 

6，innobackupex做增量备份

6.1做第一次增量备份

先录入增量数据

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> create table incre1 select'incre1 table ' as incre1, 'a' as id;  
  2. Query OK, 1 row affected (0.06 sec)  
  3. Records: 1 Duplicates: 0  Warnings: 0  
  4.    
  5. mysql> select * from incre1;  
  6. +---------------+----+  
  7. | incre1        | id |  
  8. +---------------+----+  
  9. | incre1 table  | a  |  
  10. +---------------+----+  
  11. 1 row in set (0.00 sec)  
  12.    
  13. mysql>  



再进行增量备份，命令如下：

innobackupex --incremental/data/backups/mysql/innobackex_incre_1--incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_1.log

备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 mysql]# more/tmp/innobackex_incre_1.log  
  2.    
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy  
  4. and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.  
  5.    
  6. This software is published under  
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.  
  8.    
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:  
  10. http://www.percona.com/xb/p  
  11.    
  12. 150319 02:38:04  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES).  
  13. 150319 02:38:04  innobackupex: Connected to MySQL server  
  14. 150319 02:38:04  innobackupex: Executing a version checkagainst the server...  
  15. 150319 02:38:04  innobackupex: Done.  
  16. 150319 02:38:04  innobackupex: Starting the backup operation  
  17.    
  18. IMPORTANT: Please check that the backup runcompletes successfully.  
  19.           At the end of a successful backup run innobackupex  
  20.           prints "completed OK!".  
  21.    
  22. innobackupex:  Using server version 5.6.12-log  
  23.    
  24. innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_1  
  25.    
  26. 150319 02:38:04  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_1 --tmpdir=/tm  
  27. p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackupex_full_20150319/'  
  28. innobackupex: Waiting for ibbackup(pid=13358) to suspend  
  29. innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2'  
  30.    
  31. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  32. incremental backup from 423920964 isenabled.  
  33. xtrabackup: uses posix_fadvise().  
  34. xtrabackup: cd to /home/data/mysql/data  
  35. xtrabackup: open files limit requested 0,set to 1024  
  36. xtrabackup: using the following InnoDBconfiguration:  
  37. xtrabackup:   innodb_data_home_dir = ./  
  38. xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  39. xtrabackup:   innodb_log_group_home_dir = ./  
  40. xtrabackup:   innodb_log_files_in_group = 3  
  41. xtrabackup:   innodb_log_file_size = 67108864  
  42. >> log scanned up to (423925361)  
  43. xtrabackup: Generating a list oftablespaces  
  44. xtrabackup: using the full scan forincremental backup  
  45. >> log scanned up to (423925361)  
  46. >> log scanned up to (423925361)  
  47. >> log scanned up to (423925361)  
  48. >> log scanned up to (423925361)  
  49. [01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_1/IBdata1.delta  
  50. >> log scanned up to (423925361)  
  51. [01]       ...done  
  52. >> log scanned up to (423925361)  
  53. [01] Copying ./IBdata2 to /data/backups/mysql/innobackex_incre_1/IBdata2.delta  
  54. [01]       ...done  
  55. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.ibd.delta  
  56. [01]       ...done  
  57. [01] Copying./mysql/slave_relay_log_info.ibd to /data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.ibd.delta  
  58. [01]       ...done  
  59. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.ibd.delta  
  60. [01]       ...done  
  61. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.ibd.delta  
  62. [01]       ...done  
  63. >> log scanned up to (423925361)  
  64. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.ibd.delta  
  65. [01]       ...done  
  66. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.ibd.delta  
  67. [01]       ...done  
  68. [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.ibd.delta  
  69. [01]       ...done  
  70. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.ibd.delta  
  71. [01]       ...done  
  72. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.ibd.delta  
  73. [01]       ...done  
  74. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.ibd.delta  
  75. [01]       ...done  
  76. [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.ibd.delta  
  77. [01]       ...done  
  78. >> log scanned up to (423925361)  
  79. [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.ibd.delta  
  80. [01]       ...done  
  81. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.ibd.delta  
  82. [01]        ...done  
  83. [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_1/test/t1.ibd.delta  
  84. [01]       ...done  
  85. [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_1/test/t4.ibd.delta  
  86. [01]       ...done  
  87. [01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_1/test/incre1.ibd.delta  
  88. [01]       ...done  
  89. [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_1/test/t3.ibd.delta  
  90. [01]       ...done  
  91. [01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_1/test/t.ibd.delta  
  92. [01]       ...done  
  93. [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackex_incre_1/test/t_incre_2.ibd.delta  
  94. [01]       ...done  
  95. [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t1.ibd.delta  
  96. [01]       ...done  
  97. [01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test.ibd.delta  
  98. [01]       ...done  
  99. [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test2.ibd.delta  
  100. [01]       ...done  
  101. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t3.ibd.delta  
  102. [01]       ...done  
  103. [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.ibd.delta  
  104. [01]       ...done  
  105. [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t.ibd.delta  
  106. [01]       ...done  
  107. >> log scanned up to (423925361)  
  108. xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2' with pid'13358'  
  109. >> log scanned up to (423925361)  
  110.    
  111. 150319 02:38:15  innobackupex: Continuing after ibbackup hassuspended  
  112. 150319 02:38:15  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...  
  113. 150319 02:38:15  innobackupex: All tables locked and flushedto disk  
  114.    
  115. 150319 02:38:15  innobackupex: Starting to backup non-InnoDBtables and files  
  116. innobackupex: in subdirectories of'/home/data/mysql/data/'  
  117. innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)  
  118. >> log scanned up to (423925361)  
  119. innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)  
  120. >> log scanned up to (423925361)  
  121. innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest3.frm'  
  122. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'  
  123. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'  
  124. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'  
  125. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'  
  126. innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'  
  127. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'  
  128. innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest7.frm'  
  129. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'  
  130. innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'  
  131. innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'  
  132. innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'  
  133. innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'  
  134. innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'  
  135. innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'  
  136. innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'  
  137. innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'  
  138. innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'  
  139. innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'  
  140. innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'  
  141. innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'  
  142. 150319 02:38:17  innobackupex: Finished backing up non-InnoDBtables and files  
  143.    
  144. 150319 02:38:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...  
  145. 150319 02:38:17  innobackupex: Waiting for log copying tofinish  
  146.    
  147. xtrabackup: The latest check point (forincremental): '423925361'  
  148. xtrabackup: Stopping log copying thread.  
  149. .>> log scanned up to (423925361)  
  150.    
  151. xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_log_copied' with pid '13358'  
  152. xtrabackup: Transaction log of lsn(423925361) to (423925361) was copied.  
  153. 150319 02:38:18  innobackupex: All tables unlocked  
  154.    
  155. innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_1'  
  156. innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120  
  157. 150319 02:38:18  innobackupex: Connection to database server closed  
  158. 150319 02:38:18  innobackupex: completed OK!  
  159. [root@data01 mysql]#  


 

6.2 基于全备和第一个增量备份来做第二次增量备份

先录入增量数据录入

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> create table incre2 select'incre2' as a,3 as id;  
  2. Query OK, 1 row affected (0.12 sec)  
  3. Records: 1 Duplicates: 0  Warnings: 0  
  4.    
  5. mysql>  
  6. mysql> select * from incre2;  
  7. +--------+----+  
  8. | a     | id |  
  9. +--------+----+  
  10. | incre2 | 3 |  
  11. +--------+----+  
  12. 1 row in set (0.00 sec)  
  13.    
  14. mysql>  
  15.    



开始进行第二次增量备份，备份命令：

innobackupex --incremental/data/backups/mysql/innobackex_incre_2 --incremental-basedir=/data/backups/mysql/innobackex_incre_1/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_2.log

备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. [root@data01 innobackex_incre_1]# more/tmp/innobackex_incre_2.log  
  2.    
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy  
  4. and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.  
  5.    
  6. This software is published under  
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.  
  8.    
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:  
  10. http://www.percona.com/xb/p  
  11.    
  12. 150319 02:51:09  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES).  
  13. 150319 02:51:09  innobackupex: Connected to MySQL server  
  14. 150319 02:51:09  innobackupex: Executing a version checkagainst the server...  
  15. 150319 02:51:09  innobackupex: Done.  
  16. 150319 02:51:09  innobackupex: Starting the backup operation  
  17.    
  18. IMPORTANT: Please check that the backup runcompletes successfully.  
  19.           At the end of a successful backup run innobackupex  
  20.           prints "completed OK!".  
  21.    
  22. innobackupex:  Using server version 5.6.12-log  
  23.    
  24. innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_2  
  25.    
  26. 150319 02:51:10  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_2 --tmpdir=/tm  
  27. p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackex_incre_1/'  
  28. innobackupex: Waiting for ibbackup(pid=13694) to suspend  
  29. innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2'  
  30.    
  31. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )  
  32. incremental backup from 423925361 isenabled.  
  33. xtrabackup: uses posix_fadvise().  
  34. xtrabackup: cd to /home/data/mysql/data  
  35. xtrabackup: open files limit requested 0,set to 1024  
  36. xtrabackup: using the following InnoDBconfiguration:  
  37. xtrabackup:   innodb_data_home_dir = ./  
  38. xtrabackup:  innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend  
  39. xtrabackup:   innodb_log_group_home_dir = ./  
  40. xtrabackup:   innodb_log_files_in_group = 3  
  41. xtrabackup:   innodb_log_file_size = 67108864  
  42. >> log scanned up to (423929601)  
  43. xtrabackup: Generating a list oftablespaces  
  44. xtrabackup: using the full scan forincremental backup  
  45. >> log scanned up to (423929601)  
  46. [01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_2/IBdata1.delta  
  47. >> log scanned up to (423929601)  
  48. [01]       ...done  
  49. [01] Copying ./IBdata2 to/data/backups/mysql/innobackex_incre_2/IBdata2.delta  
  50. [01]       ...done  
  51. [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_master_info.ibd.delta  
  52. [01]       ...done  
  53. >> log scanned up to (423929601)  
  54. [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackex_incre_2/mysql/slave_relay_log_info.ibd.delta  
  55. [01]       ...done  
  56. [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_index_stats.ibd.delta  
  57. [01]       ...done  
  58. [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_worker_info.ibd.delta  
  59. [01]       ...done  
  60. [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_table_stats.ibd.delta  
  61. [01]       ...done  
  62. [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.ibd.delta  
  63. [01]       ...done  
  64. [01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.ibd.delta  
  65. [01]       ...done  
  66. [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.ibd.delta  
  67. [01]       ...done  
  68. [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.ibd.delta  
  69. [01]       ...done  
  70. [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.ibd.delta  
  71. [01]       ...done  
  72. [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.ibd.delta  
  73. [01]       ...done  
  74. [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.ibd.delta  
  75. [01]       ...done  
  76. [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.ibd.delta  
  77. [01]       ...done  
  78. [01] Copying ./test/incre2.ibd to/data/backups/mysql/innobackex_incre_2/test/incre2.ibd.delta  
  79. [01]       ...done  
  80. [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_2/test/t1.ibd.delta  
  81. [01]       ...done  
  82. [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_2/test/t4.ibd.delta  
  83. [01]       ...done  
  84. [01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_2/test/incre1.ibd.delta  
  85. [01]       ...done  
  86. [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_2/test/t3.ibd.delta  
  87. [01]       ...done  
  88. >> log scanned up to (423929601)  
  89. [01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_2/test/t.ibd.delta  
  90. [01]       ...done  
  91. [01] Copying ./test/t_incre_2.ibd to /data/backups/mysql/innobackex_incre_2/test/t_incre_2.ibd.delta  
  92. [01]       ...done  
  93. [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t1.ibd.delta  
  94. [01]       ...done  
  95. [01] Copying ./user_db/test.ibd to /data/backups/mysql/innobackex_incre_2/user_db/test.ibd.delta  
  96. [01]       ...done  
  97. [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_2/user_db/test2.ibd.delta  
  98. [01]       ...done  
  99. [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t3.ibd.delta  
  100. [01]       ...done  
  101. [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.ibd.delta  
  102. [01]       ...done  
  103. [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t.ibd.delta  
  104. [01]       ...done  
  105. >> log scanned up to (423929601)  
  106. xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2' with pid'13694'  
  107.    
  108. 150319 02:51:16  innobackupex: Continuing after ibbackup hassuspended  
  109. 150319 02:51:16  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...  
  110. 150319 02:51:16  innobackupex: All tables locked and flushedto disk  
  111.    
  112. 150319 02:51:16  innobackupex: Starting to backup non-InnoDBtables and files  
  113. innobackupex: in subdirectories of '/home/data/mysql/data/'  
  114. innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)  
  115. >> log scanned up to (423929601)  
  116. innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)  
  117. >> log scanned up to (423929601)  
  118. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'  
  119. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'  
  120. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'  
  121. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'  
  122. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'  
  123. innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'  
  124. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'  
  125. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'  
  126. innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'  
  127. innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'  
  128. innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'  
  129. innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'  
  130. innobackupex: Backing up file '/home/data/mysql/data//test/incre2.frm'  
  131. innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'  
  132. innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'  
  133. innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'  
  134. innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'  
  135. innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'  
  136. innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'  
  137. innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'  
  138. innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'  
  139. innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'  
  140. 150319 02:51:17  innobackupex: Finished backing up non-InnoDBtables and files  
  141.    
  142. 150319 02:51:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...  
  143. 150319 02:51:17  innobackupex: Waiting for log copying tofinish  
  144.    
  145. xtrabackup: The latest check point (forincremental): '423929601'  
  146. xtrabackup: Stopping log copying thread.  
  147. .>> log scanned up to (423929601)  
  148.    
  149. xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_log_copied' with pid '13694'  
  150. xtrabackup: Transaction log of lsn(423929601) to (423929601) was copied.  
  151. 150319 02:51:18  innobackupex: All tables unlocked  
  152.    
  153. innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_2'  
  154. innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120  
  155. 150319 02:51:19  innobackupex: Connection to database serverclosed  
  156. 150319 02:51:19  innobackupex: completed OK!  


 

7，innobackupex做增量恢复

7.1先删除两次增量数据，用来查看验证恢复结果

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. mysql> delete from incre1;  
  2. Query OK, 1 row affected (0.05 sec)  
  3.    
  4. mysql> delete from incre2;  
  5. Query OK, 1 row affected (0.01 sec)  
  6.    
  7. mysql> select * from incre1;  
  8. Empty set (0.00 sec)  
  9.    
  10. mysql> select * from incre2;  
  11. Empty set (0.00 sec)  



7.2 开始做恢复，恢复全备份

备份命令如下：

innobackupex --user=backup--password="123456" --host=192.168.52.129  --apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log

备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. ......  
  2. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053  
  3.    
  4. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1  
  5. InnoDB: FTS optimize thread exiting.  
  6. InnoDB: Starting shutdown...  
  7. InnoDB: Shutdown completed; log sequencenumber 423921634  
  8. 150319 03:06:57  innobackupex: completed OK!  

7.3 基于全备份进行第一字增量备份的恢复

备份命令：

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129  /data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_1  --apply-log2>/tmp/innobackex_restore_incre_1.log

备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. ……  
  2. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1  
  3. InnoDB: FTS optimize thread exiting.  
  4. InnoDB: Starting shutdown...  
  5. InnoDB: Shutdown completed; log sequencenumber 423925452  
  6. innobackupex: Starting to copy non-InnoDBfiles in '/data/backups/mysql/innobackex_incre_1'  
  7. innobackupex: to the full backup directory'/data/backups/mysql/innobackupex_full_20150319'  
  8. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_info'  
  9. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_binlog_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_binlog_info'  
  10. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history.frm'  
  11. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_global_by_event_name.frm'  
  12. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/threads.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/threads.frm'  
  13. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history_long.frm'  
  14. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_instance.frm'  
  15. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_user_by_event_name.frm'  
  16. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_account_by_event_name.frm'  
  17. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_user_by_event_name.frm'  
  18. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_current.frm'  
  19. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_lock_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_lock_waits_summary_by_table.frm'  
  20. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_instances.frm'  
  21. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_actors.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_actors.frm'  
  22. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_table.frm'  
  23. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_account_by_event_name.frm'  
  24. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/cond_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/cond_instances.frm'  
  25. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/users.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/users.frm'  
  26. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/db.opt'  
  27. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_instance.frm'  
  28. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_thread_by_event_name.frm'  
  29. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_current.frm'  
  30. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/objects_summary_global_by_type.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/objects_summary_global_by_type.frm'  
  31. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_thread_by_event_name.frm'  
  32. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_host_by_event_name.frm'  
  33. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_event_name.frm'  
  34. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history.frm'  
  35. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history.frm'  
  36. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_thread_by_event_name.frm'  
  37. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_global_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_global_by_event_name.frm'  
  38. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_timers.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_timers.frm'  
  39. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/hosts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/hosts.frm'  
  40. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_host_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_host_by_event_name.frm'  
  41. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/host_cache.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/host_cache.frm'  
  42. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_objects.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_objects.frm'  
  43. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/setup_consumers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_consumers.frm'  
  44. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_account_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_account_connect_attrs.frm'  
  45. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_host_by_event_name.frm'  
  46. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_instance.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_instance.frm'  
  47. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_global_by_event_name.frm'  
  48. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_user_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_user_by_event_name.frm'  
  49. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/mutex_instances.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/mutex_instances.frm'  
  50. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_instruments.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_instruments.frm'  
  51. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_digest.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_digest.frm'  
  52. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/rwlock_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/rwlock_instances.frm'  
  53. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_event_name.frm'  
  54. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_account_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_account_by_event_name.frm'  
  55. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_current.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_current.frm'  
  56. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_connect_attrs.frm'  
  57. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/performance_timers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/performance_timers.frm'  
  58. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history_long.frm'  
  59. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_instances.frm'  
  60. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history_long.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history_long.frm'  
  61. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_index_usage.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_index_usage.frm'  
  62. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/accounts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/accounts.frm'  
  63. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/event.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYI'  
  64. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.frm'  
  65. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYD'  
  66. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYI' to '/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYI'  
  67. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.frm'  
  68. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.frm'  
  69. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYI'  
  70. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYI'  
  71. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYI'  
  72. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSV'  
  73. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYI'  
  74. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.frm' to '/data/backups/mysql/innobackupex_full_20150319/mysql/db.frm'  
  75. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYD'  
  76. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYI'  
  77. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYI'  
  78. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYI'  
  79. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.frm'  
  80. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYD'  
  81. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.frm'  
  82. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSM' to '/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSM'  
  83. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYD'  
  84. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.frm'  
  85. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/event.frm'  
  86. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYD'  
  87. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYD'  
  88. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYI'  
  89. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.frm'to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.frm'  
  90. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYI'  
  91. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYI'  
  92. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.frm'  
  93. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.frm'  
  94. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYD'  
  95. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYD'  
  96. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYI'  
  97. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.frm'  
  98. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.frm'  
  99. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYD'  
  100. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.frm'  
  101. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.frm'  
  102. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYD'  
  103. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.frm'  
  104. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYI'  
  105. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYD'  
  106. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYI'to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYI'  
  107. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYD'  
  108. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSM' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSM'  
  109. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYD'  
  110. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYI'  
  111. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.frm'  
  112. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYI'  
  113. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYI'  
  114. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYD'  
  115. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.frm'  
  116. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYD'  
  117. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.frm'  
  118. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYD'  
  119. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.frm'  
  120. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYI'  
  121. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYI'  
  122. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.frm'  
  123. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.frm'  
  124. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYI'  
  125. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.frm'  
  126. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.frm'  
  127. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.frm'  
  128. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD'  
  129. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm'  
  130. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD'  
  131. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD'  
  132. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm'  
  133. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV'  
  134. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD'  
  135. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD'  
  136. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm'  
  137. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm'  
  138. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm'  
  139. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.frm' to '/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm'  
  140. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm'  
  141. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm'  
  142. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt'  
  143. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm'  
  144. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm'  
  145. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm'  
  146. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm'  
  147. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm'  
  148. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm'  
  149. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm'  
  150. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t1.frm'  
  151. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm'  
  152. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm'  
  153. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm'  
  154. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm'  
  155. innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/user_db/db.opt'to '/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt'  
  156. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm'  
  157. innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm'  
  158. 150319 03:12:51  innobackupex: completed OK!  


 

7.4, 基于全备份和第一次增量备份，恢复第二次增量备份

备份命令如下：

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129  /data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_2  --apply-log2>/tmp/innobackex_restore_incre_2.log

备份过程信息如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. ……  
  2. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD'  
  3. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm'  
  4. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD'  
  5. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD'  
  6. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm'  
  7. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV'  
  8. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD'  
  9. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD'  
  10. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm'  
  11. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm'  
  12. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm'  
  13. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm'  
  14. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm'  
  15. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm'  
  16. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt'  
  17. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm'  
  18. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm'  
  19. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm'  
  20. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm'  
  21. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm'  
  22. innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm'  
  23. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre2.frm'  
  24. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm'  
  25. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t1.frm' to '/data/backups/mysql/innobackupex_full_20150319/test/t1.frm'  
  26. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm'  
  27. innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/user_db/t.frm'to '/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm'  
  28. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm'  
  29. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm'  
  30. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt'  
  31. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm'  
  32. innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm'  
  33. 150319 03:18:34  innobackupex: completed OK!  
  34. [root@data01 mysql]#  


 

7.5 恢复整个数据库

停止数据库

 service mysqld5612 stop

 清空数据目录下所有文件

 mkdir -p /tmp/mysqldatabak/

 mv/home/data/mysql/data/* /tmp/mysqldatabak/

 将恢复好的数据按照配置文件的需求拷贝到相应目录

 innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129--defaults-file=/etc/my.cnf --copy-back/data/backups/mysql/innobackupex_full_20150319/ 2>/tmp/innobackex_incre_restore_end.log

赋予mysql账号权限

chown -R mysql:mysql /home/data/mysql/data

启动mysql服务

service mysqld5612 start

PS：--copy-back过程如下：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
  1. innobackupex: Starting to copy InnoDB undotablespaces  
  2. innobackupex: in'/data/backups/mysql/innobackupex_full_20150319'  
  3. innobackupex: back to'/home/data/mysql/data'  
  4.    
  5. innobackupex: Starting to copy InnoDB logfiles  
  6. innobackupex: in'/data/backups/mysql/innobackupex_full_20150319'  
  7. innobackupex: back to original InnoDB logdirectory '/home/data/mysql/data'  
  8. innobackupex: Copying '/data/backups/mysql/innobackupex_full_20150319/ib_logfile1'to '/home/data/mysql/data/ib_logfile1'  
  9. innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile2' to'/home/data/mysql/data/ib_logfile2'  
  10. innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile0' to'/home/data/mysql/data/ib_logfile0'  
  11. innobackupex: Finished copying back files.  
  12.    
  13. 150319 03:23:49  innobackupex: completed OK!  



7.6 check已经被delete的数据

登录mysql界面，查看表incre1和表incre2，数据已经恢复，如下所示：

[ruby] view plain copy print?在CODE上查看代码片派生到我的代码片
    1. mysql> select * from incre1;  
    2. +---------------+----+  
    3. | incre1        | id |  
    4. +---------------+----+  
    5. | incre1 table  | a  |  
    6. +---------------+----+  
    7. 1 row in set (0.04 sec)  
    8.    
    9. mysql> select * from incre2;  
    10. +--------+----+  
    11. | a     | id |  
    12. +--------+----+  
    13. | incre2 | 3 |  
    14. +--------+----+  
    15. 1 row in set (0.07 sec)  
    16.    
    17. mysql> 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/moss_tan_jun/p/5751894.html