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

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

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配置如下

#server-id = 1

#log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

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

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> create database dbtest;

Query OK, 1 row affected (0.00 sec)

mysql> use dbtest;

Database changed

mysql> create table table1 ( id int primary key, name varchar(40), birthday datetime);

Query OK, 0 rows affected (0.44 sec)

mysql> insert into table1 values (1,'befor_binlog1',NOW());

Query OK, 1 row affected (0.05 sec)

mysql> insert into table1 values (2,'befor_binlog2',NOW());

Query OK, 1 row affected (0.07 sec)

mysql> select * from table1;

+----+---------------+---------------------+

| id | name | birthday |

+----+---------------+---------------------+

| 1 | befor_binlog1 | 2018-06-09 08:36:33 |

| 2 | befor_binlog2 | 2018-06-09 08:36:40 |

+----+---------------+---------------------+

2 rows in set (0.00 sec)

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

mysql> show master status \G

Empty set (0.00 sec)

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

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

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

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

mysql> use dbtest;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into table1 values (3,'after_binlog1',NOW());

Query OK, 1 row affected (0.12 sec)

mysql> insert into table1 values (4,'after_binlog2',NOW());

Query OK, 1 row affected (0.20 sec)

mysql> select * from table1;

+----+---------------+---------------------+

| id | name | birthday |

+----+---------------+---------------------+

| 1 | befor_binlog1 | 2018-06-09 08:36:33 |

| 2 | befor_binlog2 | 2018-06-09 08:36:40 |

| 3 | after_binlog1 | 2018-06-09 08:44:33 |

| 4 | after_binlog2 | 2018-06-09 08:44:38 |

+----+---------------+---------------------+

4 rows in set (0.00 sec)

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

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

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

Enter password:

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

--

-- Position to start replication or point-in-time recovery from

--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=740;

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

mysql> show master status \G

*************************** 1. row ***************************

File: mysql-bin.000001

Position: 740

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

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

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

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

mysql> insert into table1 values (5,'after_backup',NOW());

Query OK, 1 row affected (0.13 sec)

mysql> insert into table1 values (6,'after_backup2',NOW());

Query OK, 1 row affected (0.11 sec)

mysql> select * from table1;

+----+---------------+---------------------+

| id | name | birthday |

+----+---------------+---------------------+

| 1 | befor_binlog1 | 2018-06-09 08:36:33 |

| 2 | befor_binlog2 | 2018-06-09 08:36:40 |

| 3 | after_binlog1 | 2018-06-09 08:44:33 |

| 4 | after_binlog2 | 2018-06-09 08:44:38 |

| 5 | after_backup | 2018-06-09 09:11:18 |

| 6 | after_backup2 | 2018-06-09 09:11:25 |

+----+---------------+---------------------+

6 rows in set (0.00 sec)

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

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

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

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql>

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

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

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

mysql> show databases;

+--------------------+

| Database |

+---------------