- 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 编译
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .
形式二 已编译好的二进制文件
二进制文件下载地址
https://github.com/liuhr/my2s…
复制到我的 docker 中
#1、把 my2sql 二进制文件复制到 mysql8 的容器中
docker cp /usr/local/bin/my2sql mysql8:/usr/local/bin
#2、进入容器
docker exec -it mysql8 bash
chmod +x /usr/local/bin/my2sql
3. 如何应用 my2sql 工具
3.1 应用 my2sql 工具解析 binglog 文件
step1:查问 binglog 文件
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show master status;
+----------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+----------------------------------------------+
| 1.000001 | 1021 | | | ecd66956-f106-11ec-bb85-0242ac110005:1-19718 |
+----------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.71 sec)
mysql> show master status;
+----------+----------+--------------+------------------+----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+----------------------------------------------+
| 1.000002 | 193 | | | ecd66956-f106-11ec-bb85-0242ac110005:1-19718 |
+----------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_log_group_home_dir';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | ./ |
+---------------------------+-------+
1 row in set (0.01 sec)
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
step2:查问测试表:
-- 查看测试表构造
mysql> show create table sbtest.sbtest1\G;
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
1 row in set (0.00 sec)
-- 查看测试表行数
mysql> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
-- 查看测试表的测验值
mysql> checksum table sbtest.sbtest1;
+----------------+------------+
| Table | Checksum |
+----------------+------------+
| sbtest.sbtest1 | 4167997150 |
+----------------+------------+
1 row in set (0.09 sec)
-- 删除 5 万行数据
mysql> delete from sbtest.sbtest1 where id<50000;【这里删除了 49999 行数据】Query OK, 49999 rows affected (7.72 sec)
-- 再次看测试表的测验值
mysql> checksum table sbtest.sbtest1;
+----------------+------------+
| Table | Checksum |
+----------------+------------+
| sbtest.sbtest1 | 1629663751 |
+----------------+------------+
1 row in set (0.04 sec)
step3:解析 binglog 文件
# 解析 binglog 文件
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
[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}
[2022/07/28 10:26:42] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2022/07/28 10:26:42] [info] binlogsyncer.go:360 begin to sync binlog from position (1.000002, 4)
[2022/07/28 10:26:42] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/07/28 10:26:42] [info] events.go:210 start thread to write redo/rollback sql into file
[2022/07/28 10:26:42] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2022/07/28 10:26:42] [info] repl.go:16 start to get binlog from mysql
[2022/07/28 10:26:42] [info] binlogsyncer.go:777 rotate to (1.000002, 4)
[2022/07/28 10:26:50] [info] repl.go:84 deadline exceeded.
[2022/07/28 10:26:50] [info] repl.go:18 finish getting binlog from mysql
[2022/07/28 10:26:50] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/07/28 10:26:50] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2022/07/28 10:26:50] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2022/07/28 10:26:50] [info] events.go:274 finish writing redo/forward sql into file
[2022/07/28 10:26:50] [info] events.go:277 exit thread to write redo/rollback sql into file
root@29b86217f7d4:/tmp# ls -lrt
total 2392
-rw-r--r-- 1 root root 261 Jul 28 10:26 biglong_trx.txt
-rw-r--r-- 1 root root 2438845 Jul 28 10:26 forward.2.sql
-rw-r--r-- 1 root root 288 Jul 28 10:26 binlog_status.txt
step4:查看解析后的 sql:
-- 文件 binlog_status.txt 和 biglong_trx.txt 是事务的统计信息
root@29b86217f7d4:/tmp# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
1.000002 2022-07-28_10:24:06 2022-07-28_10:24:06 341 9536267 0 0 49999 sbtest sbtest1
root@29b86217f7d4:/tmp# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
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)]
-- 文件 forward.2.sql 是 binlog 解析之后的 sql
root@29b86217f7d4:/tmp# more -10 forward.2.sql
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=1;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=2;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=3;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=4;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=5;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=6;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=7;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=8;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=9;
DELETE FROM `sbtest`.`sbtest1` WHERE `id`=10;
--More--(0%)
3.2 应用 my2sql 工具疾速闪回
step1:生成闪回 sql 文件
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/
[2022/07/28 10:42:55] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2022/07/28 10:42:55] [info] events.go:210 start thread to write redo/rollback sql into file
[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}
[2022/07/28 10:42:55] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2022/07/28 10:42:55] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/07/28 10:42:55] [info] binlogsyncer.go:360 begin to sync binlog from position (1.000002, 4)
[2022/07/28 10:42:55] [info] repl.go:16 start to get binlog from mysql
[2022/07/28 10:42:55] [info] binlogsyncer.go:777 rotate to (1.000002, 4)
[2022/07/28 10:43:00] [info] repl.go:84 deadline exceeded.
[2022/07/28 10:43:00] [info] repl.go:18 finish getting binlog from mysql
[2022/07/28 10:43:00] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/07/28 10:43:00] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2022/07/28 10:43:00] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[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
[2022/07/28 10:43:00] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[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
[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
[2022/07/28 10:43:00] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2022/07/28 10:43:00] [info] events.go:272 finish reverting content order of tmp files
[2022/07/28 10:43:00] [info] events.go:277 exit thread to write redo/rollback sql into file
step2:查看生成的闪回文件:
root@29b86217f7d4:/tmp/flashback# ls -lrt
total 12596
-rw-r--r-- 1 root root 261 Jul 28 10:42 biglong_trx.txt
-rw-r--r-- 1 root root 288 Jul 28 10:43 binlog_status.txt
-rw-r--r-- 1 root root 12888636 Jul 28 10:43 rollback.2.sql
root@29b86217f7d4:/tmp/flashback# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
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)]
root@29b86217f7d4:/tmp/flashback# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
1.000002 2022-07-28_10:24:06 2022-07-28_10:24:06 341 9536267 0 0 49999 sbtest sbtest1
root@29b86217f7d4:/tmp/flashback# more -10 rollback.2.sql
INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49999,33022,'82276829554-28600016482-71437056503-67189283057-49828408020-97469013057-54486869404-00631592142
-97314346455-10619483378','14496218158-82953408254-72982060504-31493955809-57788873512');
INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49998,50244,'96937339387-30971064351-79066739653-24906328840-02614118448-07131634761-96921531810-30574594369
-22852947139-53163560618','11061918782-99235282357-54725926348-09882016546-43656283296');
INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49997,49958,'23875561992-79383179714-31844019265-78678195929-11039506986-44432953782-19018620372-45679869851
-21838821757-54316746647','72021998255-21544610411-49097617755-83325624702-67496909817');
INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49996,49784,'64121185719-74243968401-44193775190-53796401184-56297492349-79981936074-03057285270-03714583251
-22209198873-10937378934','77159337465-83656217977-70877234699-71552982384-42185635425');
INSERT INTO `sbtest`.`sbtest1` (`id`,`k`,`c`,`pad`) VALUES (49995,49750,'32364537835-96169411677-52025383891-99856461851-54876781624-38035712955-18254695168-34412213489
-24174915574-41228645716','40846762001-49118260546-21871620253-63339659850-56585169893');
--More--(0%)
step3:利用 rollback 文件进行数据恢复
mysql -uroot -p****** -P3306 -h127.0.0.1 sbtest < /tmp/flashback/rollback.2.sql
step4:最初查看复原状况
mysql> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
mysql> checksum table sbtest.sbtest1;
+----------------+------------+
| Table | Checksum |
+----------------+------------+
| sbtest.sbtest1 | 4167997150 |
+----------------+------------+
1 row in set (0.08 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 丢数据的修复、大事务和长事务的剖析、主从提早剖析等,前面有空持续分享 my2sql 在事务方面的利用,大家也能够本人入手实际一下。
参考文档
https://github.com/liuhr/my2s…
Enjoy GreatSQL :)
文章举荐:
乏味的 SQL DIGEST
ulimits 不失效导致数据库启动失败和相干设置阐明
MGR 及 GreatSQL 资源汇总
GreatSQL MGR FAQ
在 Linux 下源码编译装置 GreatSQL/MySQL
## 对于 GreatSQL
GreatSQL 是由万里数据库保护的 MySQL 分支,专一于晋升 MGR 可靠性及性能,反对 InnoDB 并行查问个性,是实用于金融级利用的 MySQL 分支版本。
Gitee:
https://gitee.com/GreatSQL/GreatSQL
GitHub:
https://github.com/GreatSQL/GreatSQL
Bilibili:
https://space.bilibili.com/1363850082/favlist
技术交换群:
微信:扫码增加
GreatSQL 社区助手
微信好友,发送验证信息加群
。