MySQL回滚到某一时刻数据的方法

时间：2023-03-09 20:11:30
MySQL回滚到某一时刻数据的方法

MySQL回滚到某一时刻数据的方法

对于有归档日志的数据库来说，原理上都具备全库回滚到之前某一时刻的能力。在这方面最好用的Orale数据库，使用Oracle数据库的RMAN工具，可以方便的设置全备，增备保留的时间和自动清理，RMAN自己记录之前做过哪些备份操作，有一份备份列表，所以可以全自动的根据全备、增备、归档日志进行回滚，只需一条命令。这个可以参考我之前写的博客

Oracle Database 12c RMAN全量+增量备份+归档日志恢复详解

Oracle 12c数据库定时备份和清理脚本

mysql不同于oracle和db2这种企业级数据库，它没有oracle里面的redo日志，也没有db2里面的循环日志。mysql有类似于oracle和db2归档日志的binlog，而这个binlog可以看作是循环日志和归档日志的结合。有一定的大小限制。未完成的事务和已完成的事务都会记录在binlog中，当一个binlog写满之后，就会开启一个新的binlog。binlog还有三种方式，row，statement，mixed。其中row记录量最大，但是对于各种工具支持最好，所以对于安全要求比较高的数据库，推荐使用row格式。

同时，binlog也是mysql主从复制的依据，所以使用binlog来恢复数据库是比较可靠的。不足的就是mysql并没有内置binlog的清理工具，对于长时间的binlog我们需要去手动清理或者编写脚本清理。mysql也没有提供oracle，db2那样的增量备份方法。所以保证binlog不要丢失就比较重要。虽然手动的操作多了一些，但是这也代表着mysql的恢复更偏向于无状态的，即异地跨平台恢复会比较方便，不需要像oracle那样必须找到控制文件。

数据库恢复的过程于oracle，db2区别不大。基本都是通过先恢复全备份，再逐个恢复增量备份，再根据归档日志逐条重做事务，一直重做到你需要恢复到的日期为止。mysql由于没有增量备份，所以先恢复全备，再手动找到binlog中全备时间的那一行，从那一行往后开始执行重做事务，直到你需要的停止的那一行。

下面来介绍一下如何将mysql数据库回滚到某一时刻。大概有如下步骤

1、找一个现有的mysql数据库，先不打开binlog，插入几条数据

2、打开binlog，重启数据库，再插入几条数据

3、使用mysqldump全备一次数据库

4、再插入几条数据，模拟全备之后执行成功的事务，记录执行完毕的时间。

5、模拟数据库崩溃或者误删操作，然后将全备文件和binlog都拷贝到另一台服务器上进行异地恢复。

这里使用的版本是mysql 5.7.18

首先我们先建库建表，但是此时没有开启binlog，这里我主要像说明，如果没有binlog，那么就没有归档日志，我们就不知道以前做过了哪些事务，只能使用全备进行恢复，而全备之后发生的操作就都会丢失。my.cnf配置如下

  1. #server-id = 1
  2. #log_bin = /var/log/mysql/mysql-bin.log
  3. expire_logs_days = 10
  4. max_binlog_size = 100M

可以看见log_bin前面被注释掉了，也就是没有开启。下面开始建库建表

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mydb |
  7. | mysql |
  8. | performance_schema |
  9. | sys |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)
  13. mysql> create database dbtest;
  14. Query OK, 1 row affected (0.00 sec)
  16. mysql> use dbtest;
  17. Database changed
  19. mysql> create table table1 ( id int primary key, name varchar(40), birthday datetime);
  20. Query OK, 0 rows affected (0.44 sec)
  22. mysql> insert into table1 values (1,'befor_binlog1',NOW());
  23. Query OK, 1 row affected (0.05 sec)
  25. mysql> insert into table1 values (2,'befor_binlog2',NOW());
  26. Query OK, 1 row affected (0.07 sec)
  28. mysql> select * from table1;
  29. +----+---------------+---------------------+
  30. | id | name | birthday |
  31. +----+---------------+---------------------+
  32. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  33. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  34. +----+---------------+---------------------+
  35. 2 rows in set (0.00 sec)

此时我们是看不到当前binlog是哪个文件第几行的

  1. mysql> show master status \G
  2. Empty set (0.00 sec)

可以发现，再不开启binlog的情况下，是可以正常插入数据的，但我还是推荐从开始，也就是建库建表之前就开启binlog，那样即使没有全备，也可以从头开始恢复。但是现在这种情况下，如果出现了误删操作，我们是无法拯救我们的数据的。

