MySQL增量备份之xtrbackup

点击(此处)折叠或打开

1. 平台：Centos 7
   
2. 数据库版本：MySQL 5.7
   
3. xtrabackup 版本：xtrabackup version 2.4.8

点击(此处)折叠或打开

1. [root@my01 xtrabackup]# tar zxvf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
   
2. [root@my01 xtrabackup]# mv percona-xtrabackup-2.4.8-Linux-x86_64 /usr/local/xtrabackup
3. 添加环境变量
4. export PATH=$PATH:/usr/local/xtrabackup/bin
   

点击(此处)折叠或打开

1. mysql> create user xtbakup@'localhost' identified by 'oracle';
   
2. mysql> grant reload,process,lock tables,replication client on *.* to xtbakup@localhost;

点击(此处)折叠或打开

1. [root@my01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock /data/db/xtbakup

点击(此处)折叠或打开

1. [root@my01 ~]# service mysql stop
   
2. Shutting down MySQL..
3. [root@my01 db]# ls
   
4. mysql xtbakup
   
5. [root@my01 db]# mv mysql/ mysql_bak/
   
6. [root@my01 db]# ls
   
7. mysql_bak xtbakup
   
8. 
   
9. 
   
10. 查看数据库状态
    
11. 
    
12. [root@my01 ~]# service mysql status
    
13. MySQL is not running
    
14. [root@my01 ~]# service mysql start
    
15. Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
    
16. 2018-03-20T03:26:56.919210Z mysqld_safe Directory '/data/db/mysql/1221' for UNIX socket file don't exists.
    
17. ERROR! The server quit without updating PID file (/data/db/mysql/1221/my01.pid).
    
18. 
    
19. 应用日志
    
20. 
    
21. [root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
    
22. 180320 16:01:55 innobackupex: Starting the apply-log operation
    
23. 
    
24. IMPORTANT: Please check that the apply-log run completes successfully.
    
25. At the end of a successful apply-log run innobackupex
    
26. prints "completed OK!".
    
27. 
    
28. innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
    
29. xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
    
30. xtrabackup: This target seems to be not prepared yet.
    
31. InnoDB: Number of pools: 1
    
32. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9239084)
    
33. xtrabackup: using the following InnoDB configuration for recovery:
    
34. xtrabackup: innodb_data_home_dir = .
    
35. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
36. xtrabackup: innodb_log_group_home_dir = .
    
37. xtrabackup: innodb_log_files_in_group = 1
    
38. xtrabackup: innodb_log_file_size = 8388608
    
39. xtrabackup: using the following InnoDB configuration for recovery:
    
40. xtrabackup: innodb_data_home_dir = .
    
41. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
42. xtrabackup: innodb_log_group_home_dir = .
    
43. xtrabackup: innodb_log_files_in_group = 1
    
44. xtrabackup: innodb_log_file_size = 8388608
    
45. xtrabackup: Starting InnoDB instance for recovery.
    
46. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    
47. InnoDB: PUNCH HOLE support available
    
48. InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
49. InnoDB: Uses event mutexes
    
50. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    
51. InnoDB: Compressed tables use zlib 1.2.3
    
52. InnoDB: Number of pools: 1
    
53. InnoDB: Using CPU crc32 instructions
    
54. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    
55. InnoDB: Completed initialization of buffer pool
    
56. InnoDB: page_cleaner coordinator priority: -20
    
57. InnoDB: Highest supported file format is Barracuda.
    
58. InnoDB: Log scan progressed past the checkpoint lsn 9239084
    
59. InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
    
60. InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
    
61. InnoDB: Database was not shutdown normally!
    
62. InnoDB: Starting crash recovery.
    
63. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
    
64. InnoDB: Creating shared tablespace for temporary tables
    
65. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    
66. InnoDB: File './ibtmp1' size is now 12 MB.
    
67. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    
68. InnoDB: 32 non-redo rollback segment(s) are active.
    
69. InnoDB: 5.7.13 started; log sequence number 9239093
    
70. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
    
71. 
    
72. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    
73. InnoDB: FTS optimize thread exiting.
    
74. InnoDB: Starting shutdown...
    
75. InnoDB: Shutdown completed; log sequence number 9239112
    
76. InnoDB: Number of pools: 1
    
77. xtrabackup: using the following InnoDB configuration for recovery:
    
78. xtrabackup: innodb_data_home_dir = .
    
79. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
80. xtrabackup: innodb_log_group_home_dir = .
    
81. xtrabackup: innodb_log_files_in_group = 2
    
82. xtrabackup: innodb_log_file_size = 50331648
    
83. InnoDB: PUNCH HOLE support available
    
84. InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
85. InnoDB: Uses event mutexes
    
86. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    
87. InnoDB: Compressed tables use zlib 1.2.3
    
88. InnoDB: Number of pools: 1
    
89. InnoDB: Using CPU crc32 instructions
    
90. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    
91. InnoDB: Completed initialization of buffer pool
    
92. InnoDB: page_cleaner coordinator priority: -20
    
93. InnoDB: Setting log file ./ib_logfile101 size to 48 MB
    
94. InnoDB: Setting log file ./ib_logfile1 size to 48 MB
    
95. InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    
96. InnoDB: New log files created, LSN=9239112
    
97. InnoDB: Highest supported file format is Barracuda.
    
98. InnoDB: Log scan progressed past the checkpoint lsn 9239564
    
99. InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
    
100. InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
     
101. InnoDB: Database was not shutdown normally!
     
102. InnoDB: Starting crash recovery.
     
103. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
     
104. InnoDB: Removed temporary tablespace data file: "ibtmp1"
     
105. InnoDB: Creating shared tablespace for temporary tables
     
106. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
     
107. InnoDB: File './ibtmp1

点击(此处)折叠或打开

1. mysql> create table t1 (id int,name varchar(40));
   
2. Query OK, 0 rows affected (0.04 sec)
   
3. 
   
4. mysql> show tables;
   
5. +----------------+
   
6. | Tables_in_test |
   
7. +----------------+
   
8. | t |
   
9. | t1 |
   
10. | test |
    
11. +----------------+
    
12. 3 rows in set (0.00 sec)
    
13. 
    
14. mysql> insert into t1 values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
    
15. Query OK, 4 rows affected (0.01 sec)
    
16. Records: 4 Duplicates: 0 Warnings: 0
    
17. 
    
18. mysql> select * from t1;
    
19. +------+------+
    
20. | id | name |
    
21. +------+------+
    
22. | 1 | aaa |
    
23. | 2 | bbb |
    
24. | 3 | ccc |
    
25. | 4 | ddd |
    
26. +------+------+
    
27. 4 rows in set (0.00 sec)

点击(此处)折叠或打开

1. [root@my01 db]# ls
   
2. incdata mysql mysql_bak xtbakup
   
3. [root@my01 incdata]# pwd
   
4. /data/db/incdata
   
5. 
   
6. [root@my01 incdata]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/xtbakup/2018-03-20_16-02-00/
   
7. 
   
8. [root@my01 incdata]# ls
   
9. 2018-03-20_16-04-16
   
10. [root@my01 incdata]# cd 2018-03-20_16-04-16/
    
11. [root@my01 2018-03-20_16-04-16]# ls
    
12. backup-my.cnf ibdata1.delta mysql scott test xtrabackup_checkpoints xtrabackup_logfile
    
13. ib_buffer_pool ibdata1.meta performance_schema sys xtrabackup_binlog_info xtrabackup_info
    
14. [root@my01 2018-03-20_16-04-16]# more xtrabackup_checkpoints
    
15. backup_type = incremental
    
16. from_lsn = 9251193
    
17. to_lsn = 9257319
    
18. last_lsn = 9257328
    
19. compact = 0
    
20. recover_binlog_info = 0

点击(此处)折叠或打开

1. mysql> insert into t1 values (101,'aaa'),(102,'bbb'),(103,'ccc'),(104,'ddd');
   
2. Query OK, 4 rows affected (0.02 sec)
   
3. Records: 4 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> select * from t1;
   
6. +------+------+
   
7. | id | name |
   
8. +------+------+
   
9. | 1 | aaa |
   
10. | 2 | bbb |
    
11. | 3 | ccc |
    
12. | 4 | ddd |
    
13. | 101 | aaa |
    
14. | 102 | bbb |
    
15. | 103 | ccc |
    
16. | 104 | ddd |
    
17. +------+------+
    
18. 8 rows in set (0.00 sec)
    
19. 
    
20. 增量备份2
    
21. 
    
22. [root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/incdata/2018-03-20_16-04-16

点击(此处)折叠或打开

1. mysql> drop table t1;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. mysql> show tables;
   
5. +----------------+
   
6. | Tables_in_test |
   
7. +----------------+
   
8. | t |
   
9. | test |
   
10. +----------------+
    
11. 2 rows in set (0.00 sec)

点击(此处)折叠或打开

1. 将第一次的增量备份添加到全备份
   
2. 
   
3. [root@my01 db]# innobackupex --apply-log --redo-only /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-04-16
4. 
   
5. 将第二次的增量备份添加到全备份（注意：不添加redo-only）
   
6. *****************************注意******************************
7. ***************************************************************
8. 做增量备份还原时，最后一次的增量备份添加到全备中时不添加redo-only参数
9. ***************************************************************
10. 
    
11. [root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-09-04
    
12. 
    
13. 把所有的备份和到一起进行一次apply-log
    
14. 
    
15. [root@my01 xtbakup]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
    
16. 180320 16:17:37 innobackupex: Starting the apply-log operation
    
17. 
    
18. IMPORTANT: Please check that the apply-log run completes successfully.
    
19. At the end of a successful apply-log run innobackupex
    
20. prints "completed OK!".
    
21. 
    
22. innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
    
23. xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
    
24. xtrabackup: This target seems to be already prepared.
    
25. InnoDB: Number of pools: 1
    
26. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    
27. xtrabackup: using the following InnoDB configuration for recovery:
    
28. xtrabackup: innodb_data_home_dir = .
    
29. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
30. xtrabackup: innodb_log_group_home_dir = .
    
31. xtrabackup: innodb_log_files_in_group = 2
    
32. xtrabackup: innodb_log_file_size = 50331648
    
33. xtrabackup: using the following InnoDB configuration for recovery:
    
34. xtrabackup: innodb_data_home_dir = .
    
35. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
36. xtrabackup: innodb_log_group_home_dir = .
    
37. xtrabackup: innodb_log_files_in_group = 2
    
38. xtrabackup: innodb_log_file_size = 50331648
    
39. xtrabackup: Starting InnoDB instance for recovery.
    
40. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    
41. InnoDB: PUNCH HOLE support available
    
42. InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
43. InnoDB: Uses event mutexes
    
44. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    
45. InnoDB: Compressed tables use zlib 1.2.3
    
46. InnoDB: Number of pools: 1
    
47. InnoDB: Using CPU crc32 instructions
    
48. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    
49. InnoDB: Completed initialization of buffer pool
    
50. InnoDB: page_cleaner coordinator priority: -20
    
51. InnoDB: Highest supported file format is Barracuda.
    
52. InnoDB: Removed temporary tablespace data file: "ibtmp1"
    
53. InnoDB: Creating shared tablespace for temporary tables
    
54. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    
55. InnoDB: File './ibtmp1' size is now 12 MB.
    
56. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    
57. InnoDB: 32 non-redo rollback segment(s) are active.
    
58. InnoDB: 5.7.13 started; log sequence number 9262120
    
59. InnoDB: xtrabackup: Last MySQL binlog file position 1842, file name mysql-bin.000001
    
60. 
    
61. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    
62. InnoDB: FTS optimize thread exiting.
    
63. InnoDB: Starting shutdown...
    
64. InnoDB: Shutdown completed; log sequence number 9262139
    
65. InnoDB: Number of pools: 1
    
66. xtrabackup: using the following InnoDB configuration for recovery:
    
67. xtrabackup: innodb_data_home_dir = .
    
68. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    
69. xtrabackup: innodb_log_group_home_dir = .
    
70. xtrabackup: innodb_log_files_in_group = 2
    
71. xtrabackup: innodb_log_file_size = 50331648
    
72. InnoDB: PUNCH HOLE support available
    
73. InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
74. InnoDB: Uses event mutexes
    
75. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    
76. InnoDB: Compressed tables use zlib 1.2.3
    
77. InnoDB: Number of pools: 1
    
78. InnoDB: Using CPU crc32 instructions
    
79. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    
80. InnoDB: Completed initialization of buffer pool
    
81. InnoDB: page_cleaner coordinator priority: -20
    
82. InnoDB: Highest supported file format is Barracuda.
    
83. InnoDB: Removed temporary tablespace data file: "ibtmp1"
    
84. InnoDB: Creating shared tablespace for temporary tables
    
85. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    
86. InnoDB: File './ibtmp1' size is now 12 MB.
    
87. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    
88. InnoDB: 32 non-redo rollback segment(s) are active.
    
89. InnoDB: 5.7.13 started; log sequence number 9262139
    
90. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    
91. InnoDB: FTS optimize thread exiting.
    
92. InnoDB: Starting shutdown...
    
93. InnoDB: Shutdown completed; log sequence number 9262158
    
94. 180320 16:17:40 completed
    
95. 
    
96. 恢复数据
    
97. 
    
98. [root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/db/xtbakup/2018-03-20_16-02-00/

点击(此处)折叠或打开

1. [root@my01 ~]# service mysql status
   
2. ERROR! MySQL is not running
   
3. [root@my01 ~]# service mysql start
   
4. Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
   
5. SUCCESS!
   
6. [root@my01 ~]#
   
7. [root@my01 ~]#
   
8. [root@my01 ~]#
   
9. [root@my01 ~]# mysql -u root -p
   
10. Enter password:
    
11. Welcome to the MySQL monitor. Commands end with ; or \g.
    
12. Your MySQL connection id is 3
    
13. Server version: 5.7.19-log MySQL Community Server (GPL)
    
14. 
    
15. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    
16. 
    
17. Oracle is a registered trademark of Oracle Corporation and/or its
    
18. affiliates. Other names may be trademarks of their respective
    
19. owners.
    
20. 
    
21. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
22. 
    
23. mysql>
    
24. mysql>
    
25. mysql>
    
26. mysql> show databases;
    
27. +--------------------+
    
28. | Database |
    
29. +--------------------+
    
30. | information_schema |
    
31. | mysql |
    
32. | performance_schema |
    
33. | scott |
    
34. | sys |
    
35. | test |
    
36. +--------------------+
    
37. 6 rows in set (0.01 sec)
    
38. 
    
39. mysql> use test;
    
40. Reading table information for completion of table and column names
    
41. You can turn off this feature to get a quicker startup with -A
    
42. 
    
43. Database changed
    
44. mysql> show tables;
    
45. +----------------+
    
46. | Tables_in_test |
    
47. +----------------+
    
48. | t |
    
49. | t1 |
    
50. | test |
    
51. +----------------+
    
52. 3 rows in set (0.00 sec)
    
53. 
    
54. mysql> select * from t1;
    
55. +------+------+
    
56. | id | name |
    
57. +------+------+
    
58. | 1 | aaa |
    
59. | 2 | bbb |
    
60. | 3 | ccc |
    
61. | 4 | ddd |
    
62. | 101 | aaa |
    
63. | 102 | bbb |
    
64. | 103 | ccc |
    
65. | 104 | ddd |
    
66. +------+------+
    
67. 8 rows in set (0.00 sec)