三、对数据库的增量备份与恢复
为了进行增量备份，先对数据库添加一些数据 
 
  
  1. mysql> insert into test values(11);  
  2. Query OK, 1 row affected (0.10 sec)  
  4. mysql> insert into test values(12);  
  5. Query OK, 1 row affected (0.05 sec)  
  7. mysql> insert into test values(13);  
  8. Query OK, 1 row affected (0.00 sec)  
  10. mysql> insert into test values(14);  
  11. Query OK, 1 row affected (0.00 sec)  
  13. mysql> insert into test values(15);  
  14. Query OK, 1 row affected (0.00 sec)  
  16. mysql> flush privileges;  
  17. Query OK, 0 rows affected (0.01 sec)  
  19. mysql> select * from test;  
  20. +------+  
  21. | id   |  
  22. +------+  
  23. |    1 |  
  24. |    2 |  
  25. |    3 |  
  26. |    4 |  
  27. |    5 |  
  28. |   11 |  
  29. |   12 |  
  30. |   13 |  
  31. |   14 |  
  32. |   15 |  
  33. +------+  
  34. 10 rows in set (0.00 sec)  
 
  

    然后进行增量的备份 
  
 
  

 
  
 
  
  1. root@client2:/var/lib/mysql# innobackupex --user=root--password=123456--database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data  
  3. InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy  
  4. and Percona Inc 2009-2012.  All Rights Reserved.  
  6. This software is published under  
  7. the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.  
  9. 130307 21:13:38  innobackupex: Starting mysql with options:  --password=xxxxxxxx--user='root' --unbuffered --  
  10. 130307 21:13:38  innobackupex: Connected to database with mysql child process (pid=12864)  
  11. 130307 21:13:44  innobackupex: Connection to database server closed  
  12. IMPORTANT: Please check that the backup run completes successfully.  
  13.            At the end of a successful backup run innobackupex  
  14.            prints "completed OK!".  
  16. innobackupex: Using mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2  
  17. innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.  
  19. innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44  
  20. 130307 21:13:44  innobackupex: Starting mysql with options:  --password=xxxxxxxx--user='root' --unbuffered --  
  21. 130307 21:13:44  innobackupex: Connected to database with mysql child process (pid=12891)  
  22. 130307 21:13:46  innobackupex: Connection to database server closed  
  24. 130307 21:13:46  innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/'
  25. innobackupex: Waiting for ibbackup (pid=12898) to suspend  
  26. innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended'  
  28. xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)  
  29. incremental backup from 59605543 is enabled.  
  30. xtrabackup: uses posix_fadvise().  
  31. xtrabackup: cd to /var/lib/mysql  
  32. xtrabackup: Target instance is assumed as followings.  
  33. xtrabackup:   innodb_data_home_dir = ./  
  34. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend  
  35. xtrabackup:   innodb_log_group_home_dir = ./  
  36. xtrabackup:   innodb_log_files_in_group = 2
  37. xtrabackup:   innodb_log_file_size = 5242880
  38. 130307 21:13:46 InnoDB: Using Linux native AIO  
  39. >> log scanned up to (59606124)  
  40. [01] Copying ./ibdata1   
  41.      to /tmp/data/2013-03-07_21-13-44/ibdata1.delta  
  42. [01]        ...done  
  44. 130307 21:13:50  innobackupex: Continuing after ibbackup has suspended  
  45. 130307 21:13:50  innobackupex: Starting mysql with options:  --password=xxxxxxxx--user='root' --unbuffered --  
  46. 130307 21:13:50  innobackupex: Connected to database with mysql child process (pid=12913)  
  47. >> log scanned up to (59606124)  
  48. 130307 21:13:52  innobackupex: Starting to lock all tables...  
  49. >> log scanned up to (59606124)  
  50. >> log scanned up to (59606124)  
  51. 130307 21:14:03  innobackupex: All tables locked and flushed to disk  
  53. 130307 21:14:03  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,  
  54. innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in  
  55. innobackupex: subdirectories of '/var/lib/mysql'  
  56. innobackupex: Backing up file '/var/lib/mysql/test/test.frm'  
  57. innobackupex: Backing up file '/var/lib/mysql/test/db.opt'  
  58. 130307 21:14:03  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files  
  60. innobackupex: Resuming ibbackup  
  62. xtrabackup: The latest check point (for incremental): '59606124'  
  63. >> log scanned up to (59606124)  
  64. xtrabackup: Stopping log copying thread.  
  65. xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied.  
  66. 130307 21:14:05  innobackupex: All tables unlocked  
  67. 130307 21:14:05  innobackupex: Connection to database server closed  
  69. innobackupex: Backup created in directory '/tmp/data/2013-03-07_21-13-44'  
  70. innobackupex: MySQL binlog position: filename 'mysql-bin.000023', position 107  
  71. 130307 21:14:05  innobackupex: completed OK!  
 
  

    其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。 
  
 
  

    在进行增量备份的恢复之前，先关闭数据库，然后删除数据库test 
  
 
  
 
  
  1. root@client2:/var/lib/mysql# service mysql stop  
  2. mysql stop/waiting  
  3. root@client2:/var/lib/mysql# rm -rf test  
  4. root@client2:/var/lib/mysql# ll  
  5. total 77856  
  6. drwx------  7 mysql mysql     4096 Mar  7 21:17 ./  
  7. drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../  
  8. -rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag  
  9. drwx------  2 mysql mysql     4096 Feb 11 17:39 django/  
  10. -rw-rw----  1 mysql mysql 69206016 Mar  7 21:17 ibdata1  
  11. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:17 ib_logfile0  
  12. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:11 ib_logfile1  
  13. drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/  
  14. drwx------  2 mysql root      4096 Jan  5 14:22 mysql/  
  15. -rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info  
  16. drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/  
  17. drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/  
 
  

    增量备份的恢复 
  
 
  
 
  
  1. root@client2:/var/lib/mysql# innobackupex -user=root--password=123456--defaults-file=/etc/mysql/my.cnf --apply-log /tmp/restore/ --incremental-dir=/tmp/data/2013-03-07_21-13-44/  
  3. InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy  
  4. and Percona Inc 2009-2012.  All Rights Reserved.  
  6. This software is published under  
  7. the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.  
  9. IMPORTANT: Please check that the apply-log run completes successfully.  
  10.            At the end of a successful apply-log run innobackupex  
  11.            prints "completed OK!".  
  15. 130307 21:18:20  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/tmp/restore --incremental-dir=/tmp/data/2013-03-07_21-13-44/  
  17. xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)  
  18. incremental backup from 59605543 is enabled.  
  19. xtrabackup: cd to /tmp/restore  
  20. xtrabackup: This target seems to be already prepared.  
  21. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124)  
  22. xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes  
  23. Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta ...  
  24. xtrabackup: Temporary instance for recovery is set as followings.  
  25. xtrabackup:   innodb_data_home_dir = ./  
  26. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend  
  27. xtrabackup:   innodb_log_group_home_dir = /tmp/data/2013-03-07_21-13-44/  
  28. xtrabackup:   innodb_log_files_in_group = 1
  29. xtrabackup:   innodb_log_file_size = 2097152
  30. 130307 21:18:20 InnoDB: Using Linux native AIO  
  31. xtrabackup: Starting InnoDB instance for recovery.  
  32. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)  
  33. 130307 21:18:20 InnoDB: The InnoDB memory heap is disabled  
  34. 130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins  
  35. 130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3  
  36. 130307 21:18:20 InnoDB: Using Linux native AIO  
  37. 130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead  
  38. 130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M  
  39. 130307 21:18:20 InnoDB: Completed initialization of buffer pool  
  40. 130307 21:18:20 InnoDB: highest supported file format is Barracuda.  
  41. InnoDB: ##########################################################  
  42. InnoDB:                          WARNING!  
  43. InnoDB: The log sequence number in ibdata files is higher  
  44. InnoDB: than the log sequence number in the ib_logfiles! Are you sure  
  45. InnoDB: you are using the right ib_logfiles to start up the database?  
  46. InnoDB: Log sequence number in ib_logfiles is 59606124, log  
  47. InnoDB: sequence numbers stamped to ibdata file headers are between  
  48. InnoDB: 59607052 and 59607052.  
  49. InnoDB: ##########################################################  
  50. InnoDB: The log sequence number in ibdata files does not match  
  51. InnoDB: the log sequence number in the ib_logfiles!  
  52. 130307 21:18:20  InnoDB: Database was not shut down normally!  
  53. InnoDB: Starting crash recovery.  
  54. InnoDB: Reading tablespace information from the .ibd files...  
  55. InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023  
  56. 130307 21:18:29  InnoDB: Waiting for the background threads to start  
  57. 130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124  
  59. [notice (again)]  
  60.   If you use binary log and don't use any hack of group commit,  
  61.   the binary log position seems to be:  
  62. InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023  
  64. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  65. 130307 21:18:30  InnoDB: Starting shutdown...  
  66. 130307 21:18:34  InnoDB: Shutdown completed; log sequence number 59607339  
  67. innobackupex: Starting to copy non-InnoDB files in '/tmp/data/2013-03-07_21-13-44/'  
  68. innobackupex: to the full backup directory '/tmp/restore'  
  69. innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info' to '/tmp/restore/xtrabackup_binlog_info'  
  70. innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/test.frm' to '/tmp/restore/test/test.frm'  
  71. innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/db.opt' to '/tmp/restore/test/db.opt'  
  72. 130307 21:18:34  innobackupex: completed OK!  
 
  

    然后再进入恢复的目录/tmp/data 
  
 
  
 
  
  1. root@client2:/var/lib/mysql# cd /tmp/data  
  2. root@client2:/tmp/data# ll  
  3. total 3276  
  4. drwxr-xr-x  3 root root    4096 Mar  7 21:13 ./  
  5. drwxrwxrwt 14 root root    4096 Mar  7 21:18 ../  
  6. drwxr-xr-x  3 root root    4096 Mar  7 21:18 2013-03-07_21-13-44/  
  7. -rw-r--r--  1 root root    3780 Mar  7 21:02 test-201303072101.log  
  8. -rw-r--r--  1 root root 3336909 Mar  7 21:02 test-201303072101.tar.gz  
  9. root@client2:/tmp/data# cd 2013-03-07_21-13-44/  
  10. root@client2:/tmp/data/2013-03-07_21-13-44# ll  
  11. total 2288  
  12. drwxr-xr-x 3 root root    4096 Mar  7 21:18 ./  
  13. drwxr-xr-x 3 root root    4096 Mar  7 21:13 ../  
  14. -rw-r--r-- 1 root root     260 Mar  7 21:13 backup-my.cnf  
  15. -rw-r--r-- 1 root root  212992 Mar  7 21:13 ibdata1.delta  
  16. -rw-r--r-- 1 root root      18 Mar  7 21:13 ibdata1.meta  
  17. drwxr-xr-x 2 root root    4096 Mar  7 21:14 test/  
  18. -rw-r--r-- 1 root root      13 Mar  7 21:14 xtrabackup_binary  
  19. -rw-r--r-- 1 root root      23 Mar  7 21:14 xtrabackup_binlog_info  
  20. -rw-r--r-- 1 root root      84 Mar  7 21:14 xtrabackup_checkpoints  
  21. -rw-r--r-- 1 root root 2097152 Mar  7 21:18 xtrabackup_logfile  
 
  

    跟全部备份一样，把test恢复到/var/lib/mysql里 
  
 
  
 
  
  1. root@client2:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/  
  2. sending incremental file list  
  3. test/  
  4. test/db.opt  
  5. test/test.frm  
  7. sent 381 bytes  received 54 bytes  870.00 bytes/sec  
  8. total size is 8621  speedup is 19.82  
  9. root@client2:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/  
  10. root@client2:/var/lib/mysql# ll  
  11. total 77860  
  12. drwx------  8 mysql mysql     4096 Mar  7 21:19 ./  
  13. drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../  
  14. -rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag  
  15. drwx------  2 mysql mysql     4096 Feb 11 17:39 django/  
  16. -rw-rw----  1 mysql mysql 69206016 Mar  7 21:17 ibdata1  
  17. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:17 ib_logfile0  
  18. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:11 ib_logfile1  
  19. drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/  
  20. drwx------  2 mysql root      4096 Jan  5 14:22 mysql/  
  21. -rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info  
  22. drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/  
  23. drwxr-xr-x  2 root  root      4096 Mar  7 21:14 test/  
  24. drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/  
 
  

    然后修改用户与组 
  
 
  
 
  
  1. root@client2:/var/lib/mysql# chown -R mysql:mysql test/  
  2. root@client2:/var/lib/mysql# ll  
  3. total 77860  
  4. drwx------  8 mysql mysql     4096 Mar  7 21:19 ./  
  5. drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../  
  6. -rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag  
  7. drwx------  2 mysql mysql     4096 Feb 11 17:39 django/  
  8. -rw-rw----  1 mysql mysql 69206016 Mar  7 21:17 ibdata1  
  9. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:17 ib_logfile0  
  10. -rw-rw----  1 mysql mysql  5242880 Mar  7 21:11 ib_logfile1  
  11. drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/  
  12. drwx------  2 mysql root      4096 Jan  5 14:22 mysql/  
  13. -rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info  
  14. drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/  
  15. drwxr-xr-x  2 mysql mysql     4096 Mar  7 21:14 test/  
  16. drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/  
 
  

    然后启动msyql，并查看test数据库里test表的内容 
  
 
  
 
  
  1. root@client2:/var/lib/mysql# service mysql start  
  2. mysql start/running, process 13109  
  3. root@client2:/var/lib/mysql# mysql -u root -p  
  4. Enter password:   
  5. Welcome to the MySQL monitor.  Commands end with ; or \g.  
  6. Your MySQL connection id is 36  
  7. Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)  
  9. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.  
  11. Oracle is a registered trademark of Oracle Corporation and/or its  
  12. affiliates. Other names may be trademarks of their respective  
  13. owners.  
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
  17. mysql> use test  
  18. Reading table information for completion of table and column names  
  19. You can turn off this feature to get a quicker startup with -A  
  21. Database changed  
  22. mysql> select * from test;  
  23. +------+  
  24. | id   |  
  25. +------+  
  26. |    1 |  
  27. |    2 |  
  28. |    3 |  
  29. |    4 |  
  30. |    5 |  
  31. |   11 |  
  32. |   12 |  
  33. |   13 |  
  34. |   14 |  
  35. |   15 |  
  36. +------+  
  37. 10 rows in set (0.00 sec)  
 
  

    可以看到增量备份已经恢复完成。