然后我们执行第二步，开启binlog然后重启数据库。首先修改配置文件my.cnf

  1. server-id = 1
  2. log_bin = /var/log/mysql/mysql-bin.log
  3. expire_logs_days = 10
  4. max_binlog_size = 100M

然后我们再来插入两条数据

  1. mysql> use dbtest;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> insert into table1 values (3,'after_binlog1',NOW());
  7. Query OK, 1 row affected (0.12 sec)
  9. mysql> insert into table1 values (4,'after_binlog2',NOW());
  10. Query OK, 1 row affected (0.20 sec)
  12. mysql> select * from table1;
  13. +----+---------------+---------------------+
  14. | id | name | birthday |
  15. +----+---------------+---------------------+
  16. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  17. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  18. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  19. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  20. +----+---------------+---------------------+
  21. 4 rows in set (0.00 sec)

如果此时发生了误删，那我们在进行异地恢复的时候，只能恢复出id为3和4的两条数据，因为1，2在插入的时候没有开启binlog，binlog中没有这两条事务的记录，所以就恢复不了。

下面我们进行第三部，使用mysqldump进行一次全备

  1. root@f4d417a2e6ea:/# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p > /backup/full.sql
  2. Enter password:

查看一下备份出来的文件所在时刻归档日志的为止

  1. --
  2. -- Position to start replication or point-in-time recovery from
  3. --
  5. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=740;

此时查看一下数据库里归档日志的位置

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000001
  4. Position: 740
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

可以看到，在备份前和备份后，归档日志并没有发生变化，还是停留在同一行里。这个行数和文件名被记录在了全备文件中，以后会用到。

现在如果出现了误删或者存储损坏我们是可以高枕无忧的，因为有了全备，我们可以轻松的异地恢复回来

我们进行第四步，插入两条新数据

  1. mysql> insert into table1 values (5,'after_backup',NOW());
  2. Query OK, 1 row affected (0.13 sec)
  4. mysql> insert into table1 values (6,'after_backup2',NOW());
  5. Query OK, 1 row affected (0.11 sec)
  7. mysql> select * from table1;
  8. +----+---------------+---------------------+
  9. | id | name | birthday |
  10. +----+---------------+---------------------+
  11. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  12. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  13. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  14. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  15. | 5 | after_backup | 2018-06-09 09:11:18 |
  16. | 6 | after_backup2 | 2018-06-09 09:11:25 |
  17. +----+---------------+---------------------+
  18. 6 rows in set (0.00 sec)

然后我们进行第六步，假装系统崩溃存储损坏，我们来尝试恢复到当前数据。在这里我们分两步来做一个是系统的崩溃，我们需要异地恢复到最新为止，另一个是进行了误删操作，我们将6条数据全部恢复回来。

1、系统崩溃情况下，我们先将全备文件和binlog都拷贝到要恢复的新服务器上，这里要注意binlog可能不止一个，但是本例由于数据量少只有一个binlog。binlog的目录就写在my.cnf我们刚改过的文件里

按照正常的步骤，我们应该先恢复全备，然后根据binlog重做已经提交的事务，在恢复之前，可以看到是没有我们原来的库的

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mydb |
  7. | mysql |
  8. | performance_schema |
  9. | sys |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)
  13. mysql>

全备份恢复，恢复其实就是让mysql执行一大段sql，而这段sql就是我们用mysqldump导出来的那个。此时可以不开启新数据库的binlog。开了反而还会变慢

# mysql -uroot -p < /import/full.sql

然后查看一下导入了哪些数据

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | dbtest |
  7. | mydb |
  8. | mysql |
  9. | performance_schema |
  10. | sys |
  11. +--------------------+
  12. 6 rows in set (0.00 sec)
  14. mysql> use dbtest;
  15. Reading table information for completion of table and column names
  16. You can turn off this feature to get a quicker startup with -A
  18. Database changed
  19. mysql> select * from table1;
  20. +----+---------------+---------------------+
  21. | id | name | birthday |
  22. +----+---------------+---------------------+
  23. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  24. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  25. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  26. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  27. +----+---------------+---------------------+
  28. 4 rows in set (0.00 sec)

