binlog mysql 闪回 mysql闪回工具

转载

mob64ca14144dde 2023-08-31 19:53:54

文章标签 binlog mysql 闪回数据库 mysql linux 运维 文章分类 MySQL 数据库

MySQL闪回工具—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秒	不支持

安装

首先需要go语言的环境

# yum方式安装
yum install -y golang
go version
go env | grep GOPATH

# tar包方式安装
 wget https://dl.google.com/go/go1.10.3.linux-amd64.tar.gz
 mv go1.10.3.linux-amd64.tar.gz /usr/local/
 cd /usr/local/
 tar -zxvf  go1.10.3.linux-amd64.tar.gz
 vim /etc/profile
# 在最后一排添加
 export GOROOT=/usr/local/go
 export PATH=$PATH:$GOROOT/bin
# 重新编译profile
source /etc/profile
# 查看版本信息
go version

下载my2sql工具

# 从git上下载zip包并解压
unzip my2sql-master.zip
mv my2sql-master my2sql
cd my2sql/releases/centOS_release_7.x/
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql

使用my2sql的前提条件

需要准备好MySQL环境，需要满足以下条件

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

关于如何安装MySQL可以参考我的另一篇博客【Linux上的MySQL8搭建与初始化】

MySQL8.0的默认加密方式不是mysql_native_password，所以要在配置文件中加入default_authentication_plugin =mysql_native_password

使用

此次是生产上CI/CD需要MySQL闪回，所以用时间来作为条件进行闪回

create database test;
use test;
create table t1(id int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (5);

-- 记录一下时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-04-19 14:48:42 |
+---------------------+
1 row in set (0.02 sec)

-- 等几分钟
insert into t1 values (6);
insert into t1 values (7);
insert into t1 values (8);
insert into t1 values (9);
insert into t1 values (10);

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-04-19 14:54:52 |
+---------------------+
1 row in set (0.02 sec)

此时我们使用闪回工具回到2021-04-19 14:52:57这个第一次插完数据的时间

my2sql -user root -password Mysql_123 -host 127.0.0.1   -port 3306  -work-type rollback -start-file binlog.000001 -start-datetime "2021-04-19 14:48:42" -output-dir /root/tmpdir

[2021/04/19 20:56:04] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/04/19 20:56:05] [info] events.go:208 start thread to write redo/rollback sql into file
[2021/04/19 20:56:04] [info] events.go:58 start thread 1 to generate redo/rollback sql
[2021/04/19 20:56:05] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/04/19 20:56:05] [info] binlogsyncer.go:360 begin to sync binlog from position (binlog.000001, 4)
[2021/04/19 20:56:06] [info] repl.go:15 start to get binlog from mysql
[2021/04/19 20:56:06] [info] binlogsyncer.go:777 rotate to (binlog.000001, 4)
[2021/04/19 20:56:06] [info] binlogsyncer.go:777 rotate to (binlog.000002, 4)
[2021/04/19 20:56:06] [info] binlogsyncer.go:777 rotate to (binlog.000003, 4)
[2021/04/19 20:56:07] [info] events.go:239 finish processing binlog.000003 684
[2021/04/19 20:56:12] [info] repl.go:83 deadline exceeded.
[2021/04/19 20:56:12] [info] repl.go:17 finish getting binlog from mysql
[2021/04/19 20:56:12] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/04/19 20:56:12] [info] events.go:183 exit thread 1 to generate redo/rollback sql
[2021/04/19 20:56:12] [info] events.go:257 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/04/19 20:56:12] [info] rollback_process.go:15 start thread 2 to revert rollback sql files
[2021/04/19 20:56:12] [info] rollback_process.go:41 start to revert tmp file /root/tmpdir/.rollback.1.sql into /root/tmpdir/rollback.1.sql
[2021/04/19 20:56:12] [info] rollback_process.go:156 finish reverting tmp file /root/tmpdir/.rollback.1.sql into /root/tmpdir/rollback.1.sql
[2021/04/19 20:56:12] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/04/19 20:56:12] [info] events.go:270 finish reverting content order of tmp files
[2021/04/19 20:56:12] [info] events.go:275 exit thread to write redo/rollback sql into file

此时可以发现我们的回滚sql已经生成完毕了，切换到我们刚才指定的outdir

cd /root/tmpdir
vim rollback.1.sql

# 内容如下
DELETE FROM `test`.`t1` WHERE `id`=10;
DELETE FROM `test`.`t1` WHERE `id`=9;
DELETE FROM `test`.`t1` WHERE `id`=8;
DELETE FROM `test`.`t1` WHERE `id`=7;
DELETE FROM `test`.`t1` WHERE `id`=6;

此时已经成功生成了回滚sql，然后执行sql就可以回到2021-04-19 14:48:42这个时间点的状态了。