快速回滚mysql数据

使用binlog快速回滚mysql数据

在日常使用mysql时，特殊是在开发环境，经常会有一些憨憨操作，手动去操作数据库，或者又删表导入等操作，除了运维备份mysql，也可以记录binlog的的一些信息，比如在手动操作前记录一下时间点和position，操作结束之后也分别记录一下，这样对于数据会更安全，在开发环境时也可以一波骚操作把数据库搞坏又快速回滚。

模拟操作

在各种蜜汁操作前，特殊是没有把握的操作，例如各种批量操作，结构更新，和手动执行一些sql时，如果操作失误比如没有加where等。会直接污染数据库

在操作前强烈建议备份或记录binlog，备份往往是全量的大文本数据，无论是导出还是导入都会导致速度比较慢很麻烦，而记录binlog的时间点或position就比较方便了，如果操作没有出现重大问题就节约上面导出的时间和大部分导入的时间

我们这里模拟一个操作，有一个需求会变更数据库中的某些字段的某些值信息，所以需要写一个补偿脚本来对历史数据进行更新，这些操作往往数量比较大（历史全量），如果是涉及隐私等信息更是重要。

所以上述操作往往不会仅执行一次，会在开发或测试环境进行多次的操作，这就涉及到数据每次都需要恢复为起始状态，然后再执行脚本，最后验证数据的完整性和准确性

开启binlog

[mysqld]
skip-grant-tables
log-bin=mysql-bin
server-id=1
binlog_format=ROW

docker的话5.7是挂载到这个地方

 {
    "Type": "bind",
    "Source": "/Users/xxxxx/mysql/conf/mysql",
    "Destination": "/etc/mysql/conf.d",
    "Mode": "",
    "RW": true,
    "Propagation": "rprivate"
 }

ok 我们在执行脚本之前，需要记录当前binlog的位置,进入mysql终端，获取目前最新的binlog日志file和position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 16275505 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

此时就可以去执行脚本数据了，如果出现问题就可以直接指定恢复到16275505的位置上

mysqlbinlog --skip-gtids --stop-position=16275505 mysql-bin.000013 | mysql -u root -p

如果我们要是把表全部删除，然后再导入结构+数据，最后再执行脚本上面的操作就不行了
按顺序我们可以先删除表，然后去看一下此时的时间点和position

#我们可以通过这个命令查看某个binlog的全部信息
show binlog events in 'mysql-bin.000013';
#我们来到/var/lib/mysql目录下，默认的binlog在这里
cd /var/lib/mysql

我们需要知道最后一次删除表是在什么位置

mysqlbinlog mysql-bin.000013 | grep -C 2 “DROP TABLE”

我们可以看到删除每一条命令的时间点和position。记录它们。

# at 13836902
#210813  7:31:27 server id 1  end_log_pos 13837051 CRC32 0xff733779 	Query	thread_id=5	exec_time=6133	error_code=0
SET TIMESTAMP=1628839887/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE IF EXISTS `dept_info` /* generated by server */
--
# at 13842194
#210813  7:31:27 server id 1  end_log_pos 13842347 CRC32 0x5490a31e 	Query	thread_id=5	exec_time=6133	error_code=0
SET TIMESTAMP=1628839887/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE IF EXISTS `operation_log` /* generated by server */
--
# at 15136041
#210813  7:31:28 server id 1  end_log_pos 15136201 CRC32 0xe265dc77 	Query	thread_id=5	exec_time=6133	error_code=0
SET TIMESTAMP=1628839888/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE IF EXISTS `templates_change_log` /* generated by server */
--
# at 15138969
#210813  7:31:28 server id 1  end_log_pos 15139129 CRC32 0x17543742 	Query	thread_id=5	exec_time=6133	error_code=0
SET TIMESTAMP=1628839888/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE IF EXISTS `user_department_info` /* generated by server */
--
# at 15588672
#210813  7:31:29 server id 1  end_log_pos 15588821 CRC32 0x7eaf9bd6 	Query	thread_id=5	exec_time=6132	error_code=0
SET TIMESTAMP=1628839889/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE IF EXISTS `ironbank_user_info` /* generated by server */

接下来我们导入数据,和上面的操作类似，只需要在执行sql脚本之后，将时间点和position也记录一下就可以了，然后我们就可以随意的对数据进行蹂躏，如果出了错误或污染了数据，就可以通过binlog回滚到数据刚被导入的状态,我这里是通过position进行回滚，如果记录时间点也是一样的，如果不知道范围的话往往不太好操作，因为你如果只记录了开始没有结束他就会从起点一直到结束都会重写，反而也一样。

mysqlbinlog --start-position=3775337 --skip-gtids --stop-position=6796448 mysql-bin.000013 | mysql -u root -p