不出所料，由于全备时间点的关系，只有id为1-4的被恢复回来了，5，6由于是全备之后插入的，所以当前没有，我们需要从binlog中恢复。这里我们使用mysql自带的mysqlbinlog工具，将binlog解析成sql，然后用mysql执行这段sql，就把后面的事务给执行了，

  1. root@5d41cbeb4b4d:/import/mysql# ls
  2. error.log mysql-bin.000001 mysql-bin.index
  3. root@5d41cbeb4b4d:/import/mysql# mysqlbinlog --no-defaults /import/mysql/mysql-bin.000001 | mysql -uroot -p
  4. Enter password:
  5. ERROR 1062 (23000) at line 38: Duplicate entry '3' for key 'PRIMARY'
  1. mysql> select * from table1
  2. -> ;
  3. +----+---------------+---------------------+
  4. | id | name | birthday |
  5. +----+---------------+---------------------+
  6. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  7. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  8. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  9. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  10. +----+---------------+---------------------+
  11. 4 rows in set (0.00 sec)

我们会发现报了一个错，同时数据也没有导入成功。这是什么原因呢。

由于我们在id=2之后，开启了binlog，所以此时binlog中的内容就是insert id为3，4，5，6的语句，所以当我们把整个binlog文件全部重做一遍的话，在insert id=3的时候就会报主键冲突的错误，这点是显而易见的。那么如何避免重复执行已备份的事务呢，这就要我们手动指定binlog的重做时间点。前面我们已经知道，从全备文件full.sql中可以看到备份时间点的binlog文件和行数，也就是mysql-bin.000001的第740行，所以我们就从这一行开始恢复

  1. # mysqlbinlog --no-defaults --start-position=740 /import/mysql/mysql-bin.000001 | mysql -
  2. uroot -p
  3. Enter password:
  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  8. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  9. | 5 | after_backup | 2018-06-09 09:11:18 |
  10. | 6 | after_backup2 | 2018-06-09 09:11:25 |
  11. +----+---------------+---------------------+
  12. 6 rows in set (0.00 sec)

现在我们发现已经成功的恢复了全部的6条数据

2、上面是针对系统崩溃这种情况，进行的全量恢复操作，那么如果是因为操作事务删除了某些数据或者插入了某些脏数据怎么办呢。

首先我们去源库中删除一些数据，然后插入一些错误数据

  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  8. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  9. | 5 | after_backup | 2018-06-09 09:11:18 |
  10. | 6 | after_backup2 | 2018-06-09 09:11:25 |
  11. +----+---------------+---------------------+
  12. 6 rows in set (0.00 sec)
  14. mysql> delete from table1 where id >3;
  15. Query OK, 3 rows affected (0.14 sec)
  17. mysql> select * from table1;
  18. +----+---------------+---------------------+
  19. | id | name | birthday |
  20. +----+---------------+---------------------+
  21. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  22. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  23. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  24. +----+---------------+---------------------+
  25. 3 rows in set (0.00 sec)
  27. mysql> insert into table1 values (4,'xxxxxxx1',NOW());
  28. Query OK, 1 row affected (0.12 sec)
  30. mysql> insert into table1 values (5,'xxxxxxx1',NOW());
  31. Query OK, 1 row affected (0.14 sec)
  33. mysql> select * from table1;
  34. +----+---------------+---------------------+
  35. | id | name | birthday |
  36. +----+---------------+---------------------+
  37. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  38. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  39. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  40. | 4 | xxxxxxx1 | 2018-06-09 09:43:00 |
  41. | 5 | xxxxxxx1 | 2018-06-09 09:43:05 |
  42. +----+---------------+---------------------+
  43. 5 rows in set (0.00 sec)

如上所示，先删掉了一半的数据，然后又插入了两条数据，占用了原来的4，5.id=6的数据没了。也就是两条数据被篡改，1条丢失的情况。

此时再看一下binlog写到什么位置了。

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000001
  4. Position: 2233
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

如果再按照上面的方法对binlog进行恢复，那么我们的误删操作也会被恢复，就失去了意义，所以此时我们必须指定恢复的时间点。

从上面可以看出，进行误删操作是9：11：25之后，所以我们只需恢复到这个时间点就可以了。

首先还是根据前面的全备进行恢复

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mydb |
  7. | mysql |
  8. | performance_schema |
  9. | sys |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)
  1. # mysql -uroot -p < /import/full.sql
  2. Enter password:
  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  8. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  9. +----+---------------+---------------------+
  10. 4 rows in set (0.00 sec)

然后关键点就是既要指定清楚binlog起始位置，又要指明结束时间，这里时间使用UTC时间，要和binlog里面对应注意，id=6的数据是9:11:25才插入成功的，所以我们恢复最早只能是9:11:26，如果恢复到9:11:25是看不到id=6的数据的

# mysqlbinlog --no-defaults --start-position=740 --stop-datetime="2018-06-09 01:11:26" /import/mysql/mysql-bin.000001 | mysql -uroot -p

