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

2021/11/24 19:40:14

本文主要是介绍MySQL-binlog2sql:非主从实时同步+恢复误删数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

文章目录

  • `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.引

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操作,回滚SQLdelete。对于update操作,回滚sql应该交换SETWHERE的值。

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)安装

若报错,则安装python3yum 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:若误删除数据时间记不清楚,恢复时可在此基础上往后延迟几秒或几分,若数据量比较多,时间间隔越小,排查起来也越快!

  • 删除选中的两条记录

    image-20211119153210234

  • 删除后,发现数据只剩一条

    [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文件名,若数据量极为频繁,则需要更换

经测试,500Mbinlog存储,一般是不会被很快填满的,只要一天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

自定义关键词

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日志,并查看目标数据库的数据与正服是否对应

- 数据是否对应:查看getsql文件,过滤某一条语句,根据条件去目标数据库进行查询,数据存在则同步成功!

开始演示>>>

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:非主从实时同步+恢复误删数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程