my2sql工具之快速入门

尚未崩坏  金牌会员 | 2022-9-16 17:19:57 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 921|帖子 921|积分 2763


  • GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  • GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
my2sql工具之快速入门


  • 1.什么是my2sql
  • 2.如何快速部署my2sql工具
  • 3.如何使用my2sql工具

    • 3.1使用my2sql工具解析binglog文件
    • 3.2使用my2sql工具快速闪回

  • 4.遇到的问题
  • 5.使用限制
  • 6.总结
1. 什么是my2sql？

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

方式一 go编译
  1. git clone https://github.com/liuhr/my2sql.git
  2. cd my2sql/
  3. go build .
复制代码
方式二 已编译好的二进制文件

二进制文件下载地址
https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql
复制到我的docker中
  1. #1、把my2sql 二进制文件复制到mysql8的容器中
  2. docker cp /usr/local/bin/my2sql  mysql8:/usr/local/bin
  4. #2、进入容器
  5. docker exec -it mysql8  bash
  6. chmod +x /usr/local/bin/my2sql
复制代码
3. 如何使用my2sql工具

3.1 使用my2sql工具解析binglog文件

step1：查询binglog文件
  1. mysql> show variables like 'binlog_format';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | binlog_format | ROW   |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
  9. mysql> show master status;
  10. +----------+----------+--------------+------------------+--------------------------------------------+
  11. | File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
  12. +----------+----------+--------------+------------------+----------------------------------------------+
  13. | 1.000001 |     1021 |              |                  | ecd66956-f106-11ec-bb85-0242ac110005:1-19718 |
  14. +----------+----------+--------------+------------------+----------------------------------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> flush logs;
  18. Query OK, 0 rows affected (0.71 sec)
  20. mysql> show master status;
  21. +----------+----------+--------------+------------------+----------------------------------------------+
  22. | File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
  23. +----------+----------+--------------+------------------+----------------------------------------------+
  24. | 1.000002 |      193 |              |                  | ecd66956-f106-11ec-bb85-0242ac110005:1-19718 |
  25. +----------+----------+--------------+------------------+----------------------------------------------+
  26. 1 row in set (0.00 sec)
  28. mysql> show variables like 'innodb_log_group_home_dir';
  29. +---------------------------+-------+
  30. | Variable_name             | Value |
  31. +---------------------------+-------+
  32. | innodb_log_group_home_dir | ./    |
  33. +---------------------------+-------+
  34. 1 row in set (0.01 sec)
  36. mysql> show variables like '%datadir%';
  37. +---------------+-----------------+
  38. | Variable_name | Value           |
  39. +---------------+-----------------+
  40. | datadir       | /var/lib/mysql/ |
  41. +---------------+-----------------+
  42. 1 row in set (0.00 sec)