然后发现我们的恢复成功了

  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  8. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  9. | 5 | after_backup | 2018-06-09 09:11:18 |
  10. | 6 | after_backup2 | 2018-06-09 09:11:25 |
  11. +----+---------------+---------------------+
  12. 6 rows in set (0.00 sec)

如果不指明--stop-datetime 那么就会恢复到binlog的末尾，我们的误操作也就一起恢复回去了。

题外话：

刚才看到--stop-datetime要用UTC时间，是因为我们查看binlog得来的，下面我们就来看看如何查看binlog文件

比如我们可以看到以下binlog的记录

  1. mysqlbinlog --no-defaults /import/mysql/mysql-bin.000001
  2. ......
  1. # at 1179
  2. #180609 1:11:25 server id 1 end_log_pos 1235 CRC32 0x6b867c5b Table_map: `dbtest`.`table1` mapped to number 254
  3. # at 1235
  4. #180609 1:11:25 server id 1 end_log_pos 1294 CRC32 0xc058c50f Write_rows: table id 254 flags: STMT_END_F
  6. BINLOG '
  7. PSkbWxMBAAAAOAAAANMEAAAAAP4AAAAAAAEABmRidGVzdAAGdGFibGUxAAMDDxIDeAAABlt8hms=
  8. PSkbWx4BAAAAOwAAAA4FAAAAAP4AAAAAAAEAAgAD//gGAAAADWFmdGVyX2JhY2t1cDKZoBIS2Q/F
  9. WMA=
  10. '/*!*/;
  11. # at 1294
  12. #180609 1:11:25 server id 1 end_log_pos 1325 CRC32 0x8113be5c Xid = 992
  13. COMMIT/*!*/;
  14. # at 1325
  15. #180609 1:42:32 server id 1 end_log_pos 1390 CRC32 0x6c1bff71 Anonymous_GTID last_committed=4 sequence_number=5
  16. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  17. # at 1390
  18. #180609 1:42:32 server id 1 end_log_pos 1464 CRC32 0xfb668ab1 Query thread_id=11 exec_time=0 error_code=0
  19. SET TIMESTAMP=1528508552/*!*/;
  20. BEGIN
  21. /*!*/;
  22. # at 1464
  23. #180609 1:42:32 server id 1 end_log_pos 1520 CRC32 0x89d622f5 Table_map: `dbtest`.`table1` mapped to number 254
  24. # at 1520
  25. #180609 1:42:32 server id 1 end_log_pos 1626 CRC32 0x4c8f4330 Delete_rows: table id 254 flags: STMT_END_F
  27. BINLOG '
  28. iDAbWxMBAAAAOAAAAPAFAAAAAP4AAAAAAAEABmRidGVzdAAGdGFibGUxAAMDDxIDeAAABvUi1ok=
  29. iDAbWyABAAAAagAAAFoGAAAAAP4AAAAAAAEAAgAD//gEAAAADWFmdGVyX2JpbmxvZzKZoBILJvgF
  30. AAAADGFmdGVyX2JhY2t1cJmgEhLS+AYAAAANYWZ0ZXJfYmFja3VwMpmgEhLZMEOPTA==
  31. '/*!*/;

上面展示了我们最后一次正常的插入操作和第一次误删操作的日志。可以看到误删操作是1325开始，1520结束，1：42：32秒一秒钟之内结束。而最后一次正常插入是1235行结束，1:11:25时候。

然后我们再找全备起始时间也就是740行所对应的时间

  1. # at 709
  2. #180609 0:44:38 server id 1 end_log_pos 740 CRC32 0x1119b274 Xid = 15
  3. COMMIT/*!*/;
  4. # at 740
  5. #180609 1:11:18 server id 1 end_log_pos 805 CRC32 0x7732118b Anonymous_GTID last_committed=2 sequence_number=3
  6. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  7. # at 805
  8. #180609 1:11:18 server id 1 end_log_pos 887 CRC32 0x22d66d83 Query thread_id=10 exec_time=0 error_code=0
  9. SET TIMESTAMP=1528506678/*!*/;
  10. BEGIN
  11. /*!*/;

可以看到740行所对应的是1:11:18

