my2sql通过binlog恢复数据

my2sql恢复binlog

一、安装
1,首先需要go语言的环境
# tar包方式安装
mkdir /root/t3 -p
cd /root/t3
wget https://dl.google.com/go/go1.10.3.linux-amd64.tar.gz
tar -xzvf go1.10.3.linux-amd64.tar.gz
vi /etc/profile
export PATH=$PATH:/root/t3/go/bin
source /etc/profile

2,解压my2sql
my2sql的GitHub地址：https://github.com/liuhr/my2sql
下载zip包，上传到/root/t3目录
unzip my2sql-master.zip
拷贝my2sql 到/usr/sbin/
cd /root/t3/my2sql-master/releases/centOS_release_7.x
cp my2sql /usr/sbin
which my2sql


完成安装,mysql可以正常使用。


二、转换binlog
1，到binlog日志目录，查看binlog
-rw-r----- 1 mysql mysql 1074515055 Mar 17 00:00 mysql-bin.000041
-rw-r----- 1 mysql mysql 419627172 Mar 17 00:51 mysql-bin.000042
-rw-r----- 1 mysql mysql 593937533 Mar 17 01:39 mysql-bin.000043
-rw-r----- 1 mysql mysql 194 Mar 20 19:37 mysql-bin.000044
-rw-r----- 1 mysql mysql 152 Mar 20 19:36 mysql-bin.index

2，本文转换mysql-bin.000043为sql语句
my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -work-type 2sql -start-file mysql-bin.000043 -stop-datetime "2023-03-17 04:48:42" -output-dir /root/t3

三，生成sql语句
1,在输出目录 /root/t3,生成3个文件
-rw-r--r-- 1 root root 34298712 Mar 20 19:51 biglong_trx.txt
-rw-r--r-- 1 root root 50832 Mar 20 19:51 binlog_status.txt
-rw-r--r-- 1 root root 235930377 Mar 20 19:51 forward.43.sql

2，查看转换后的sql文件
[root@docker t3]# head -10 forward.43.sql
UPDATE `tpcc_test`.`warehouse` SET `w_ytd`='4914633.00' WHERE `w_id`=8;
UPDATE `tpcc_test`.`district` SET `d_ytd`='472273.00' WHERE (`d_w_id`=8 AND `d_id`=5);
UPDATE `tpcc_test`.`customer` SET `c_balance`='-524.00' WHERE (`c_w_id`=8 AND `c_d_id`=5 AND `c_id`=2156);
INSERT INTO `tpcc_test`.`history` (`h_c_id`,`h_c_d_id`,`h_c_w_id`,`h_d_id`,`h_w_id`,`h_date`,`h_amount`,`h_data`) VALUES (2156,5,8,5,8,'2023-03-17 01:11:07','514.00','Ol6iIdPEgwnW');
UPDATE `tpcc_test`.`warehouse` SET `w_ytd`='5090206.00' WHERE `w_id`=5;
UPDATE `tpcc_test`.`district` SET `d_ytd`='521726.00' WHERE (`d_w_id`=5 AND `d_id`=1);
UPDATE `tpcc_test`.`customer` SET `c_balance`='-2688.00' WHERE (`c_w_id`=5 AND `c_d_id`=1 AND `c_id`=103);
INSERT INTO `tpcc_test`.`history` (`h_c_id`,`h_c_d_id`,`h_c_w_id`,`h_d_id`,`h_w_id`,`h_date`,`h_amount`,`h_data`) VALUES (103,1,5,1,5,'2023-03-17 01:11:07','2678.00','1b6ZldXBWWeqDa');
UPDATE `tpcc_test`.`warehouse` SET `w_ytd`='4710278.00' WHERE `w_id`=1;
UPDATE `tpcc_test`.`district` SET `d_ytd`='410962.00' WHERE (`d_w_id`=1 AND `d_id`=6);

3，根据表名，可以过滤想要的sql语句
cat forward.43.sql |grep customer >customer.sql
[root@docker t3]# head -10 customer.sql
UPDATE `tpcc_test`.`customer` SET `c_balance`='-524.00' WHERE (`c_w_id`=8 AND `c_d_id`=5 AND `c_id`=2156);
UPDATE `tpcc_test`.`customer` SET `c_balance`='-2688.00' WHERE (`c_w_id`=5 AND `c_d_id`=1 AND `c_id`=103);
UPDATE `tpcc_test`.`customer` SET `c_balance`='-88.00' WHERE (`c_w_id`=1 AND `c_d_id`=6 AND `c_id`=2856);