复制代码
step2：查询测试表：
  1. -- 查看测试表结构
  2. mysql> show create table sbtest.sbtest1\G;
  3. *************************** 1. row ***************************
  4.        Table: sbtest1
  5. Create Table: CREATE TABLE `sbtest1` (
  6.   `id` int NOT NULL AUTO_INCREMENT,
  7.   `k` int NOT NULL DEFAULT '0',
  8.   `c` char(120) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
  9.   `pad` char(60) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
  10.   PRIMARY KEY (`id`),
  11.   KEY `k_1` (`k`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
  13. 1 row in set (0.00 sec)
复制代码
  1. --查看测试表行数
  2. mysql> select count(*) from sbtest.sbtest1;
  3. +----------+
  4. | count(*) |
  5. +----------+
  6. |   100000 |
  7. +----------+
  8. 1 row in set (0.00 sec)
复制代码
  1. --查看测试表的检验值
  2. mysql> checksum table sbtest.sbtest1;
  3. +----------------+------------+
  4. | Table          | Checksum   |
  5. +----------------+------------+
  6. | sbtest.sbtest1 | 4167997150 |
  7. +----------------+------------+
  8. 1 row in set (0.09 sec)
复制代码
  1. -- 删除5万行数据
  2. mysql> delete from sbtest.sbtest1 where id<50000; 【这里删除了49999行数据】
  3. Query OK, 49999 rows affected (7.72 sec)
  5. --再次看测试表的检验值
  6. mysql> checksum table sbtest.sbtest1;
  7. +----------------+------------+
  8. | Table          | Checksum   |
  9. +----------------+------------+
  10. | sbtest.sbtest1 | 1629663751 |
  11. +----------------+------------+
  12. 1 row in set (0.04 sec)
复制代码
step3：解析binglog文件
  1. #解析binglog文件
  2. root@29b86217f7d4:/tmp# my2sql  -user root -password ****** -host 127.0.0.1 -port 3306 -databases sbtest -tables sbtest1 -mode repl -work-type 2sql -start-file 1.000002 -output-dir /tmp
  3. [2022/07/28 10:26:42] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false UTC false 0 0s 0s 0 false false 0}
  4. [2022/07/28 10:26:42] [info] events.go:60 start thread 1 to generate redo/rollback sql
  5. [2022/07/28 10:26:42] [info] binlogsyncer.go:360 begin to sync binlog from position (1.000002, 4)
  6. [2022/07/28 10:26:42] [info] stats_process.go:166 start thread to analyze statistics from binlog
  7. [2022/07/28 10:26:42] [info] events.go:210 start thread to write redo/rollback sql into file
  8. [2022/07/28 10:26:42] [info] events.go:60 start thread 2 to generate redo/rollback sql
  9. [2022/07/28 10:26:42] [info] repl.go:16 start to get binlog from mysql
  10. [2022/07/28 10:26:42] [info] binlogsyncer.go:777 rotate to (1.000002, 4)
  11. [2022/07/28 10:26:50] [info] repl.go:84 deadline exceeded.
  12. [2022/07/28 10:26:50] [info] repl.go:18 finish getting binlog from mysql
  13. [2022/07/28 10:26:50] [info] stats_process.go:266 exit thread to analyze statistics from binlog
  14. [2022/07/28 10:26:50] [info] events.go:185 exit thread 1 to generate redo/rollback sql
  15. [2022/07/28 10:26:50] [info] events.go:185 exit thread 2 to generate redo/rollback sql
  16. [2022/07/28 10:26:50] [info] events.go:274 finish writing redo/forward sql into file
  17. [2022/07/28 10:26:50] [info] events.go:277 exit thread to write redo/rollback sql into file
  18. root@29b86217f7d4:/tmp# ls -lrt
  19. total 2392
  20. -rw-r--r-- 1 root root     261 Jul 28 10:26 biglong_trx.txt
  21. -rw-r--r-- 1 root root 2438845 Jul 28 10:26 forward.2.sql
  22. -rw-r--r-- 1 root root     288 Jul 28 10:26 binlog_status.txt
复制代码
step4：查看解析后的sql：
  1. --文件binlog_status.txt和biglong_trx.txt是事务的统计信息
  2. root@29b86217f7d4:/tmp# cat binlog_status.txt
  3. binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
  4. 1.000002          2022-07-28_10:24:06 2022-07-28_10:24:06 341        9536267    0        0        49999    sbtest          sbtest1            
  5. root@29b86217f7d4:/tmp# cat biglong_trx.txt
  6. binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
  7. 1.000002          2022-07-28_10:24:06 2022-07-28_10:24:06 269        9536294    49999    0          [sbtest.sbtest1(inserts=0, updates=0, deletes=49999)]
复制代码
  1. --文件forward.2.sql是binlog解析之后的sql
  2. root@29b86217f7d4:/tmp#  more -10 forward.2.sql
  3. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=1;
  4. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=2;
  5. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=3;
  6. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=4;
  7. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=5;
  8. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=6;
  9. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=7;
  10. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=8;
  11. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=9;
  12. DELETE FROM `sbtest`.`sbtest1` WHERE `id`=10;
  13. --More--(0%)
复制代码
3.2 使用my2sql工具快速闪回

step1：生成闪回sql文件
  1. root@29b86217f7d4:/tmp# my2sql  -user root -password ****** -host 127.0.0.1 -port 3306 -databases sbtest -tables sbtest1 -mode repl -work-type rollback   -start-file 1.000002  -output-dir /tmp/flashback/
  2. [2022/07/28 10:42:55] [info] events.go:60 start thread 1 to generate redo/rollback sql
  3. [2022/07/28 10:42:55] [info] events.go:210 start thread to write redo/rollback sql into file
  4. [2022/07/28 10:42:55] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false UTC false 0 0s 0s 0 false false 0}
  5. [2022/07/28 10:42:55] [info] events.go:60 start thread 2 to generate redo/rollback sql
  6. [2022/07/28 10:42:55] [info] stats_process.go:166 start thread to analyze statistics from binlog
  7. [2022/07/28 10:42:55] [info] binlogsyncer.go:360 begin to sync binlog from position (1.000002, 4)
  8. [2022/07/28 10:42:55] [info] repl.go:16 start to get binlog from mysql
  9. [2022/07/28 10:42:55] [info] binlogsyncer.go:777 rotate to (1.000002, 4)
  10. [2022/07/28 10:43:00] [info] repl.go:84 deadline exceeded.
  11. [2022/07/28 10:43:00] [info] repl.go:18 finish getting binlog from mysql
  12. [2022/07/28 10:43:00] [info] stats_process.go:266 exit thread to analyze statistics from binlog
  13. [2022/07/28 10:43:00] [info] events.go:185 exit thread 1 to generate redo/rollback sql
  14. [2022/07/28 10:43:00] [info] events.go:185 exit thread 2 to generate redo/rollback sql
  15. [2022/07/28 10:43:00] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
  16. [2022/07/28 10:43:00] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
  17. [2022/07/28 10:43:00] [info] rollback_process.go:41 start to revert tmp file /tmp/flashback/.rollback.2.sql into /tmp/flashback/rollback.2.sql
  18. [2022/07/28 10:43:00] [info] rollback_process.go:156 finish reverting tmp file /tmp/flashback/.rollback.2.sql into /tmp/flashback/rollback.2.sql
  19. [2022/07/28 10:43:00] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
  20. [2022/07/28 10:43:00] [info] events.go:272 finish reverting content order of tmp files
  21. [2022/07/28 10:43:00] [info] events.go:277 exit thread to write redo/rollback sql into file