但是奇怪的事情是，如果我们按照上面的，--start-position和s--stop-datetime相结合，那么binlog并不是从740行1294行，而是从第四行开始的，如下

  1. # mysqlbinlog --no-defaults --start-position=740 --stop-datetime="2018-06-09 01:11:26" /import/mysql/mysql-bin.000001
  2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #180609 0:43:43 server id 1 end_log_pos 123 CRC32 0x73c53756 Start: binlog v 4, server v 5.7.18-0ubuntu0.16.04.1-log created 180609 0:43:43 at startup
  7. # Warning: this binlog is either in use or was not closed properly.
  8. ROLLBACK/*!*/;
  9. BINLOG '
  10. vyIbWw8BAAAAdwAAAHsAAAABAAQANS43LjE4LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
  11. AAAAAAAAAAAAAAAAAAC/IhtbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  12. AVY3xXM=
  13. '/*!*/;
  14. # at 740
  15. #180609 1:11:18 server id 1 end_log_pos 805 CRC32 0x7732118b Anonymous_GTID last_committed=2 sequence_number=3
  16. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  17. # at 805
  18. #180609 1:11:18 server id 1 end_log_pos 887 CRC32 0x22d66d83 Query thread_id=10 exec_time=0 error_code=0
  19. SET TIMESTAMP=1528506678/*!*/;
  20. SET @@session.pseudo_thread_id=10/*!*/;
  21. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  22. SET @@session.sql_mode=1436549152/*!*/;
  23. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  24. /*!\C utf8 *//*!*/;
  25. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  26. SET @@session.time_zone='SYSTEM'/*!*/;
  27. SET @@session.lc_time_names=0/*!*/;
  28. SET @@session.collation_database=DEFAULT/*!*/;
  29. BEGIN
  30. /*!*/;
  31. # at 887
  32. #180609 1:11:18 server id 1 end_log_pos 943 CRC32 0x5d815b86 Table_map: `dbtest`.`table1` mapped to number 254
  33. # at 943
  34. #180609 1:11:18 server id 1 end_log_pos 1001 CRC32 0x5f1763c3 Write_rows: table id 254 flags: STMT_END_F
  36. BINLOG '
  37. NikbWxMBAAAAOAAAAK8DAAAAAP4AAAAAAAEABmRidGVzdAAGdGFibGUxAAMDDxIDeAAABoZbgV0=
  38. NikbWx4BAAAAOgAAAOkDAAAAAP4AAAAAAAEAAgAD//gFAAAADGFmdGVyX2JhY2t1cJmgEhLSw2MX
  39. Xw==
  40. '/*!*/;
  41. # at 1001
  42. #180609 1:11:18 server id 1 end_log_pos 1032 CRC32 0x38b9cbf4 Xid = 991
  43. COMMIT/*!*/;
  44. # at 1032
  45. #180609 1:11:25 server id 1 end_log_pos 1097 CRC32 0xbf1c5f5e Anonymous_GTID last_committed=3 sequence_number=4
  46. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  47. # at 1097
  48. #180609 1:11:25 server id 1 end_log_pos 1179 CRC32 0xeebaef40 Query thread_id=10 exec_time=0 error_code=0
  49. SET TIMESTAMP=1528506685/*!*/;
  50. BEGIN
  51. /*!*/;
  52. # at 1179
  53. #180609 1:11:25 server id 1 end_log_pos 1235 CRC32 0x6b867c5b Table_map: `dbtest`.`table1` mapped to number 254
  54. # at 1235
  55. #180609 1:11:25 server id 1 end_log_pos 1294 CRC32 0xc058c50f Write_rows: table id 254 flags: STMT_END_F
  57. BINLOG '
  58. PSkbWxMBAAAAOAAAANMEAAAAAP4AAAAAAAEABmRidGVzdAAGdGFibGUxAAMDDxIDeAAABlt8hms=
  59. PSkbWx4BAAAAOwAAAA4FAAAAAP4AAAAAAAEAAgAD//gGAAAADWFmdGVyX2JhY2t1cDKZoBIS2Q/F
  60. WMA=
  61. '/*!*/;
  62. # at 1294
  63. #180609 1:11:25 server id 1 end_log_pos 1325 CRC32 0x8113be5c Xid = 992
  64. COMMIT/*!*/;
  65. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  66. DELIMITER ;
  67. # End of log file
  68. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  69. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面清楚的记载了我们id=5和id=6插入的情况，是不是很有意思。

刚才我们看到的是只有1个binlog的情况，而我们知道binlog是会不断增加的，当有两个以上的binlog的时候该怎么办。

沃恩首先到源数据库上，手动建立新的binlog

  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  8. | 4 | xxxxxxx1 | 2018-06-09 09:43:00 |
  9. | 5 | xxxxxxx1 | 2018-06-09 09:43:05 |
  10. +----+---------------+---------------------+
  11. 5 rows in set (0.00 sec)
  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000001
  4. Position: 2233
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

