MySQL-binlog2sql：非主从实时同步+恢复误删数据

2021-09-05 18:36:11

文章目录

`MySQL-binlog2sql`：非主从实时同步+恢复误删数据

`MySQL-binlog2sql`：非主从实时同步+恢复误删数据

1.引

1）介绍

针对于DBA或开发人员，有时会误删或者误更新数据，如果是线上环境并且影响较大，就需要能快速回滚。

传统恢复方法是利用备份重搭实例，再应用去除错误sql后的binlog来恢复数据。此法费时费力，甚至需要停机维护，并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间，但快照的缺点是会影响mysql的性能。现在有不少好用而且效率又高的开源闪回工具如【binlog2sql、mysqlbinlog_flashback】，这些工具在工作中给DBA减轻了不少痛苦，以下针对binlog2sql的使用进行实践演练。

2）功能

数据快速回滚(闪回)
主从切换后数据不一致的修复
从binlog生成标准SQL，带来的衍生功能（sql可直接执行的那种~）

3）针对两种场景

场景1：用来拉取binlog，同步到其它数据库
- 无论是增删改查，都要同步到另一个数据库，所有数据均保持一致
  - 所需数据库
    - 源数据库，此处为【mysql234】
    - 目标数据库，此处为【mysql236】
  - 自动化同步binlog所有数据到目标数据库
场景2：用binlog2sql主要做紧急恢复来使用
- 针对于误操作删除了数据，用来做数据紧急恢复
  - 所需数据库
    - 主数据库，此处为【mysql234】
  - 手动同步binlog误删数据
两者区别：-B选项
- 最大的区别取决于增加此选项，此选项是将处理后的纯sql语句反向解析，即：
  - insert解析为delete
  - delete解析为insert
  - update不会改变

2.先决条件

原理：主要是对于delete操作，从binlog取出delete信息，生成insert的回滚语句。对于insert操作，回滚SQL是delete。对于update操作，回滚sql应该交换SET和WHERE的值。

1）安装`MySQL`

参见：MySQL安装部署启动

2）修改`MySQL`配置

针对于源数据库

cat my.cnf 
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full（默认）

3.安装`binlog2sql`

官网下载：戳我~
百度云链接：戳我~ 提取码：peng

推荐，百度云打包的文件是经我测试过的，不用担心版本问题，下载上传后可直接执行后面的解压安装命令

安装所需依赖工具包列表
- python-pip
- PyMySQL
- python-mysql-replication
- wheel argparse

1）解压

mkdir /opt/binlog2sql/packages && cd /opt/binlog2sql/packages	# 上传包

# 解压到上层
unzip binlog2sql.zip -d /binlog
cd /binlog
tar xf pip-10.0.1.tar.gz
tar xf PyMySQL-0.8.0.tar.gz
tar xf wheel-0.31.0.tar.gz
unzip binlog2sql-master.zip
unzip python-mysql-replication-master.zip
rm -f *.gz *.zip

# 查看目录
[root@web2 ~]# ll
total 20K
drwxr-xr-x 5 root root  4.0K Jan  4  2018 binlog2sql-master
drwxrwxrwx 4 root root  4.0K Apr 20  2018 pip-10.0.1
drwxr-xr-x 4  501 games 4.0K Dec 20  2017 PyMySQL-0.8.0
drwxr-xr-x 7 root root  4.0K Mar  1  2018 python-mysql-replication-master
drwxr-xr-x 4 2000  2000 4.0K Apr  2  2018 wheel-0.31.0

2）安装

若报错，则安装python3：yum install -y python3，将下方的python替换为python3

# 安装依赖环境
cd PyMySQL-0.8.0/
python setup.py install

cd ../wheel-0.31.0/
python setup.py install

cd ../python-mysql-replication-master/
python setup.py install

# 检测依赖安装，already表示已安装
cd ../pip-10.0.1/
pip freeze > requirements.txt
pip install -r requirements.txt

3）添加别名

方便一键调用，不用再单独执行python+py的路径了~

直接可以用binlog2sql来代替 python /opt/binlog2sql/binlog2sql-master/binlog2sql/binlog2sql.py这条命令

