暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 解析工具my2sql使用

解析工具my2sql使用

原创 随心逐流 2022-11-23
867

工具简介：

my2sql：Go 语言编写（可直接下载 linux 二进制版本执行），除了闪回，还提供了前滚和事务分析的功能

主要参数 :
-work-type：指定工作类型（前滚、闪回、事务分析），合法值分别为：2sql（默认）、rollback、stats
-sql：过滤 DML 语句的类型，合法值为：insert、update、delete
-ignorePrimaryKeyForInsert：对于 work-type 为 2sql 的 insert 操作，忽略主键（适合大量数据导入的场景）
-big-trx-row-limit int：判定为大事务的阈值（默认 500 行），合法值区间：10-30000 行
-long-trx-seconds int：判定为长事务的阈值（默认 300 秒），合法值区间：1-3600 秒
-databases：过滤库，默认为全部库
-tables：过滤表，默认为全部表
-start-file：指定开始的 binlog 文件
-start-pos：指定 binlog 文件中开始的点位
-start-datetime：指定开始的时间
-stop-datetime：指定结束的时间
-output-dir：指定文件生成目录
-output-toScreen：指定输出到屏幕
-tl：指定时区（time location），默认为 local（Asia/Shanghai）

下载安装

wget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql
[root@vm04 soft]# mv /soft/my2sql /usr/local/bin/my2sql
[root@vm04 soft]# chmod u+x /usr/local/bin/my2sql

创建恢复数据使用的用户

mysql> grant SELECT,REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'tom'@'%';
Query OK, 0 rows affected (0.45 sec)


模拟实战：

1.数据快速回滚(闪回)

1.1 先记录时间及binlog位置

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-23 18:21:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000003 |  1360476 |              |                  | b72f5029-6808-11ed-a9f5-000c29dcca09:1-114 |
+------------------+----------+--------------+------------------+--------------------------------------------+


1.2.执行删除数据操作

mysql> delete from film_text;
Query OK, 1000 rows affected (0.07 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-23 18:22:45 |
+---------------------+
1 row in set (0.00 sec)


1.3. 执行my2sql命令生成delete语句的回滚sql文件

my2sql  -user tom -password 123 -host 127.0.0.1 -port 3306 \
-mode repl \
-work-type rollback  \
-start-file mysql-bin.000003  \
-start-datetime "2022-11-23 18:21:10" \
-stop-datetime "2022-11-23 18:23:00" \
-output-dir /data/tmpdir

输出结果：

[2022/11/23 19:44:23] [info] events.go:221 start thread to write redo/rollback sql into file
[2022/11/23 19:44:23] [info] binlogsyncer.go:164 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 tom   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0 <nil> 0xc0001020c0 0x637c00}
[2022/11/23 19:44:23] [info] events.go:61 start thread 2 to generate redo/rollback sql
[2022/11/23 19:44:23] [info] events.go:61 start thread 1 to generate redo/rollback sql
[2022/11/23 19:44:23] [info] binlogsyncer.go:400 begin to sync binlog from position (mysql-bin.000003, 4)
[2022/11/23 19:44:23] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/11/23 19:44:23] [info] repl.go:16 start to get binlog from mysql
[2022/11/23 19:44:23] [info] binlogsyncer.go:816 rotate to (mysql-bin.000003, 4)
[2022/11/23 19:44:28] [info] repl.go:84 deadline exceeded.
[2022/11/23 19:44:28] [info] repl.go:18 finish getting binlog from mysql
[2022/11/23 19:44:28] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/11/23 19:44:28] [info] events.go:196 exit thread 2 to generate redo/rollback sql
[2022/11/23 19:44:28] [info] events.go:196 exit thread 1 to generate redo/rollback sql
[2022/11/23 19:44:28] [info] events.go:270 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2022/11/23 19:44:28] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2022/11/23 19:44:28] [info] rollback_process.go:41 start to revert tmp file /data/tmpdir/.rollback.3.sql into /data/tmpdir/rollback.3.sql
[2022/11/23 19:44:28] [info] rollback_process.go:156 finish reverting tmp file /data/tmpdir/.rollback.3.sql into /data/tmpdir/rollback.3.sql
[2022/11/23 19:44:28] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2022/11/23 19:44:28] [info] events.go:283 finish reverting content order of tmp files
[2022/11/23 19:44:28] [info] events.go:288 exit thread to write redo/rollback sql into file

1.4 查看回滚sql文件内容：