现在手动创建新的binlog文件

  1. mysql> flush logs;
  2. Query OK, 0 rows affected (0.30 sec)
  4. mysql> show master status \G
  5. *************************** 1. row ***************************
  6. File: mysql-bin.000002
  7. Position: 154
  8. Binlog_Do_DB:
  9. Binlog_Ignore_DB:
  10. Executed_Gtid_Set:
  11. 1 row in set (0.00 sec)

这样一个新的00002的binlog就开始使用了，起始行数154，并且原来的00001并没有被删掉，而是永久的保存在那里。

我们在创建新binlog后插入几条数据进去

  1. mysql> insert into table1 values (6,'new_binlog1',NOW());
  2. Query OK, 1 row affected (0.13 sec)
  4. mysql> insert into table1 values (7,'new_binlog2',NOW());
  5. Query OK, 1 row affected (0.15 sec)
  7. mysql> select * from table1;
  8. +----+---------------+---------------------+
  9. | id | name | birthday |
  10. +----+---------------+---------------------+
  11. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  12. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  13. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  14. | 4 | xxxxxxx1 | 2018-06-09 09:43:00 |
  15. | 5 | xxxxxxx1 | 2018-06-09 09:43:05 |
  16. | 6 | new_binlog1 | 2018-06-09 10:47:21 |
  17. | 7 | new_binlog2 | 2018-06-09 10:47:28 |
  18. +----+---------------+---------------------+
  19. 7 rows in set (0.00 sec)
  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000002
  4. Position: 736
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

现在假设系统崩溃，我们要异地恢复这个数据库，我们会有之前的一个全备，和两个binlog

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mydb |
  7. | mysql |
  8. | performance_schema |
  9. | sys |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)

还是首先恢复全库备份，显而易见，恢复之后只有id=1-4的数据，并且id=4的数据还是旧版本的

  1. mysql> use dbtest;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> select * from table1;
  7. +----+---------------+---------------------+
  8. | id | name | birthday |
  9. +----+---------------+---------------------+
  10. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  11. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  12. | 3 | after_binlog1 | 2018-06-09 08:44:33 |
  13. | 4 | after_binlog2 | 2018-06-09 08:44:38 |
  14. +----+---------------+---------------------+
  15. 4 rows in set (0.00 sec)

然后我们需要按照binlog的顺序依次恢复，也就是先重做00001上的，再重做00002上的，由于全备是从00001的740行开始的，所以再重做00001的时候要采用--start-position，而重做00002的时候就不需要了。

# mysqlbinlog --no-defaults --start-position=740 /import/mysql/mysql-bin.000001 | mysql -uroot -p

此时会恢复到我们flush log之前的状态

  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 00:44:33 |
  8. | 4 | xxxxxxx1 | 2018-06-09 01:43:00 |
  9. | 5 | xxxxxxx1 | 2018-06-09 01:43:05 |
  10. +----+---------------+---------------------+
  11. 5 rows in set (0.00 sec)

然后再重做00002上的事务，此时不需要指定--start-postion了，因为00001和00002是连续的。

  1. # ls
  2. error.log mysql-bin.000001 mysql-bin.000002 mysql-bin.index
  3. # mysqlbinlog --no-defaults /import/mysql/mysql-bin.000002 | mysql -uroot -p

发现我们已经把全部数据都恢复回来了

  1. mysql> select * from table1;
  2. +----+---------------+---------------------+
  3. | id | name | birthday |
  4. +----+---------------+---------------------+
  5. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  6. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  7. | 3 | after_binlog1 | 2018-06-09 00:44:33 |
  8. | 4 | xxxxxxx1 | 2018-06-09 01:43:00 |
  9. | 5 | xxxxxxx1 | 2018-06-09 01:43:05 |
  10. | 6 | new_binlog1 | 2018-06-09 10:47:21 |
  11. | 7 | new_binlog2 | 2018-06-09 10:47:28 |
  12. +----+---------------+---------------------+
  13. 7 rows in set (0.00 sec)

同样，你也可以在恢复00001的时候指定--stop-datetime，但是这样在重做00002的时候可能会碰到主键冲突的问题，需要你自己去把握了。

上面都是DML操作，通过binlog可以准确无误的还原回来，下面再执行一个DDL操作，看看binlog是否还有用

