Mysql闪回工具之binlog2sql的原理及其使用

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql闪回工具之binlog2sql的原理及其使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6015字，纯文字阅读大概需要9分钟。

内容图文

用途

• 数据回滚
• 主从切换后数据不一致的修复
• 从 binlog 生成标准 SQL，带来的衍生功能

闪回原理简析

开始之前，先说说闪回。我们都知道 MySQL binlog 以 event 为单位，记录数据库的变更信息，这些信息能够帮助我们重现这之间的所有变化，也就是所谓的闪回。

binlog 有三种可选的格式：

• statement：基于 SQL 语句的模式，binlog 数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
• mixed：混合模式，根据语句来选用是 statement 还是 row 模式；
• row：基于行的模式，记录的是行的完整变化。安全，但 binlog 会比其他两种模式大很多；

利用 binlog 做闪回，需要将 binlog 格式设置为 row，因为我们需要最详尽的信息来确定操作之后数据不会出错。

既然 binlog 以 event 形式记录了所有的变更信息，那么我们把需要回滚的 event，从后往前回滚回去即可。

回滚操作：

对于 delete 操作，我们从 binlog 提取出 delete 信息，反向生成 insert 回滚语句；
对于 insert 操作，反向生成 delete 回滚语句；
对于 update 操作，根据信息生成反向的 update 语句；
 

竟然这么厉害？？

来实例演习下

主要测试 DML，也就是 delete、update、insert 等操作的闪回效果。

但是，DDL 语句，比如drop,truncate 在整个使用中都是无法被回滚的，这种情况，只能用最近的备份数据+二进制日志恢复

本次实验，更改一条数据，并删除一条数据，然后从解析 binlog 信息，到使用 binlog2sql 工具来生成标准和回滚 SQL，来剖析整个运行过程。

一、准备工作

1.确定版本信息和binlog格式

  mysql版本：5.7.12

   查看binlog格式的命令

mysql> show variables like ‘binlog_format‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+

2.安装binlog2sql工具     #（随便安装在合适的服务器即可，比如我在内网15安装了一个）

   安装参考链接：http://note.youdao.com/noteshare?id=85a90269a21d877962bfce0dfa40a90b&sub=71CD3E637F534AA1AFF129563F6A064E

3.在mysql的主服务器上，创建闪回操作账号的权限 

mysql>   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘glon‘@‘%‘  identified by ‘123456‘;


mysql> show grants for ‘glon‘@‘%‘;
+--------------------------------------------------------------------------+
| Grants for glon@% |
+--------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘glon‘@‘%‘ |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.初始化数据    

CREATE TABLE `edai_binlog2sql` (

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
 

insert into edai_binlog2sql (name,create_time) values (‘Glon Ho‘,‘2012-10-1‘),(‘Eason Chan‘, ‘2016-05-02‘),(‘Jacky Cheung‘, ‘2015-05-02‘);

 
mysql> select * from edai_binlog2sql;
+----+--------------+---------------------+
| id | name | create_time |
+----+--------------+---------------------+
| 4 | Glon Ho | 2012-10-01 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
| 6 | Jacky Cheung | 2015-05-02 00:00:00 |
+----+--------------+---------------------+
3 rows in set (0.00 sec)

 

• 进行 update 和 delete 操作

mysql> update edai_binlog2sql set create_time = ‘2017-05-12‘ where name = ‘Glon Ho‘;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from edai_binlog2sql;
+----+--------------+---------------------+
| id | name | create_time |
+----+--------------+---------------------+
| 4 | Glon Ho | 2017-05-12 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
| 6 | Jacky Cheung | 2015-05-02 00:00:00 |
+----+--------------+---------------------+
3 rows in set (0.00 sec)

mysql> delete from edai_binlog2sql where name = ‘Jacky Cheung‘;
Query OK, 1 row affected (0.09 sec)

mysql> select * from edai_binlog2sql;
+----+------------+---------------------+
| id | name | create_time |
+----+------------+---------------------+
| 4 | Glon Ho | 2017-05-12 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
+----+------------+---------------------+
2 rows in set (0.00 sec)

操作时候，Glon Ho 的时间改变了，而 Jacky Cheung 也被删除了。

我们来看下mysql的binlog文件位置

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 6159854 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

好的，接下来使用binlog2sql工具

1）解析出标准的SQL

python binlog2sql.py -h192.168.1.21 -P30136 -uglon -p‘123456‘ -d xcrm -t edai_binlog2sql  --start-file=mysql-bin.000001 > edai_binlog2sql.sql

cat edai_binlog2sql.sql

可以看到，几乎完美重现了我们上面执行过的 SQL，而且生成的每个 SQL 后面都带有该语句在 binlog 中的 position 信息和该语句的执行时间。

2)解析想要回滚的SQL

比如，我想回滚刚刚操作的，edai_binlog2sql 后面两个update和DELETE操作

时间节点就是：start 6159262   end 6159823    #分析最好用pos分析，这个可以更准确的定位到想要的

 python binlog2sql.py  --flashback  -h192.168.1.21 -P30136 -uglon -p‘123456‘ -dxcrm -tedai_binlog2sql --start-file=mysql-bin.000001 --start-position=6159262    --stop-pos=6159823 > edai_binlog2sql-new.sql 

[root@soft binlog2sql]# cat edai_binlog2sql-new.sql 
INSERT INTO `xcrm`.`edai_binlog2sql`(`create_time`, `id`, `name`) VALUES (‘2015-05-02 00:00:00‘, 6, ‘Jacky Cheung‘); #start 6159565 end 6159823 time 2018-11-22 15:16:30
UPDATE `xcrm`.`edai_binlog2sql` SET `create_time`=‘2012-10-01 00:00:00‘, `id`=4, `name`=‘Glon Ho‘ WHERE `create_time`=‘2017-05-12 00:00:00‘ AND `id`=4 AND `name`=‘Glon Ho‘ LIMIT 1; #start 6159262 end 6159534 time 2018-11-22 15:15:46

可以看到，我们刚刚的delete语句，被反转为insert语句，update 修改为原来的时间

拿到了具体的恢复语句

那我们拿去数据库执行吧

Mysql闪回工具之binlog2sql的原理及其使用

标签：技术   方法   其他   不一致   orm   好的   prim   update   uda   

本文系统来源：https://www.cnblogs.com/tianfen/p/10001807.html

内容总结

以上是互联网集市为您收集整理的Mysql闪回工具之binlog2sql的原理及其使用全部内容，希望文章能够帮你解决Mysql闪回工具之binlog2sql的原理及其使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端