【DB宝43】MySQL误操作闪回恢复利器之my2sql（上）

华为云博客 技术博客 14831 65

一、my2sql简介

可以用于MySQL误操作闪回的工具包括my2sql、binlog2sql和MyFlash等工具，其中，个人感觉my2sql最好用。

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

my2sql的GitHub地址：https://github.com/liuhr/my2sql

优点：

  • 功能丰富，不仅支持回滚操作，还有其他实用功能。
  • 基于golang实现，速度快，全量解析1.1Gbinlog只需要1分30秒左右，当前其他类似开源工具一般要几十分钟。

二、my2sql用途

  • 数据快速回滚(闪回)
  • 主从切换后新master丢数据的修复
  • 从binlog生成标准SQL，带来的衍生功能
  • 生成DML统计信息，可以找到哪些表更新的比较频繁
  • IO高TPS高， 查出哪些表在频繁更新
  • 找出某个时间点数据库是否有大事务或者长事务
  • 主从延迟，分析主库执行的SQL语句
  • 除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成

三、产品性能对比

binlog2sql当前是业界使用最广泛的MySQL回滚工具，下面对my2sql和binlog2sql做个性能对比。

my2sql binlog2sql
1.1G binlog生成回滚SQL 1分40秒 65分钟
1.1G binlog生成原始SQL 1分30秒 50分钟
1.1G binlog生成表DML统计信息、以及事务统计信息 40秒 不支持

四、安装

-- 申请一台全新的主机
docker rm -f lhrmy2sql
docker run -d --name lhrmy2sql -h lhrmy2sql \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init

docker exec -it lhrmy2sql bash



-- 方法1：编译安装
yum install -y golang
go version
go env | grep GOPATH

mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .


-- 方法2：直接下载编译好的二进制文件。
-- https://github.com/liuhr/my2sql/blob/master/releases/my2sql
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/my2sql
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql


-- 若不能下载，请添加以下解析：
echo "
13.229.188.59 github.com
199.232.4.133 raw.githubusercontent.com
" >> /etc/hosts

五、重要参数说明

  • -U 优先使用unique key作为where条件，默认false

  • -add-extraInfo 是否把database/table/datetime/binlogposition…信息以注释的方式加入生成的每条sql前，默认false

datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773

UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'

  • -big-trx-row-limit n
    transaction with affected rows greater or equal to this value is considerated as big transaction
    找出影响了n行数据的事务，默认500条

  • -databases 、 -tables 库及表条件过滤, 以逗号分隔

  • -sql 要解析的sql类型，可选参数insert、update、delete，默认全部解析

  • -doNotAddPrifixDb
    Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1)
    默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql，也可以生成不带库名的sql

  • -file-per-table 为每个表生成一个sql文件

  • -full-columns
    For update sql, include unchanged columns. for update and delete, use all columns to build where condition.
    default false, this is, use changed columns to build set part, use primary/unique key to build where condition
    生成的sql是否带全列信息，默认false

  • -ignorePrimaryKeyForInsert 生成的insert语句是否去掉主键，默认false

  • -output-dir 将生成的结果存放到制定目录

  • -output-toScreen 将生成的结果打印到屏幕，默认写到文件

  • -threads 线程数，默认8个

  • -work-type 2sql表示生成原始sql，rollback表示生成回滚sql，stats表示只统计DML、事务信息

六、使用案例

6.1 解析出标准SQL

根据时间点解析出标准SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" \
-output-dir ./tmpdir

根据pos点解析出标准SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-output-dir ./tmpdir

6.2 解析出回滚SQL

根据时间点解析出回滚SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" \
-output-dir ./tmpdir

根据pos点解析出回滚SQL

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-output-dir ./tmpdir

6.3 统计DML以及大事务

统计时间范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  \
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

统计一段pos点范围各个表的DML操作数量，统计一个事务大于500条、时间大于300秒的事务

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir

6.4 从某一个pos点解析出标准SQL，并且持续打印到屏幕

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-pos 4   -output-toScreen

七、限制

  • 使用回滚/闪回功能时，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才能解析

八、案例演示

8.1 准备MySQL环境

docker rm -f mysql3306
docker run -d --name mysql3306 -h mysql3306 -p 3306:3306 \
  -v /etc/mysql/mysql3306/conf:/etc/mysql/conf.d \
  -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
  mysql:5.7.30
  
  
docker exec -it mysql3306 bash
docker logs -f mysql3306


cat >  /etc/mysql/mysql3306/conf/my.cnf <<"EOF"
[mysqld]
default-time-zone = '+8:00'
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=573306
character_set_server=utf8mb4
EOF

docker restart mysql3306



mysql -uroot -plhr -h192.168.66.35 -P3306
create database lhrdb default character set utf8mb4;

8.2 执行DML操作

show master logs;
flush logs;


select now();

use lhrdb;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
  `content` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{\"age\":13,\"author\":\"liuhan\"}');

select * from student;
select now();

show master logs;
show binlog events in 'mysql3306-bin.000002';

执行过程：

MySQL [(none)]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       154 |
+----------------------+-----------+
3 rows in set (0.05 sec)

MySQL [(none)]>
MySQL [(none)]> create database lhrdb default character set utf8mb4;
Query OK, 1 row affected (0.05 sec)

MySQL [(none)]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       346 |
+----------------------+-----------+
3 rows in set (0.05 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.06 sec)

MySQL [(none)]>
MySQL [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:22:38 |
+---------------------+
1 row in set (0.05 sec)

MySQL [(none)]>
MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> CREATE TABLE `student` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `number` int(11) NOT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
    ->   `content` json DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `idx_name` (`number`,`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
Query OK, 1 row affected (0.08 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
Query OK, 1 row affected (0.06 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{\"age\":13,\"author\":\"liuhan\"}');
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> select * from student;
+------+--------+--------+---------------------+---------------------------------+
| id   | number | name   | add_time            | content                         |
+------+--------+--------+---------------------+---------------------------------+
| 1227 |     18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1229 |     20 | chenxi | 2020-07-11 16:20:50 | NULL                            |
| 1231 |     21 | chenxi | 2020-07-12 10:12:45 | NULL                            |
| 1232 |    134 | asdf   | 2020-07-12 11:08:41 | NULL                            |
| 1233 |     26 | ranran | 2020-07-15 19:06:03 | NULL                            |
+------+--------+--------+---------------------+---------------------------------+
5 rows in set (0.05 sec)

MySQL [lhrdb]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:23:16 |
+---------------------+
1 row in set (0.05 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql3306-bin.000001 |   3071539 |
| mysql3306-bin.000002 |       154 |
| mysql3306-bin.000003 |       397 |
| mysql3306-bin.000004 |      2131 |
+----------------------+-----------+
4 rows in set (0.05 sec)
（完）