echo "alias binlog2sql='python /opt/binlog2sql/binlog2sql-master/binlog2sql/binlog2sql.py'" >> ~/.bashrc
source ~/.bashrc

4.准备测试数据

1）建库建表

CREATE DATABASE `peng` DEFAULT CHARACTER SET utf8mb4;
USE peng;

CREATE TABLE `binlog_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

2）插入数据

记录插入数据的时间，精确到秒，此时是【2021-11-19 15:20:06】

PS：可提前几秒

INSERT INTO `peng`.`binlog_test` (`id`, `name`) VALUES (1, '插入数据1');
INSERT INTO `peng`.`binlog_test` (`id`, `name`) VALUES (1, '插入数据2');
INSERT INTO `peng`.`binlog_test` (`id`, `name`) VALUES (1, '插入数据3');

3）查看数据

mysql> SELECT * FROM `peng`.`binlog_test`;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
|  2 | 插入数据2     |
|  3 | 插入数据3     |
+----+---------------+
3 rows in set (0.00 sec)

4）模拟误删除数据

模拟开发人员误删除数据，此时时间是【2021-11-19 15:32:14】左右

PS：若误删除数据时间记不清楚，恢复时可在此基础上往后延迟几秒或几分，若数据量比较多，时间间隔越小，排查起来也越快！

删除选中的两条记录

删除后，发现数据只剩一条

[root@myslq ~]# mysql234
mysql> select * from peng.binlog_test;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
+----+---------------+
1 row in set (0.00 sec)

5.场景1（数据实时同步）

场景了解参见：场景介绍

导出后会自动优化为纯sql语句，无没用的部分，可直接在数据库执行的那种~

1）案例

1、数据导出

导出前询问误删除数据的时间，精确到秒，基于上面的例子，记不清楚的话往后延迟几秒：【2021-11-19 15:33:00】

导出后可见，插入的三条和删除的两条都有记录，且后面带有position(位置点)、操作的时间记录

1> 导出命令

binlog2sql -h 172.23.0.234 -P 3306 -u root -peHu2016  -d peng -t binlog_test --start-file='mysql-bin.000006' --start-datetime='2021-11-19 15:20:00' --stop-datetime='2021-11-19 15:23:00' > binlog1.sql

2> 导出结果

[root@web2 ~]# cat binlog1.sql 
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 4741 end 4925 time 2021-11-19 15:20:31
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 4956 end 5140 time 2021-11-19 15:20:38
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 5171 end 5355 time 2021-11-19 15:20:44
DELETE FROM `peng`.`binlog_test` WHERE `id`=2 AND `name`='插入数据2' LIMIT 1; #start 5386 end 5570 time 2021-11-19 15:32:01
DELETE FROM `peng`.`binlog_test` WHERE `id`=3 AND `name`='插入数据3' LIMIT 1; #start 5601 end 5785 time 2021-11-19 15:32:01

2、数据导入

检查需要对应的posistion位置点、数据信息、时间点，确认无误后，方可导入至另一台数据库

另一台数据库要有对应结构、同名的库、表，或另一台已经导入过此数据库的全量数据（前提是上面插入的数据是在导入全量之后插入的）

PS：若数据量大，则可直接库外导入sql，或利用数据库工具来导入

在另一台导入上面的sql，发现与源数据库的数据是相同的，成功~

1> 导入命令

INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 4741 end 4925 time 2021-11-19 15:20:31
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 4956 end 5140 time 2021-11-19 15:20:38
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 5171 end 5355 time 2021-11-19 15:20:44
DELETE FROM `peng`.`binlog_test` WHERE `id`=2 AND `name`='插入数据2' LIMIT 1; #start 5386 end 5570 time 2021-11-19 15:32:01
DELETE FROM `peng`.`binlog_test` WHERE `id`=3 AND `name`='插入数据3' LIMIT 1; #start 5601 end 5785 time 2021-11-19 15:32:01

2> 导入结果

[root@web2 binlog2sql]# mysql236
mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 4741 end 4925 time 2021-11-19 15:20:31
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 4956 end 5140 time 2021-11-19 15:20:38
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 5171 end 5355 time 2021-11-19 15:20:44
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM `peng`.`binlog_test` WHERE `id`=2 AND `name`='插入数据2' LIMIT 1; #start 5386 end 5570 time 2021-11-19 15:32:01
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM `peng`.`binlog_test` WHERE `id`=3 AND `name`='插入数据3' LIMIT 1; #start 5601 end 5785 time 2021-11-19 15:32:01
Query OK, 1 row affected (0.00 sec)

3> 查看数据一致性

查看是否与源数据库数据一致

源数据库

mysql> select * from binlog_test;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
+----+---------------+
1 row in set (0.00 sec)

目标数据库

mysql> select * from binlog_test;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
+----+---------------+
1 row in set (0.00 sec)

2）自动同步

针对于自动同步，其目的就是让目标数据库实时拉取源数据库的binlog，再同步到目标数据库本身

1、配置源数据库

确保源数据库的binlog更新间隔，取决于设置的max_binlog_size大小及数据插入频繁率

binlog最大存储设置：默认为1G，建议修改为500M，便于快速拉取处理

1> 配置`max_binlog_size`大小

# 查看当前binlog存储最大值
mysql> show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 524288000 |
+-----------------+-----------+
1 row in set (0.00 sec)

# 永久修改(可带单位或直接写bytes)
vim /etc/my.cnf
max_binlog_size=500m
#max_binlog_size=524288000

# 临时修改(不能带单位，只能转换为字节bytes)
set global max_binlog_size=524288000;

2> binlog更新频率计算

目的是为了导出时是否需要更换--start-file='mysql-bin.000006'，即binlog文件名，若数据量极为频繁，则需要更换

经测试，500M的binlog存储，一般是不会被很快填满的，只要一天24小时内此文件不被填满产生新的binlog，我们就可以省很多事

下面为测试计算过程，配置为：max_binlog_size=500m，因为binlog是以event事件为单位来存储，最后某个事件若较大，超出500m则属于正常，一般不会太多

1>> 计算过程

# 获取列表
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000010 | 525055100 |
| mysql-bin.000011 | 528614358 |
| mysql-bin.000012 | 550397479 |
| mysql-bin.000013 | 524430486 |
| mysql-bin.000014 | 527412076 |
| mysql-bin.000015 | 554424041 |
| mysql-bin.000016 | 524289004 |
| mysql-bin.000017 | 524288328 |
| mysql-bin.000018 | 529522538 |
| mysql-bin.000019 | 543769836 |
| mysql-bin.000020 | 524327234 |
| mysql-bin.000021 | 524288472 |
| mysql-bin.000022 | 604406562 |
| mysql-bin.000023 | 524313044 |
| mysql-bin.000024 | 524288456 |
| mysql-bin.000025 | 550211581 |
| mysql-bin.000026 | 524288363 |
| mysql-bin.000027 | 524608627 |
| mysql-bin.000028 | 541076047 |
| mysql-bin.000029 | 524663266 |
| mysql-bin.000030 | 524288106 |
| mysql-bin.000031 |  61796217

# 编写获取binlog大小脚本
cat >> select_binlog.sh <<EOF
mysql -h 172.23.0.234 -u root -peHu2016 -e 'show binary logs;' | awk 'NR > 20 {print}' >> $file
echo "" >> $file
EOF

# 定时半小时执行一次
*/30 * * * * /bin/sh /root/scripts/select_binlog.sh &>/dev/null

# 查看日志
2021-11-22 15:30:01
mysql-bin.000029	524663266
mysql-bin.000030	524288106
mysql-bin.000031	49655438

2021-11-22 16:00:01
mysql-bin.000029	524663266
mysql-bin.000030	524288106
mysql-bin.000031	55292913

2021-11-22 16:30:01
mysql-bin.000029	524663266
mysql-bin.000030	524288106
mysql-bin.000031	66880555

2021-11-22 17:00:01
mysql-bin.000029	524663266
mysql-bin.000030	524288106
mysql-bin.000031	76985855


# 计算
30分钟依次相差多少
55292913 - 49655438 = 5637475	≈ 5m
66880555 - 55292913 = 11587642	≈ 11m
76985855 - 66880555 = 10105300  ≈ 9m
# 同归都按10M来算，计算24h内能产生的binlog大小
10 * 24 = 240m

2> 结论

结果为240m，刚到我们定义的500m的一半，故每天都可以直接获取最后一个binlog（即最新的binlog）

建议测试一天再进行计算，会更准确

后续随公司业务增长，需隔一段时间计算一下，若是超过500m，则会产生新的binlog：

脚本开头需定义if判断，判断binlog是否在脚本预期范围内，若数据突增产生新的binlog，则告警且不执行脚本，防止数据不一致
- get脚本内需增加binlog拉取命令：一个binlog一条拉取命令（多个则用for循环）
  
  不建议更改max_binlog_size的大小，数据拉取同步会随文件大小逐渐变慢
  - 第一条 ~ 最后一条不指定时间，只指定logfile（拉取整个binlog）；
  - 最后一条指定也可以指定logfile、start_time、stop_time：数据少，速度快
  - 不指定start_time则默认从binlog开头拉取，截止到stop_time
  - 不指定stop_time则默拉取到当前时间，从start_time开始
  - PS：都不指定，则拉取整个binlog
  - PS：就算有重复的sql语句也无碍，因为判断条件是表结构的所有字段都符合才会更改数据，重复执行的条件下不可能都符合
- import脚本无需更改，get时已处理完毕，直接导入即可~

2、注意事项

同步过程中，脚本每隔1分钟执行一次，拉取当前时间~前1分钟之间的数据保持每分钟同步数据

PS：Linux定时任务最小单位为分钟

1> 重复执行的`sql`

对于重复执行的sql语句的测试结果如下：

条件是某条数据的所有字段条件，不会误删误改其他数据

insert：无影响，会报错重复更新此条数据，不耽误后续sql继续执行；
update：无影响，重复更新此条数据；
delete：无影响，重复删除不报错，但也不会误删别的数据；

2> 同步`binlog`时间间隔

同步脚本：每分钟执行，确保数据实时同步
脚本条件同步间隔：确保不遗漏更新数据，且更新数据量较小
- start_time：2021-11-22 12:04:33（举例：前1分钟）
- stop_time：2021-11-22 12:05:33（举例：当前时间）

3> 时间获取格式示例

当前时间：date +%F' %H:%M:%S'：2021-11-22 13:45:00
前五分钟：date -d '5 mins ago' "+%F %H:%M:%S"：2021-11-22 13:40:00
- 前1小时：1 days ago
- 前1分钟：1 hours ago
- 前1分钟：1 mins ago
- 前1秒钟：1 second

3、钉钉告警

条件：当binlog过滤后不唯一（产生了多个新binlog）时，则调用告警脚本，发送告警信息并终止脚本运行

1> 创建钉钉机器人

1、钉钉-加号-发起群聊-自选-输入群名字-创建

2、进入创建的群-设置-只能群助手-添加机器人-加号-自定义机器人-添加-取名字-自定义关键字-告警内含有的关键字即可)-完成

3、复制Webhook

2>钉钉告警脚本

monitoring.sh需更改3处配置：

Dingding_Url：填写创建钉钉机器人时复制的Webhook，

Subject：钉钉告警标题，自定义
Body：钉钉告警内容，自定义

#!/bin/bash 
function SendMessageToDingding(){ 
    Dingding_Url="https://oapi.dingtalk.com/robot/send?access_token=138a41123s4dc6d39db75cdda5f0876q88f20c748dae6c" 
    # 发送钉钉消息
    curl "${Dingding_Url}" -H 'Content-Type: application/json' -d "
    {
        \"actionCard\": {
            \"title\": \"$1\", 
            \"text\": \"$2\", 
            \"hideAvatar\": \"0\", 
            \"btnOrientation\": \"0\", 
            \"btns\": [
                {
                    \"title\": \"$1\", 
                    \"actionURL\": \"\"
                }
            ]
        }, 
        \"msgtype\": \"actionCard\"
    }"
} 

# 调用脚本时，可手动输入标题、内容（此处用$1，$2为测试用）
Subject="$1" # 钉钉告警标题
Body="$2"    # 钉钉告警内容

### PS：
# 生产环境调用时，$1与$2需手动定义好告警的内容；
# 将此整个脚本放入if判断内，若不满足某个条件，则执行此整个告警代码块
###

SendMessageToDingding $Subject $Body

3> 测试脚本

标题或内容至少一项必须带着自定义的关键词binlog（创建钉钉机器人时指定的关键词），否则发送失败！

PS：标题与内容自身不能有空格，可用标点符号来间隔，如：

[root@web2 ~]# sh monitoring.sh '告警标题binlog' '我是告警内容！'
{"errcode":0,"errmsg":"ok"}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ETTzE40R-1637745371055)(C:\Users\Peng\AppData\Roaming\Typora\typora-user-images\image-20211122190421421.png)]

4、同步脚本

脚本结构目录：

[root@web2 binlog]# tree /binlog /binlog ├── 2021-11-23_19-09-08.sql └── scripts ├── monitoring.sh ├── sync.log └── sync.sh

自动同步脚本：sync.sh

#!/bin/bash

################################## 变量 ##################################
# 数据库变量
HOST='172.23.0.234'
HOST236='172.23.0.236'
USER='root'
PORT='3306'
PWD='eHu2016'
DATABASE='peng'
TABLE='binlog_test'

# binlog2sql变量
START_FILE=`mysql -h $HOST -u $USER -p$PWD -e 'show binary logs;' | awk 'END {print}'| awk '{print $1}'`
START_TIME=`date -d '1 mins ago' "+%F %H:%M:%S"`
STOP_TIME=`date +%F' %H:%M:%S'`
SQL_NAME=/binlog/`date +%F'_%H-%M-%S'`.sql

# 钉钉告警
SYNC_LOG=/binlog/scripts/sync.log
DINGDING='/binlog/scripts/monitoring.sh'

################################## 导出 ##################################
function GET(){
/usr/bin/python /opt/binlog2sql/binlog2sql-master/binlog2sql/binlog2sql.py -h $HOST -P $PORT -u $USER -p$PWD  -d $DATABASE  "--start-datetime=$START_TIME" "--stop-datetime=$STOP_TIME"  --start-file $START_FILE > $SQL_NAME
if [ $? -eq 0 ];then
	# 当`binlog`过滤后不唯一（产生了多个新`binlog`）时，则调用告警脚本，发送告警信息并终止脚本运行
	if [ `du -sh $SQL_NAME | awk '{print $1}'` == 0 ];then
		rm -f `find /binlog -type f -a -size 0c` && exit
	fi
# 分类计数导出语句
TIME=`date +%F'|%H:%M:%S'`
INSERT_COUNT=`cat $SQL_NAME | grep INSERT | wc -l`
UPDATE_COUNT=`cat $SQL_NAME | grep UPDATE | wc -l`
DELETE_COUNT=`cat $SQL_NAME | grep DELETE | wc -l`
DATA_COUNT=`cat $SQL_NAME | wc -l`

## 任务执行状态-->发送钉钉告警并写入日志
cat >> $SYNC_LOG <<EOF
$STOP_TIME 从$START_FILE导出数据成功！其中：
				INSERT：$INSERT_COUNT条
				UPDATE：$UPDATE_COUNT条
				DELETE：$DELETE_COUNT条
				  合计：$DATA_COUNT条
EOF
else
	echo "$STOP_TIME 从$START_FILE导出数据失败！" >> $SYNC_LOG
	/usr/bin/sh $DINGDING "Binlog拉取失败告警" "$TIME|FROM：$START_FILE【$SQL_NAME】"
fi
}

################################## 导入 ##################################
function IMPORT(){
/usr/bin/mysql -h $HOST236 -u $USER -p$PWD < $SQL_NAME

## 任务执行状态-->发送钉钉告警并写入日志
if [ $? -eq 0 ];then
cat >> $SYNC_LOG <<EOF
$STOP_TIME 从$START_FILE导入数据成功！其中：
				INSERT：$INSERT_COUNT条
				UPDATE：$UPDATE_COUNT条
				DELETE：$DELETE_COUNT条
				  合计：$DATA_COUNT
#######################################################
EOF
else
	echo "$STOP_TIME 从$START_FILE导入数据成功！" >> $SYNC_LOG
	echo "文件来源：$SQL_NAME 有重复条目，可忽略~" >> $SYNC_LOG
	echo "#######################################################" >> $SYNC_LOG
	/usr/bin/sh $DINGDING "Binlog导入重复告警" "$TIME|FROM：$START_FILE【$SQL_NAME】有重复条目，可忽略~"
fi
}

# 函数体调用
GET
IMPORT

5、开始测试

同步步骤：所有同步步骤都要记录时间，精确到秒！

全量同步

差异同步

- 防止数据有差异（此步骤会有重复数据条目，已测试，不影响数据一致性问题）

自动同步

- 加入定时任务，每分钟执行

检查数据一致性

实时监控sync日志，并查看目标数据库的数据与正服是否对应

- 数据是否对应：查看get的sql文件，过滤某一条语句，根据条件去目标数据库进行查询，数据存在则同步成功！

开始演示>>>

1> 全量同步

目标数据库导入源数据库的全备

PS：全备是从每天的00:00:00开始备份，比如此次备份文件的时间为：2021-11-22 00:00:00

mysql -uroot -peHu2016 < /backup/full.sql

2> 差异同步

时间戳概念：

1、源数据库定时任务时间为：2021-11-22 00:00:00，导入全备时间为2021-11-22 10:35:00，则中间相差了10h；

2、需计算全备与当前的时间差，并大于此时间差才可以，防止数据遗漏，所以start-datetime首次同步指定为12h（记录当前时间）；

3、基于上次记录时间，再次修改start-datetime为刚才执行同步的时间再往前退1分钟（记录当前时间）；

4、观察记录时间导入间隔，到现在同步命令应该可在10分钟内执行完导出导入，接下来进行自动同步步骤~

重复数据：

对于重复数据条目，我已做测试，可放心导入，不影响数据一致性问题（INSERT、UPDATE、DELETE）

1>> 源数据库导出

全备时间：2021-11-22 00:00:00

导出时间：2021-11-21 22:00:00

当前时间：2021-11-22 10:33:23

PS：导出时间是基于的全备时间，往前推2h，避免数据遗漏

/usr/bin/python /opt/binlog2sql/binlog2sql-master/binlog2sql/binlog2sql.py -h 172.23.0.234 -P 3306 -u root -peHu2016  -d peng  "--start-datetime=2021-11-21 22:00:00" "--stop-datetime=2021-11-22 10:33:23"  --start-file mysql-bin.000006 > `date +%F'_%H-%M-%S'`.sql

2>> 导入到目标库

/usr/bin/mysql -h 172.23.0.236 -u root -peHu2016 < 2021-11-22_10-33-38.sql

# 测试时有此类报错正常，插入或删除语句执行失败，是因为判断条件是所有字段，某条数据不对应导致就不会执行成功，不影响后续sql执行
Warning: Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 1: Duplicate entry '2' for key 'PRIMARY'

3> 自动同步

经过差异同步后，已确保可在10分钟内执行完导出导入命令：

实时监控同步日志tailf /binlog/scripts/sync.log，并进行以下操作：

1>> 加入定时任务

修改时间戳为10 mins

START_TIME变量指定为：date -d '10 mins ago' "+%F %H:%M:%S"
```
echo '*/1 * * * * /usr/bin/sh /binlog/sync.sh &>/dev/null' >> /var/spool/cron/root
```

监控拉取前10分钟的数据

[root@web2 ~]# tailf /binlog/scripts/sync.log
#######################################################
2021-11-24 13:18:01 从mysql-bin.000006导出数据成功！其中：
				INSERT：31条
				UPDATE：11条
				DELETE：30条
				  合计：72条
2021-11-24 13:18:01 从mysql-bin.000006导入数据成功！
文件来源：/binlog/2021-11-24_13-18-01.sql 有重复条目，可忽略~

2>> 修改时间戳为`2 mins`

监控日志导入成功后修改时间戳：START_TIME变量指定为：date -d '2 mins ago' "+%F %H:%M:%S"

PS：若在1分钟内日志显示导入完成，这可直接修改为1 mins

修改时间戳为2 mins
```
sed -i 's/10 mins/2 mins/g' sync.sh
```

监控拉取前2分钟的数据

#######################################################
2021-11-24 13:20:01 从mysql-bin.000006导出数据成功！其中：
				INSERT：3条
				UPDATE：2条
				DELETE：2条
				  合计：7条
2021-11-24 13:20:01 从mysql-bin.000006导入数据成功！
文件来源：/binlog/2021-11-24_13-20-01.sql 有重复条目，可忽略~

3>> 修改时间戳为`1 mins`

监控日志导入成功后修改时间戳：START_TIME变量指定为：date -d '1 mins ago' "+%F %H:%M:%S"

此时还会有【重复条目】的日志提示，但第二次运行时将不再有重复日志

修改时间戳为1 mins
```
sed -i 's/2 mins/1 mins/g' sync.sh
```

监控拉取前1分钟的数据

#######################################################
2021-11-24 13:21:01 从mysql-bin.000006导出数据成功！其中：
				INSERT：2条
				UPDATE：1条
				DELETE：1条
				  合计：4条
2021-11-24 13:21:01 从mysql-bin.000006导入数据成功！其中：
				INSERT：2条
				UPDATE：1条
				DELETE：1条
				  合计：4
文件来源：/binlog/2021-11-24_13-21-01.sql 有重复条目，可忽略~

再次监控拉取前1分钟的数据

此时已显示的还是刚才的日志，说明已经没有重复sql，也没有新的数据，若有新sql则会输出新的数据日志；

至此完成自动同步的所有步骤，且sync.log导入成功不应该有输出，除非有重复执行的sql

#######################################################
2021-11-24 13:21:01 从mysql-bin.000006导出数据成功！其中：
				INSERT：2条
				UPDATE：1条
				DELETE：1条
				  合计：4条
2021-11-24 13:21:01 从mysql-bin.000006导入数据成功！其中：
				INSERT：2条
				UPDATE：1条
				DELETE：1条
				  合计：4
文件来源：/binlog/2021-11-24_13-21-01.sql 有重复条目，可忽略~

6.场景2（数据紧急恢复）

场景了解参见：场景介绍

思路：真实场景下，生成的回滚sql经常会需要进一步筛选，查看里面是否有别的表的dml语句以及本表的非delete操作的语句，结合grep、编辑器等

PS1：使用 -B 选项生成回滚sql，检查回滚sql是否正确（注意：insert也会被解析成delete语句！！！）

使用 -B转换时，先不用-B选项导出sql，除delete语句外，其余语句一并删除，否则insert被解析为delete，执行完你就叽叽了…

PS2：为何只回滚转换delete语句？

insert、update是插入、更新的语句，没有被删除操作，所以无需回滚，且就算回滚也会相应的多插了一条；

delete是将记录删除了，所以只需要恢复被删除的记录即可

1）准备数据

1、插入数据

INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1');
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2');
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3');

2、查看数据

mysql> select * from binlog_test;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
|  2 | 插入数据2     |
|  3 | 插入数据3     |
+----+---------------+
3 rows in set (0.00 sec)

3、模拟删除

记录删除时间：2021-11-24 16:38:43

删除数据

DELETE FROM `peng`.`binlog_test` WHERE `id`=1;
DELETE FROM `peng`.`binlog_test` WHERE `id`=2;
DELETE FROM `peng`.`binlog_test` WHERE `id`=3;

查看数据，已被删除

mysql> select * from binlog_test;
Empty set (0.00 sec)

2）数据恢复

加-B选项，进行反解析sql，此选项主用于恢复被删除的数据

PS：

insert解析为delete

delete解析为insert

update不会改变

1、数据拉取

--start-datetime：删除数据的时间，若忘记可提前1分钟甚至更多，重复的sql不会执行且不影响后续sql执行

--stop-datetime：当前时间

拉取命令

删除时间为2021-11-24 16:38:43，拉取时间也指定为此时间戳，若拉过来不对，则指定的时间提前几秒

binlog2sql -h 172.23.0.234 -P 3306 -u root -peHu2016  -d peng -t binlog_test --start-file='mysql-bin.000006' --start-datetime='2021-11-24 16:38:43' --stop-datetime='2021-11-24 16:45:45' -B > recover.sql
[root@web2 ~]# !vim

拉取结果

已经获取到数据被删除的sql语句

此时结果已被反解析，三条insert被解析为delete，两条delete则被解析为insert

[root@web2 ~]# cat recover.sql 
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 29232 end 29416 time 2021-11-24 16:38:43
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 29017 end 29201 time 2021-11-24 16:38:43
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 28802 end 28986 time 2021-11-24 16:38:43

2、数据导入

注意！当数据量过多时，难免会含有insert语句，insert语句也会同时被反解析为delete语句，我们只需要恢复数据，故将反解析出的delete语句剔除掉即可，只保留被反解析的insert语句

1> 导入命令

INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 29232 end 29416 time 2021-11-24 16:38:43
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 29017 end 29201 time 2021-11-24 16:38:43
INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 28802 end 28986 time 2021-11-24 16:38:43

2> 执行过程

mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (3, '插入数据3'); #start 29232 end 29416 time 2021-11-24 16:38:43
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (2, '插入数据2'); #start 29017 end 29201 time 2021-11-24 16:38:43
NTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 28802 end 28986 time 2021-11-24 16:38:43Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `peng`.`binlog_test`(`id`, `name`) VALUES (1, '插入数据1'); #start 28802 end 28986 time 2021-11-24 16:38:43
Query OK, 1 row affected (0.00 sec)

3> 查询数据是否恢复

查询被删除的数据是否已恢复：已恢复

mysql> select * from binlog_test;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 插入数据1     |
|  2 | 插入数据2     |
|  3 | 插入数据3     |
+----+---------------+
3 rows in set (0.00 sec)

码农公寓

文章目录

MySQL-binlog2sql：非主从实时同步+恢复误删数据

1.引

1）介绍

2）功能

3）针对两种场景

2.先决条件

1）安装MySQL

2）修改MySQL配置

3.安装binlog2sql

1）解压

2）安装

3）添加别名

4.准备测试数据

1）建库建表

2）插入数据

3）查看数据

4）模拟误删除数据

5.场景1（数据实时同步）

1）案例

1、数据导出

1> 导出命令

2> 导出结果

2、数据导入

1> 导入命令

2> 导入结果

3> 查看数据一致性

2）自动同步

1、配置源数据库

1> 配置max_binlog_size大小

2> binlog更新频率计算

1>> 计算过程

2> 结论

2、注意事项

1> 重复执行的sql

2> 同步binlog时间间隔

3> 时间获取格式示例

3、钉钉告警

1> 创建钉钉机器人

2>钉钉告警脚本

3> 测试脚本

4、同步脚本

5、开始测试

1> 全量同步

2> 差异同步

1>> 源数据库导出

2>> 导入到目标库

3> 自动同步

1>> 加入定时任务

2>> 修改时间戳为2 mins

3>> 修改时间戳为1 mins

6.场景2（数据紧急恢复）

1）准备数据

1、插入数据

2、查看数据

3、模拟删除

2）数据恢复

1、数据拉取

2、数据导入

1> 导入命令

2> 执行过程

3> 查询数据是否恢复

相关文章

`MySQL-binlog2sql`：非主从实时同步+恢复误删数据

1）安装`MySQL`

2）修改`MySQL`配置

3.安装`binlog2sql`

1> 配置`max_binlog_size`大小

1> 重复执行的`sql`

2> 同步`binlog`时间间隔

2>> 修改时间戳为`2 mins`

3>> 修改时间戳为`1 mins`