MySQL数据库增量恢复实践

1.备份数据库

[root@localhost ~]# mysql -e "show databases like 'test';"
+-----------------+
| Database (test) |
+-----------------+
| test |
+-----------------+
[root@localhost ~]# mysqldump -B --master-data=2 --single-transaction test|gzip >/root/test/test_$(date +%F).sql.gz && ll /root/test
total 4
-rw-r--r-- 1 root root 886 Jan 11 16:51 test_2020-01-11.sql.gz

2.模拟备份后持续写入数据

[root@localhost ~]# mysql -e "use test;insert into tab_test values(4,'four',4),(5,'five',5);"
[root@localhost ~]# mysql -e "select * from test.tab_test;"
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | one | 1 |
| 2 | two | 2 |
| 3 | three | 3 |
| 4 | four | 4 |
| 5 | five | 5 |
+----+-------+-----+

3.模拟删除数据库

[root@localhost ~]# mysql -e "drop database test;show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------+

4.移动binlog增量文件

[root@localhost ~]# cp -a /usr/data/mysql/mysql-bin.* /root/test/ && ll /root/test/
total 1196
-rw-r----- 1 mysql mysql 1200676 Jan 8 20:50 mysql-bin.000010
-rw-r----- 1 mysql mysql 154 Jan 11 15:43 mysql-bin.000011
-rw-r----- 1 mysql mysql 4440 Jan 11 17:05 mysql-bin.000012
-rw-r----- 1 mysql mysql 57 Jan 11 15:43 mysql-bin.index
-rw-r--r-- 1 root root 886 Jan 11 16:51 test_2020-01-11.sql.gz

5.查看binlog数据增量位置

[root@localhost test]# grep "MASTER" test_2020-01-11.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=3982;   # 从mysql-bin.000012文件的3982位置开始恢复增量数据

6.将binlog转化为可执行的sql语句

[root@localhost test]# mysqlbinlog -d test mysql-bin.000012 --start-position=3982 -r bin.sql
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
[root@localhost test]# mysqlbinlog --no-defaults -d test mysql-bin.000012 --start-position=3982 -r bin.sql
[root@localhost test]# ll
total 1204
-rw-r----- 1 root root 2261 Jan 11 17:33 bin.sql
-rw-r----- 1 mysql mysql 1200676 Jan 8 20:50 mysql-bin.000010
-rw-r----- 1 mysql mysql 154 Jan 11 15:43 mysql-bin.000011
-rw-r----- 1 mysql mysql 4440 Jan 11 17:05 mysql-bin.000012
-rw-r----- 1 mysql mysql 57 Jan 11 15:43 mysql-bin.index
-rw-r--r-- 1 root root 2292 Jan 11 17:10 test_2020-01-11.sql
-rw-r--r-- 1 root root 886 Jan 11 16:51 test_2020-01-11.sql.gz
[root@localhost test]# grep -w drop bin.sql
drop database test
[root@localhost test]# sed -i '/drop database test/d' bin.sql     #删除drop database test，否则恢复数据库后又会将数据库删除
[root@localhost test]# grep -w drop bin.sql
[root@localhost test]#

7.恢复数据库

[root@localhost test]# mysql < test_2020-01-11.sql
[root@localhost test]# mysql -e "select * from test.tab_test;"
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | one | 1 |
| 2 | two | 2 |
| 3 | three | 3 |
+----+-------+-----+
[root@localhost test]# mysql < bin.sql
ERROR 1790 (HY000) at line 45: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. 
Ownership is released on COMMIT or ROLLBACK.    #bin.sql的45行命令执行错误，有朋友说直接删除（保留此意见），但实际数据已经导入了。
[root@localhost test]# mysql -e "select * from test.tab_test;"
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | one   |   1 |
|  2 | two   |   2 |
|  3 | three |   3 |
|  4 | four  |   4 |
|  5 | five  |   5 |
+----+-------+-----+
[root@localhost test]# sed -n '45p' bin.sql
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;