复制代码
step2：查看生成的闪回文件：
  1. root@29b86217f7d4:/tmp/flashback# ls -lrt
  2. total 12596
  3. -rw-r--r-- 1 root root      261 Jul 28 10:42 biglong_trx.txt
  4. -rw-r--r-- 1 root root      288 Jul 28 10:43 binlog_status.txt
  5. -rw-r--r-- 1 root root 12888636 Jul 28 10:43 rollback.2.sql
复制代码
  1. root@29b86217f7d4:/tmp/flashback# cat  biglong_trx.txt
  2. binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
  3. 1.000002          2022-07-28_10:24:06 2022-07-28_10:24:06 269        9536294    49999    0          [sbtest.sbtest1(inserts=0, updates=0, deletes=49999)]
复制代码
  1. root@29b86217f7d4:/tmp/flashback# cat binlog_status.txt
  2. binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
  3. 1.000002          2022-07-28_10:24:06 2022-07-28_10:24:06 341        9536267    0        0        49999    sbtest          sbtest1           
复制代码
  1. root@29b86217f7d4:/tmp/flashback# more -10 rollback.2.sql
  2. INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49999,33022,'82276829554-28600016482-71437056503-67189283057-49828408020-97469013057-54486869404-00631592142
  3. -97314346455-10619483378','14496218158-82953408254-72982060504-31493955809-57788873512');
  4. INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49998,50244,'96937339387-30971064351-79066739653-24906328840-02614118448-07131634761-96921531810-30574594369
  5. -22852947139-53163560618','11061918782-99235282357-54725926348-09882016546-43656283296');
  6. INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49997,49958,'23875561992-79383179714-31844019265-78678195929-11039506986-44432953782-19018620372-45679869851
  7. -21838821757-54316746647','72021998255-21544610411-49097617755-83325624702-67496909817');
  8. INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49996,49784,'64121185719-74243968401-44193775190-53796401184-56297492349-79981936074-03057285270-03714583251
  9. -22209198873-10937378934','77159337465-83656217977-70877234699-71552982384-42185635425');
  10. INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49995,49750,'32364537835-96169411677-52025383891-99856461851-54876781624-38035712955-18254695168-34412213489
  11. -24174915574-41228645716','40846762001-49118260546-21871620253-63339659850-56585169893');
  12. --More--(0%)
复制代码
step3：应用rollback文件进行数据恢复
  1. mysql -uroot -p****** -P3306 -h127.0.0.1 sbtest < /tmp/flashback/rollback.2.sql
复制代码
step4：最后检查恢复情况
  1. mysql> select count(*) from sbtest.sbtest1;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |   100000 |
  6. +----------+
  7. 1 row in set (0.01 sec)
复制代码
  1. mysql> checksum table sbtest.sbtest1;
  2. +----------------+------------+
  3. | Table          | Checksum   |
  4. +----------------+------------+
  5. | sbtest.sbtest1 | 4167997150 |
  6. +----------------+------------+
  7. 1 row in set (0.08 sec)
复制代码
4. 遇到的问题

问题1：Connect mysql failed this authentication plugin is not supported
  1. my2sql  -user greatsql -password ****** -host 127.0.0.1 -port 3306  -mode repl -work-type 2sql -start-file binlog.000001
  2. [2022/07/28 10:00:01] [fatal] context.go:575 Connect mysql failed this authentication plugin is not supported
复制代码
解决
  1. #修改
  2. ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
  3. FLUSH PRIVILEGES;
复制代码
问题2：Connect mysql failed this user requires mysql native password authentication.
  1. my2sql  -user greatsql  -password ****** 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file binlog.000001
  2. [2022/07/28 09:35:08] [fatal] context.go:575 Connect mysql failed this user requires mysql native password authentication.
复制代码
解决
  1. #mysql配置文件中增加以下配置项并重启
  2. 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丢数据的修复、大事务和长事务的分析、主从延迟分析等，后面有空继续分享my2sql在事务方面的应用，大家也可以自己动手实践一下。
参考文档

https://github.com/liuhr/my2sql/blob/master/README.md

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

尚未崩坏

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

存储 挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表