生产数据恢复系列之使用my2sql恢复MySQL8 误删数据
热衷于分享各种干货知识，大家有想看或者想学的可以评论区留言，秉承着“开源知识来源于互联网，回归于互联网”的理念，分享一些日常工作中能用到或者频率比较的内容，希望大家能够喜欢，并提出宝贵地意见，我们一起提升，守住自己的饭碗。
一、my2sql概述
go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。
二、功能
数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能
生成DML统计信息，可以找到哪些表更新的比较频繁
IO高TPS高， 查出哪些表在频繁更新
找出某个时间点数据库是否有大事务或者长事务
主从延迟，分析主库执行的SQL语句
除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成二、安装go语言
下载地址：https://golang.google.cn/dl/
[root@myoracle ~]# tar xvf go1.23.1.linux-amd64.tar.gz -C /usr/local/
[root@myoracle ~]# vi /etc/profile.d/go.sh
添加以下内容
export PATH=$PATH:/usr/local/go/bin
[root@myoracle ~]# . /etc/profile.d/go.sh
[root@myoracle ~]# which go
[root@myoracle ~]# go version
三、安装my2sql
下载地址：https://github.com/liuhr/my2sql?tab=readme-ov-file
这里我们上传下载好的包,大家也可以从我的网盘下载。
链接: https://pan.baidu.com/s/1exOgSXRytwDLzrdwUhuC4A提取码: idy4
[root@myoracle ~]#unzip my2sql-master.zip
[root@myoracle ~]# cd my2sql-master[root@myoracle my2sql-master]# go build . #编译安装，没报错即安装成功四、数据恢复
1、先模拟增加数据
root@localhost:(none)>create database if not exists test;
root@localhost:(none)>use test;
root@localhost:(none)>CREATE TABLE ceshi (id int NOT NULL AUTO_INCREMENT,a int NOT NULL,PRIMARY KEY (id)) ENGINE=InnoDB CHARSET=utf8mb4;
root@localhost:(none)>insert into ceshi values (1,1),(2,2);2、模拟误操作
root@localhost:test>select * from ceshi;
root@localhost:test>delete from ceshi where id=1;
root@localhost:test>select * from ceshi;3、查找误操作事务所在的Binlog并复制到新目录
这里我们根据时间段，判断误操作事务所在的Binlog为mysql-bin.000006 也可以通过mysqlbinlog去查看
[root@myoracle mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /data/mysql/mysql-bin.000006 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr[root@myoracle mysql]# cp /data/mysql/mysql-bin.000006 /root/my2sql-master4、查看误操作事务开始和结束的位点
[root@myoracle my2sql-master]# mysqlbinlog mysql-bin.000006 --start-datetime='2024-09-23 19:00:00' --stop-datetime='2024-09-23 21:40:00' --base64-output=decode-rows -v > operation.sql
[root@myoracle my2sql-master]# cat operation.sql
从图中我们可以看到误操作开始的位点可以认为是1163，结束的位点是1207，my2sql可以根据时间点和POS点进行数据恢复。
5、分析在线binlog，正向解析SQL
[root@myoracle my2sql-master]# ./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000006 -start-datetime "2024-09-23 19:00:00" -output-dir ./[root@myoracle my2sql-master]# cat forward.6.sql 6、分析在线binlog，生成回滚SQL
[root@myoracle my2sql-master]# ./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-bin.000006 -start-datetime "2024-09-23 19:00:00" -output-dir ./[root@myoracle my2sql-master]# cat rollback.6.sql
7、插入恢复的数据
root@localhost:test>INSERT INTO `test`.`ceshi` (`id`,`a`) VALUES (1,1);
root@localhost:test>select * from ceshi;
8、其他
[root@myoracle my2sql-master]#./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode file -work-type 2sql -local-binlog-file /data/mysql/mysql-bin.000006 -start-file /data/mysql/mysql-bin.000038 -start-datetime "2024-09-23 10:20:00" -output-dir ./tmpdir #分析已经离线binlog，生成回滚SQL五、总结
相比于Binlog2sql,my2sql的速度会快很多，而且有闪回的功能，但也有很多限制：
使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
只能回滚DML， 不能回滚DDL
使用rollback功能时，要解析的binlog段，表结构要保持一致（例如：解析mysql-bin.000001文件，此binlog文件的的表有add column或drop column操作，则执行rollback可能会执行异常）
支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析
文中的概念来源于互联网，如有侵权，请联系我删除。
欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。
喜欢作者
暂无留言