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，那么就没有归
mysql删除重复的数据保留一条档⽇志，我们就不知道以前做过了哪些事务，只能使⽤全备进⾏恢复，⽽全备之后发⽣的操作就都会丢失。myf配置如下
#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然后重启数据库。⾸先修改配置⽂件myf
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的⽬录就写在myf我们刚改过的⽂件⾥
按照正常的步骤，我们应该先恢复全备，然后根据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          |
+--------------------+
| information_schema |
| dbtest            |
| mydb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
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> 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为1-4的被恢复回来了，5，6由于是全备之后插⼊的，所以当前没有，我们需要从binlog中恢复。这⾥我们使⽤mysql⾃带的mysqlbinlog⼯具，将binlog解析成sql，然后⽤mysql执⾏这段sql，就把后⾯的事务给执⾏了，
root@5d41cbeb4b4d:/import/mysql# ls
error.log  mysql-bin.000001  mysql-bin.index
root@5d41cbeb4b4d:/import/mysql# mysqlbinlog --no-defaults /import/mysql/mysql-bin.000001 | mysql -uroot -p
Enter password:
ERROR 1062 (23000) at line 38: Duplicate entry '3' for key 'PRIMARY'
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=2之后，开启了binlog，所以此时binlog中的内容就是insert id为3，4，5，6的语句，所以当我们把整个binlog⽂件全部重做⼀遍的话，在insert id=3的时候就会报主键冲突的错误，这点是显⽽易见的。那么如何避免重复执⾏已备份的事务呢，这就要我们⼿动指定binlog的重做时间点。前⾯我们已经知道，从全备⽂件full.sql中可以看到备份时间点的binlog⽂件和⾏数，也就是mysql-
bin.000001的第740⾏，所以我们就从这⼀⾏开始恢复

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。