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
10. 查看数据库状态
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).
19. 应用日志
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
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!”.
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
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

恢复

[root@my01 2018-03-20_16-02-00]# innobackupex  –defaults-file=/etc/my.cnf –copy-back –rsync /data/db/xtbakup/2018-03-20_16-02-00/

[root@my01 ~]# cd /data/
[root@my01 data]# ls
db  kafka-logs  zookeeper
[root@my01 data]# cd db/
[root@my01 db]# ls
mysql  mysql_bak  xtbakup
[root@my01 db]# chown -R mysql.mysql mysql
[root@my01 db]# ll
total 0
drwxr-x—. 3 mysql mysql 18 Mar 20 11:29 mysql
drwxr-xr-x. 3 mysql mysql 18 Aug 18  2017 mysql_bak
drwxr-xr-x. 3 root  root  33 Mar 20 11:03 xtbakup
[root@my01 db]# service mysql start
Starting MySQL.Logging to ‘/data/db/mysql/1221/mariadb.log’.
 SUCCESS! 

[root@my01 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> 

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t              |
| test           |
+—————-+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
+———-+
| count(*) |
+———-+
|    81920 |
+———-+
1 row in set (0.11 sec)

六、创建表插入数据

点击(此处)折叠或打开

1. mysql> create table t1 (id int,name varchar(40));
   
2. Query OK, 0 rows affected (0.04 sec)
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)
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
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
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/
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
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)
20. 增量备份2
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)
4. mysql> show tables;
5. +––––––––––––––––+
6. | Tables_in_test |
7. +––––––––––––––––+
8. | t |
9. | test |
10. +––––––––––––––––+
11. 2 rows in set (0.00 sec)

十、恢复数据

点击(此处)折叠或打开

1. 将第一次的增量备份添加到全备份
   
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
5. 将第二次的增量备份添加到全备份（注意：不添加redo–only）
6. *****************************注意******************************
7. ***************************************************************
8. 做增量备份还原时，最后一次的增量备份添加到全备中时不添加redo-only参数
9. ***************************************************************
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
13. 把所有的备份和到一起进行一次apply–log
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
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!”.
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
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
96. 恢复数据
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)
15. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
17. Oracle is a registered trademark of Oracle Corporation and/or its
18. affiliates. Other names may be trademarks of their respective
19. owners.
21. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
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)
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
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)
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)