my2sql安装部署和使用

环境:

OS:Centos 7

my2sql:v2

 

说明：使用my2sql必须连接到解析binlog所在的实例

 

1.下载
# raw 原始文件下载
cd /soft
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql

# 赋予执行权限

chmod +x my2sql

 

# 放到全局PATH
mv my2sql /usr/local/bin/

 

# 验证
[root@host113 my2sql]# my2sql --help

[root@host113 my2sql]# my2sql -v
my2sql V2.0

 

2.创建账号

CREATE USER 'my2sql'@'%' IDENTIFIED BY 'my2sql';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'my2sql'@'%';
FLUSH PRIVILEGES;

 

3.创建测试表并写入数据

use db_test
CREATE TABLE `tb_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(4000) DEFAULT NULL,
  `email` varchar(32),
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

insert into tb_test(name,email) values('test1','test1@qq.com');
insert into tb_test(name,email) values('test2','test2@qq.com');
insert into tb_test(name,email) values('test3','test3@qq.com');
insert into tb_test(name,email) values('test4','test4@qq.com');
insert into tb_test(name,email) values('test5','test5@qq.com');
insert into tb_test(name,email) values('test6','test6@qq.com');

 

 

4.解析
192.168.1.113
mkdir -p /tmp/my2sql_tb_test
##mode=file 只能解析单个binlog
my2sql -user my2sql -password "my2sql" -host 192.168.1.113 -port 3306 -databases db_test -tables tb_test -mode file -local-binlog-file /home/mysql57/mysqllog/binlog/binlog.001218 -work-type 2sql -output-dir /tmp/my2sql_tb_test

 

##mode=repl 解析-local-binlog-file指定的binlog之后所有的binlog
my2sql -user my2sql -password "my2sql" -host 192.168.1.113 -port 3306 -databases db_test -tables tb_test -mode repl -local-binlog-file /home/mysql57/mysqllog/binlog/binlog.001215 -work-type 2sql -output-dir /tmp/my2sql_tb_test

解析出来的文件内容如下:

[root@dev-dbmaster-13 my2sql_tb_test]# more forward.4721.sql 
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (1,'test1','test1@qq.com','2026-09-16 09:20:12','2026-09-16 09:20:12');
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (2,'test2','test2@qq.com','2026-09-16 09:20:12','2026-09-16 09:20:12');
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (3,'test3','test3@qq.com','2026-09-16 09:20:12','2026-09-16 09:20:12');
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (4,'test4','test4@qq.com','2026-09-16 09:20:12','2026-09-16 09:20:12');
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (5,'test5','test5@qq.com','2026-09-16 09:20:12','2026-09-16 09:20:12');
INSERT INTO `test`.`tb_test` (`id`,`name`,`email`,`create_time`,`update_time`) VALUES (6,'test6','test6@qq.com','2026-09-16 09:20:13','2026-09-16 09:20:13');

 

-work-type 2sql 解析出来的是原始的操作语句,-work-type rollback 解析出来的是 rollback.sql,用来数据恢复
解析出来的内容如下:

[root@dev-dbmaster-13 my2sql_tb_test]# more rollback.4721.sql 
DELETE FROM `test`.`tb_test` WHERE `id`=6;
DELETE FROM `test`.`tb_test` WHERE `id`=5;
DELETE FROM `test`.`tb_test` WHERE `id`=4;
DELETE FROM `test`.`tb_test` WHERE `id`=3;
DELETE FROM `test`.`tb_test` WHERE `id`=2;
DELETE FROM `test`.`tb_test` WHERE `id`=1;