[root@vm04 tmpdir]# more -10 rollback.3.sql 
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (1000,'ZORRO ARK','A Intrepid Panorama of a Mad Scientist And a Boy who must Redeem a Boy in A Monastery');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (999,'ZOOLANDER FICTION','A Fateful Reflection of a Waitress And a Boat who must Discover a Sumo Wrestler in Ancient China');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (998,'ZHIVAGO CORE','A Fateful Yarn of a Composer And a Man who must Face a Boy in The Canadian Rockies');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (997,'YOUTH KICK','A Touching Drama of a Teacher And a Cat who must Challenge a Technical Writer in A U-Boat');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (996,'YOUNG LANGUAGE','A Unbelieveable Yarn of a Boat And a Database Administrator who must Meet a Boy in The First Manned Space Station');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (995,'YENTL IDAHO','A Amazing Display of a Robot And a Astronaut who must Fight a Womanizer in Berlin');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (994,'WYOMING STORM','A Awe-Inspiring Panorama of a Robot And a Boat who must Overcome a Feminist in A U-Boat');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (993,'WRONG BEHAVIOR','A Emotional Saga of a Crocodile And a Sumo Wrestler who must Discover a Mad Cow in New Orleans');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (992,'WRATH MILE','A Intrepid Reflection of a Technical Writer And a Hunter who must Defeat a Sumo Wrestler in A Monastery');
INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (991,'WORST BANGER','A Thrilling Drama of a Madman And a Dentist who must Conquer a Boy in The Outback');

设置行号查看记录数993 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
994 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
995 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (6,'AGENT TRUMAN','A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
996 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
997 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
998 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
999 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
1000 INSERT INTO `sakila`.`film_text` (`film_id`,`title`,`description`) VALUES (1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');

1.5 进行数据闪回

[root@vm04 tmpdir]# mysql -uroot -p123 -P3306 -h127.0.0.1 sakila < /data/tmpdir/rollback.3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

查看恢复结果

mysql> select count(1) from film_text;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)


2.生成DML统计信息，可以找到哪些表更新的比较频繁

2.1 执行一些模拟操作

mysql> create table t3 (a int, b int); 
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 select 8, 9; 
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    8 |    9 |
+------+------+
1 row in set (0.00 sec)

mysql> update t3 set b=10 where a=9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> insert into t3 select 9, 11;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

2.2 利用my2sql进行状态分析，work-type改写为stats

my2sql  -user tom -password 123 -host 127.0.0.1 -port 3306 \
-mode repl \
-work-type stats \
-start-file mysql-bin.000003  \
-start-datetime "2022-11-23 20:34:59" \
-stop-datetime "2022-11-23 20:50:00" \
-output-dir /data/tmpdir

[root@vm04 tmpdir]# cat binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql-bin.000003  2022-11-23_20:35:10 2022-11-23_20:35:10 1855919    1856009    1        0        0        db01            t3                  
mysql-bin.000003  2022-11-23_20:35:40 2022-11-23_20:35:49 1856177    1856525    1        0        1        db01            t3                  
mysql-bin.000003  2022-11-23_20:40:00 2022-11-23_20:40:26 1856693    1857051    1        1        0        db01            t3


3 找出某个时间点数据库是否有大事务

3.1 模拟大事务

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from film_text;
Query OK, 1000 rows affected (0.07 sec)

3.2 分析大事务，work-type改写为stats，big-trx-row-limit 500来指定超过1w行的DML为大事务；实际上默认超过500行就会被统计

my2sql  -user tom -password 123 -host 127.0.0.1 -port 3306 \
-mode repl \
-work-type rollback  \
-start-file mysql-bin.000003  \
-big-trx-row-limit 500 \
-output-dir /data/tmpdir

查看分析结果

[root@vm04 tmpdir]# cat biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql-bin.000003  2022-11-23_21:23:29 2022-11-23_21:23:29 1857147    1972913    1000     0          [sakila.film_text(inserts=0, updates=0, deletes=1000)]


4 找出某个时间点数据库是否有长事务

4.1 模拟长事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t3 set b=22 where a=21;select sleep(10);commit;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

4.2 分析长事务

参数long-trx-seconds指定长事务的阈值为10s，只要超过这个值的事务就会被统计；如果不指定该参数，默认执行超过5min的事务会被统计

my2sql  -user tom -password 123 -host 127.0.0.1 -port 3306 \
-mode repl \
-work-type rollback  \
-start-file mysql-bin.000003  \
-long-trx-seconds 5 \
-output-dir /data/tmpdir

查看结果文件

[root@vm04 tmpdir]# cat biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql-bin.000003  2022-11-23_21:23:29 2022-11-23_21:23:29 1857147    1972913    1000     0          [sakila.film_text(inserts=0, updates=0, deletes=1000)]
mysql-bin.000003  2022-11-23_21:29:25 2022-11-23_21:29:35 1972978    1973181    1        10         [db01.t3(inserts=0, updates=1, deletes=0)]


备注：被分析的数据库需要满足以下条件：

(1)使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
(2)只能回滚DML， 不能回滚DDL
(3)支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
(4)此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
(5)MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

my2sqlbinlog
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论