my2sql工具恢复数据

1.什么是my2sql

my2sql是go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。

2.如何快速部署my2sql工具

方式一 go编译
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .

方式二 下载已编译好的二进制文件（强烈建议，下载后不用安装直接运行）
https://gitcode.net/mirrors/liuhr/my2sql?utm_source=csdn_github_accelerator


3.如何使用my2sql工具

3.1使用my2sql工具解析binglog文件

step1：查询binglog文件

主要步骤：

show variables like 'binlog_format';
flush logs;
show master status;
show variables like 'innodb_log_group_home_dir';
show variables like '%datadir%';


主要步骤详细过程：

root@localhost :(none) 14:31:44>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost :(none) 14:32:39>show variables like '%datadir%';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| datadir | /data/mysqldb/data/ |
+---------------+---------------------+
1 row in set (0.00 sec)


step2：模拟误操作

主要步骤：

show create table fuwa.fuwa\G;
select count(*) from fuwa.fuwa;
checksum table fuwa.fuwa;
delete from fuwa.fuwa;
checksum table fuwa.fuwa;


主要步骤详细过程：

-- 查看测试表结构
root@localhost :(none) 14:36:41>show create table fuwa.fuwa\G
*************************** 1. row ***************************
Table: fuwa
Create Table: CREATE TABLE `fuwa` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`addtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

--再次看测试表的检验值
root@localhost :(none) 14:38:26> checksum table fuwa.fuwa;
+-----------+----------+
| Table | Checksum |
+-----------+----------+
| fuwa.fuwa | 0 |
+-----------+----------+
1 row in set (0.00 sec)


step3：解析binglog文件

解析

#解析binglog文件
cd /root/my2sql-master/releases/centOS_release_7.x
./my2sql -user root -password mysql -host 127.0.0.1 -port 2213 -databases fuwa -tables fuwa -mode repl -work-type 2sql -start-file mybinlog.000010 -output-dir /tmp
ls -l /tmp/

[root@localhost my2sql-master]#
total 12
-rw-r--r--. 1 root root 107 Sep 19 14:42 biglong_trx.txt
-rw-r--r--. 1 root root 288 Sep 19 14:42 binlog_status.txt
-rw-r--r--. 1 root root 760 Sep 19 14:42 forward.10.sql
drwx------. 2 root root 6 Sep 19 07:50 vmware-root_659-4013788787
drwx------. 2 root root 6 Sep 19 13:46 vmware-root_663-4022243318


step4：查看解析后的sql

• 文件binlog_status.txt和biglong_trx.txt是事务的统计信息

[root@localhost my2sql-master]# cat /tmp/binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mybinlog.000010 2023-09-19_14:38:26 2023-09-19_14:38:26 287 486 0 0 8 fuwa fuwa
[root@localhost my2sql-master]# cat /tmp/biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables


• 文件forward.2.sql是binlog解析之后的sql

[root@localhost my2sql-master]# more -10 /tmp/forward.10.sql
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=4 AND `name`='李' AND `addtime`='2000-12-12 01:00:00');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=3 AND `name`='孙' AND `addtime`='2022-01-12 12:12:12');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=2 AND `name`='钱' AND `addtime`='2023-09-19 13:48:44');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=1 AND `name`='赵' AND `addtime`='2023-09-19 13:48:44');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=4 AND `name`='李' AND `addtime`='2000-12-12 01:00:00');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=3 AND `name`='孙' AND `addtime`='2022-01-12 12:12:12');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=2 AND `name`='钱' AND `addtime`='2023-09-19 13:48:44');
DELETE FROM `fuwa`.`fuwa` WHERE (`id`=1 AND `name`='赵' AND `addtime`='2023-09-19 13:48:44');


3.2使用my2sql工具快速闪回

step1：生成闪回sql文件

mkdir -p /tmp/flashback
cd /root/my2sql-master/releases/centOS_release_7.x
./my2sql -user root -password mysql -host 127.0.0.1 -port 2213 -databases fuwa -tables fuwa -mode repl -work-type rollback -start-file mybinlog.000010 -output-dir /tmp/flashback/

--视情况加时间参数
-start-datetime '2023-09-19 15:00:00'
-stop-datetime '2023-09-19 15:00:00'


输出结果

[root@localhost centOS_release_7.x]# mkdir -p /tmp/flashback/
[root@localhost centOS_release_7.x]# ./my2sql -user root -password mysql -host 127.0.0.1 -port 2213 -databases fuwa -tables fuwa -mode repl -work-type rollback -start-file mybinlog.000010 -output-dir /tmp/flashback/
[2023/09/19 14:50:44] [info] binlogsyncer.go:164 create BinlogSyncer with config {1113306 mysql 127.0.0.1 2213 root utf8 false false false Local false 0 0s 0s 0 false false 0 0xc0000547e0 0x637c00}
[2023/09/19 14:50:44] [info] binlogsyncer.go:400 begin to sync binlog from position (mybinlog.000010, 4)
[2023/09/19 14:50:44] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2023/09/19 14:50:44] [info] events.go:221 start thread to write redo/rollback sql into file
[2023/09/19 14:50:44] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2023/09/19 14:50:44] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2023/09/19 14:50:44] [info] repl.go:16 start to get binlog from mysql
[2023/09/19 14:50:44] [info] binlogsyncer.go:816 rotate to (mybinlog.000010, 4)
[2023/09/19 14:50:49] [info] repl.go:84 deadline exceeded.
[2023/09/19 14:50:49] [info] repl.go:18 finish getting binlog from mysql
[2023/09/19 14:50:49] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2023/09/19 14:50:49] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2023/09/19 14:50:49] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2023/09/19 14:50:49] [info] events.go:270 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2023/09/19 14:50:49] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2023/09/19 14:50:49] [info] rollback_process.go:41 start to revert tmp file /tmp/flashback/.rollback.10.sql into /tmp/flashback/rollback.10.sql
[2023/09/19 14:50:49] [info] rollback_process.go:156 finish reverting tmp file /tmp/flashback/.rollback.10.sql into /tmp/flashback/rollback.10.sql
[2023/09/19 14:50:49] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2023/09/19 14:50:49] [info] events.go:283 finish reverting content order of tmp files
[2023/09/19 14:50:49] [info] events.go:288 exit thread to write redo/rollback sql into file


step2：查看生成的闪回文件

cd /tmp/flashback/
ls -lrt
more -10 rollback.10.sql

step3：应用rollback文件进行数据恢复

mysql -uroot -pmysql -P2213 -h127.0.0.1 fuwa < /tmp/flashback/rollback.10.sql


step4：最后检查恢复情况

mysql -uroot -p -P2213 fuwa
select count(*) from fuwa.fuwa;
checksum table fuwa.fuwa;

root@localhost :fuwa 14:58:32>checksum table fuwa.fuwa;
+-----------+------------+
| Table | Checksum |
+-----------+------------+
| fuwa.fuwa | 4059555528 |
+-----------+------------+
1 row in set (0.00 sec)


4.遇到的问题

问题1：Connect mysql failed this authentication plugin is not supported

my2sql -user greatsql -password ****** -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file binlog.000001
[2022/07/28 10:00:01] [fatal] context.go:575 Connect mysql failed this authentication plugin is not supported


解决

#修改
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;


问题2：Connect mysql failed this user requires mysql native password authentication.

my2sql -user greatsql -password ****** 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file binlog.000001
[2022/07/28 09:35:08] [fatal] context.go:575 Connect mysql failed this user requires mysql native password authentication.


解决

#mysql配置文件中增加以下配置项并重启
default_authentication_plugin=mysql_native_password


5.使用限制

• 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响。
• 只能回滚DML， 不能回滚DDL。
• 支持指定-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才能解析。

6.总结

my2sql除了可以用来做binlog的解析、闪回，还提供主从切换后新master丢数据的修复、大事务和长事务的分析、主从延迟分析等。