首先我们建立一张新表table2，然后drop掉table1，之后看看能否异地恢复。然后我们再考虑drop table1是一个误操作的情况下，能否异地恢复。

  1. mysql> create table table2 (
  2. -> id int primary key,
  3. -> hobby varchar(40),
  4. -> starttime datetime)
  5. -> ;
  6. Query OK, 0 rows affected (0.55 sec)
  8. mysql> insert into table2 values(1,'play',NOW());
  9. Query OK, 1 row affected (0.12 sec)
  11. mysql> select * from table2;
  12. +----+-------+---------------------+
  13. | id | hobby | starttime |
  14. +----+-------+---------------------+
  15. | 1 | play | 2018-06-09 11:06:07 |
  16. +----+-------+---------------------+
  17. 1 row in set (0.00 sec)

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000002
  4. Position: 1243
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

可以看到00002已经到了1243行了，下面我们drop table1

  1. mysql> drop table table1;
  2. Query OK, 0 rows affected (0.42 sec)
  4. mysql> show master status \G
  5. *************************** 1. row ***************************
  6. File: mysql-bin.000002
  7. Position: 1431
  8. Binlog_Do_DB:
  9. Binlog_Ignore_DB:
  10. Executed_Gtid_Set:
  11. 1 row in set (0.00 sec)

可以看到，drop操作也被写入到了binlog。下面我们开始异地恢复

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mydb |
  7. | mysql |
  8. | performance_schema |
  9. | sys |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)

我们先进行全备份的恢复，然后恢复00001，然后恢复00002，其中先恢复到create table2并插入数据完成的阶段，再恢复到drop table1之后的阶段，全面检查DML的可恢复性

  1. # mysql -uroot -p < /import/full.sql
  2. Enter password:
  3. # mysql -uroot -p
  4. Enter password:
  5. mysql> use dbtest;
  6. Reading table information for completion of table and column names
  7. You can turn off this feature to get a quicker startup with -A
  9. Database changed
  10. mysql> select * from table1;
  11. +----+---------------+---------------------+
  12. | id | name | birthday |
  13. +----+---------------+---------------------+
  14. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  15. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  16. | 3 | after_binlog1 | 2018-06-09 00:44:33 |
  17. | 4 | after_binlog2 | 2018-06-09 00:44:38 |
  18. +----+---------------+---------------------+
  19. 4 rows in set (0.00 sec)
  21. mysql> select * from table2;
  22. ERROR 1146 (42S02): Table 'dbtest.table2' doesn't exist

然后恢复00001的全部内容和00002中drop table1之前的内容，由于表2的第一条数据是11:06:07插入成功的，所以我们要恢复到11:06:08才能看到着一条数据，恢复到11:06:07是看不到的。

  1. # mysqlbinlog --no-defaults --start-position=740 /import/mysql/mysql-bin.000001 | mysql -uroot -p
  2. Enter password:
  4. # mysqlbinlog --no-defaults --stop-datetime='2018-06-09 03:06:08' /import/mysql/mysql-bin.000002 | mysql -uroot -p
  5. Enter password:
  6. root@5d41cbeb4b4d:/import/mysql# mysql -uroot -p
  7. Enter password:
  9. mysql> use dbtest;
  10. Reading table information for completion of table and column names
  11. You can turn off this feature to get a quicker startup with -A
  13. Database changed
  14. mysql> select * from table1;
  15. +----+---------------+---------------------+
  16. | id | name | birthday |
  17. +----+---------------+---------------------+
  18. | 1 | befor_binlog1 | 2018-06-09 08:36:33 |
  19. | 2 | befor_binlog2 | 2018-06-09 08:36:40 |
  20. | 3 | after_binlog1 | 2018-06-09 00:44:33 |
  21. | 4 | xxxxxxx1 | 2018-06-09 01:43:00 |
  22. | 5 | xxxxxxx1 | 2018-06-09 01:43:05 |
  23. | 6 | new_binlog1 | 2018-06-09 10:47:21 |
  24. | 7 | new_binlog2 | 2018-06-09 10:47:28 |
  25. +----+---------------+---------------------+
  26. 7 rows in set (0.00 sec)
  28. mysql> select * from table2;
  29. +----+-------+---------------------+
  30. | id | hobby | starttime |
  31. +----+-------+---------------------+
  32. | 1 | play | 2018-06-09 11:06:07 |
  33. +----+-------+---------------------+
  34. 1 row in set (0.00 sec)

此时table1和table2的数据都已经异地恢复成功，可见即使源数据库执行了删表操作也是不妨碍我们恢复的。下面我们再恢复到和源数据库一模一样的状态，就是把上一句的--stop-datetime换成--start-datetime。

# mysqlbinlog --no-defaults --start-datetime='2018-06-09 03:06:08' /import/mysql/mysql-bin.000002 | mysql -uroot -p
  1. mysql> select * from table1;
  2. ERROR 1146 (42S02): Table 'dbtest.table1' doesn't exist
  3. mysql> select * from table2;
  4. +----+-------+---------------------+
  5. | id | hobby | starttime |
  6. +----+-------+---------------------+
  7. | 1 | play | 2018-06-09 11:06:07 |
  8. +----+-------+---------------------+
  9. 1 row in set (0.00 sec)

至此，DDL语句也恢复完成了。

如何简化日常备份

还有一种特殊的备份方式，就是再mysqldump语句中加入--flush-logs这样的话会把当前没有写满的binlog停止，另起一个新的binlog来写，这样就不用在重做binlog的时候添加--start-position语句了，如下

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000002
  4. Position: 1431
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)
  10. mysql> quit
  11. Bye
  12. root@f4d417a2e6ea:/# mysqldump --single-transaction --master-data=2 --triggers --routines --flush-logs --flush-privileges --databases dbtest -p > /backup/full2.sql
  13. Enter password:

在备份的时候还加入了--flush-privileges,这个是在恢复的时候能够自动赋予相关用户相关权限，如果不加这个更适合主从复制迁移数据 --databases dbtest 是只备份 dbtest这个库，减少备份提及，但是mysql里面的user就会不一致了，这样即使加了--flush-privileges，用户权限还是会丢失，所以这样适合单机恢复，就是在出问题后将当前mysql的库drop掉，然后再执行导入。

在备份完毕之后，产生了一个新的binlog 00003，并从154行开始

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3. File: mysql-bin.000003
  4. Position: 154
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.01 sec)

此时我们查看我们备份出来的这个文件，看他的起始文件和位置

  1. # head full2.sql -n 50
  2. ......
  3. --
  4. -- Position to start replication or point-in-time recovery from
  5. --
  7. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

能看到和备份完之后的master状态是一样的。

然后我们在源库里添加几行数据

  1. mysql> use dbtest;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> insert into table2 values(2,'hahaha',NOW());
  7. Query OK, 1 row affected (0.14 sec)
  9. mysql> insert into table2 values(3,'enenen',NOW());
  10. Query OK, 1 row affected (0.14 sec)
  12. mysql> select * from table2;
  13. +----+--------+---------------------+
  14. | id | hobby | starttime |
  15. +----+--------+---------------------+
  16. | 1 | play | 2018-06-09 11:06:07 |
  17. | 2 | hahaha | 2018-06-09 04:09:17 |
  18. | 3 | enenen | 2018-06-09 04:09:34 |
  19. +----+--------+---------------------+
  20. 3 rows in set (0.00 sec)
  22. mysql> show master status \G
  23. *************************** 1. row ***************************
  24. File: mysql-bin.000003
  25. Position: 726
  26. Binlog_Do_DB:
  27. Binlog_Ignore_DB:
  28. Executed_Gtid_Set:
  29. 1 row in set (0.00 sec)

可以看到00003到了726行了。现在我们假设源库崩溃，然后我们把full2.sql和binlog 00003都拷贝到另一台服务器上。

开始恢复全备

# mysql -uroot -p < /import/full2.sql
  1. mysql> select * from table2;
  2. +----+-------+---------------------+
  3. | id | hobby | starttime |
  4. +----+-------+---------------------+
  5. | 1 | play | 2018-06-09 11:06:07 |
  6. +----+-------+---------------------+
  7. 1 row in set (0.00 sec)

不出所料只有一行数据，现在我们要重做00003，由于之前--flush-logs的作用，我们虽然从157行开始，但是无需指定--start-positon了，简化了数据库恢复的过程，而且由于重新启用一个binlog，之前的0001，00002就都可以删掉或者转移走保存起来，节省服务器上的宝贵空间，不然的话我们也许还要等00002写满之后才能转移走。

  1. # mysqlbinlog --no-defaults /import/mysql/mysql-bin.000003 | mysql -uroot -p
  2. Enter password:
  1. mysql> select * from table2;
  2. +----+--------+---------------------+
  3. | id | hobby | starttime |
  4. +----+--------+---------------------+
  5. | 1 | play | 2018-06-09 11:06:07 |
  6. | 2 | hahaha | 2018-06-09 04:09:17 |
  7. | 3 | enenen | 2018-06-09 04:09:34 |
  8. +----+--------+---------------------+
  9. 3 rows in set (0.00 sec)

通过这样的参数优化，简化了日常备份的工作量

版权声明：遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。

相关文章