前言

数据是核心，没有数据，都是空谈。只要稍有不慎，就会造成数据损失。数据误删除，误更新等，会导致数据的不一致。
使用MySQL的Binlog日志可以极大可能地恢复数据
因此测试下更新 删除是否可以恢复

什么是binlog

简介

  • binlog是一个二进制文件
  • 记录了数据更新或者删除的所有操作
  • 主从模式使用的就是binlog同步数据

工作模式

  1. Row level ：按行记录，记录每一行数据的更新操作，再同步到slave

    优点：数据记录完整性，可从日志中找到该数据行的一系列操作
    缺点：数据量太大，1:n的数据量

  2. Statement level：记录更新SQL,同步SQL到salve,slave再重放一遍SQL,达到同步的效果，但是由于部分函数不支持，主从同步不建议使用

    优点：数据量小，减少IO，提高性能
    缺点：主从数据不一致问题

  3. Mixed： 混合模式，两种模式结合

    优点：结合两种模式的优点
    缺点：结构复杂，解析复杂

环境

  • MySQL 8.0.22
  • Docker version 19.03.13, build 4484c46d9d
  • Centos 8.0
  • MySQL安装在Docker容器中
  • Navicat客户端

初始化数据

创建数据库

CREATE DATABASE test;

新建测试表并插入数据

-- 创建表
CREATE TABLE `people` (
  `age` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `id` int NOT NULL,
  `insertTime` datetime DEFAULT NULL,
  `updateTime` datetime DEFAULT NULL,
  `insertTime_format` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `updateTime_format` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `ipv4_address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 插入数据
INSERT INTO `test`.`people`(`age`, `name`, `id`, `insertTime`, `updateTime`, `insertTime_format`, `updateTime_format`, `ipv4_address`) VALUES (11, 'test', 1, '2020-11-26 17:31:22', '2020-11-27 17:31:31', '', NULL, NULL);

查看binlog开关

8.0默认打开，开关打开才能获取到数据更新的二进制文件，才能做恢复数据操作，若没有开启，则没法恢复数据

show variables like '%log_bin%';

binlog

log_bin为on的状态，则为开启状态，若没有开启则需要开启

开启binlog

在MySQL安装目录找到my.cnf,添加以下内容,保存并重启MySQL,再查看开关，开启即可

[mysqld]
# binlog 配置
expire-logs-days = 14
max-binlog-size = 500M
server-id = 1
log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
default-time_zone = '+8:00'

删除数据

delete from people where id =1;

恢复删除数据

准备工作

查看binlog

查看最后修改的binlog文件

show master status;

binlog

导出binlog

  1. 因为使用了docker部署mysql，所以操作mysql时要切换到mysql容器内操作

    docker exec -it mysql bash

  2. 使用mysqlbinlog（mysql自带工具）导出binlog文件

    mysqlbinlog --no-defaults --base64-output=decode-rows --start-datetime="2020-11-26 00:00:00" --stop-datetime="2020-11-26 23:59:59" -v -v binlog.000018| sed -n '/### DELETE FROM `test`.`people`/,/COMMIT/p' > delete.txt

  3. 查看导出文件
    binlog

    编写脚本

    把上面的文件导出来本地，执行下面脚本程序，即可反解析出insert语句，重新执行insert语句即可恢复对应的数据
    小弟很菜，脚本都是随便写的

    Java版本

    public static void main(String[] args) throws IOException {
         File file=new File("delete.txt");
         String delteData = FileUtils.readFileToString(file, Charset.defaultCharset());
         String[] split = delteData.split("# at");
         String[] split1 = split[0].split("### DELETE FROM `test`.`people`\n" +
                 "### WHERE");
         for (String data : split1) {
             //过滤掉空串
             if(StringUtils.isBlank(data)){
                 continue;
             }
             String insertSql="INSERT INTO `test`.`people`(`age`, `id`, `name`, `insertTime`, `updateTime`, " +
                     "`insertTime_format`, `updateTime_format`, `ipv4_address`) VALUES (";
             String[] split2 = data.split("###");
             String betweenSql="";
             for (String rows : split2) {
                 if(rows.contains("INT")||rows.contains("LONGINT")){
                   String dataColumn=  rows.split("\\/\\*")[0].trim().split("=")[1];
                   if(StringUtils.isNotBlank(betweenSql)){
                       betweenSql+=","+dataColumn;
                   }else{
                       betweenSql+=dataColumn;
                   }
                 }
                 if(rows.contains("VARSTRING")||rows.contains("DATETIME")){
                     String dataColumn=  rows.split("\\/\\*")[0].trim().split("=")[1];
                     if(StringUtils.isNotBlank(betweenSql)){
                         betweenSql+=",";
                     }
                     betweenSql+=dataColumn;
                 }
             }
             insertSql+=betweenSql+");";
             System.out.println(insertSql);
         }
     }

Python版本

import re


def list_split(items, n):
    return [items[i:i + n] for i in range(0, len(items), n)]


with  open('delete.txt') as f:
    content = f.read()
    f.close()
r = re.findall('###   (.*)\\/\\*', content)
list2 = list_split(r, 8)
for data in list2:
    sql = "INSERT INTO `test`.`people`(`age`, `id`, `name`, `insertTime`, `updateTime`, `insertTime_format`, " \
          "`updateTime_format`, `ipv4_address`) VALUES ( "
    for temp in data:
        b = temp.split('=')
        if '1' in b[0]:
            sql += b[1].strip()
        else:
            sql += ',' + b[1].strip()
    sql += ');'
    print(sql)

更新数据

UPDATE `test`.`people` SET `age` = 11, `name` = 'testbinlog', `insertTime` = '2020-11-26 17:31:22', `updateTime` = '2020-11-27 17:31:31', `insertTime_format` = '', `updateTime_format` = NULL, `ipv4_address` = NULL WHERE `id` = 1;

恢复更新数据

准备工作

查看binlog

查看最后修改的binlog文件

show master status;

binlog

导出binlog

  1. 因为使用了docker部署mysql，所以操作mysql时要切换到mysql容器内操作

    docker exec -it mysql bash

  2. 使用mysqlbinlog（mysql自带工具）导出binlog文件

    mysqlbinlog --no-defaults --base64-output=decode-rows --start-datetime="2020-11-26 00:00:00" --stop-datetime="2020-11-26 23:59:59" -v -v binlog.000018| sed -n '/### UPDATE `test`.`people`/,/COMMIT/p' > update.txt

  3. 查看导出文件
    binlog

编写脚本

Java版本

跟删除的脚本类似的，修改具体的update语句，组装成完整的update语句即可
小弟很菜，脚本都是随便写的

  public static void main(String[] args) throws IOException {
    File file=new File("update.txt");
    String updateData = FileUtils.readFileToString(file, Charset.defaultCharset());
    String[] split = updateData.split("# at");
    String[] split1 = split[0].split("### UPDATE `test`.`people`\n" +
            "### WHERE");
    for (String data : split1) {
        //过滤掉空串
        if(StringUtils.isBlank(data)){
            continue;
        }
        String updateSql="UPDATE `test`.`people` SET ";
        String[] split2 = data.split("### SET");
        String columuData=split2[0];
        String[] split3 = columuData.split("###   ");
        String idSql=" WHERE id=";
        for (String data2 : split3) {
            if(StringUtils.isNotBlank(data2)){
               String data3= data2.split("\\/\\*")[0];
                String index=data3.split("=")[0];
                String data4=data3.split("=")[1];
               if(index.contains("@1")){
                   updateSql+="age="+data4.trim();
               }
                if(index.contains("@2")){
                    idSql+=data4.trim();
                }
                if(index.contains("@3")){
                    updateSql+=",insertTime="+data4.trim();
                }
                if(index.contains("@4")){
                    updateSql+=",updateTime="+data4.trim();
                }
                if(index.contains("@5")){
                    updateSql+=",insertTime_format="+data4.trim();
                }
                if(index.contains("@6")){
                    updateSql+=",updateTime_format="+data4.trim();
                }
                if(index.contains("@7")){
                    updateSql+=",ipv4_address="+data4.trim();
                }
                if(index.contains("@8")){
                    updateSql+=",ipv4_address="+data4.trim();
                }

            }

        }
        updateSql+=idSql+";";
        System.out.println(updateSql);
    }
}

Python版本

跟删除的脚本类似，可自行修改即可

进阶

binlog2sql

一个常用的数据恢复工具，比自己写代码强多了，功能完整性也是不错的
解析出你要的SQL，根据自己的需求（有多种选项）

binlog2sql安装

python来运行，需要安装python相关环境
安装在与MySQL相同的一个实例上

安装python

安装python有多种方式，可参考其他方法。

yum -y install yum-utils
yum-builddep python
curl -O https://www.python.org/ftp/python/3.8.0/Python-3.8.0.tgz
tar xf Python-3.8.0.tgz
cd Python-3.8.0
./configure
make
make install
vi /etc/profile.d/python.sh         #编辑用户自定义配置，输入alias参数
alias python='/usr/local/bin/python3.8'　　#这里写你的python路径
source /etc/profile.d/python.sh     #重启会话使配置生效

安装pip

yum -y install epel-release
yum -y install python-pip
pip install --upgrade pip

安装PyMySQL

pip install PyMySQL
pip install --upgrade PyMySQL

安装git

yum install -y git

下载binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

修改配置

binlog2sql 使用的是从库的方式拉取binlog，所以要配置MySQL服务的配置，my.cnf 添加以下内容

[mysqld]
# binlog 配置
expire-logs-days = 14
max-binlog-size = 500M
server-id = 128
log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
default-time_zone = '+8:00'
binlog_format = row
binlog_row_image = full             # 默认值，可以不显式设置

binlog2sql使用

查询出被删除的数据的时间点，添加时间的筛选会大大减少数据的遍历，提高效率

cd binlog2sql                    #切入到binlog2sql.py文件所在的目录
python binlog2sql.py  --flashback  -h 127.0.0.1 -P3306 -udata_rec -p'password' -d test  -t people  --start-file='mysql-bin.000001' --start-datetime='2020-11-27 13:50:00' --stop-datetime='2020-11-27 14:30:00'>rec.sql
  • -flashback: 生成闪回SQL，不加这参数，只会解析执行过的sql
  • -h : mysql服务器的主机IP
  • -P : mysql 服务器的端口号
  • -u : mysql mysql数据库的用户名，可自己创建自己用户，默认是使用root，如果是自己创建用户，需要赋予对应的权限
  • -p :用户设置的密码
  • -d :需要解析的数据库名称
  • -t :需要解析的数据表名称
  • --start-file :mysql服务器上binlog的名字，需要使用show binary logs;查看，需要定位数据的binlog文件
  • --start-datetime :需要解析文件的操作开始时间
  • --stop-datetime: 需要解析文件的操作结束时间
  • rec.sql: 解析完的文件名称

结果

  • 不带闪回参数

    binlog

  • 带闪回参数

    binlog

官方文档

binlog2sql

问题

  • Centos8 安装出现问题，目测是python和pip的匹配的问题

  • 解决方案:重装了系统，使用了Centos7,相关依赖安装正常

  • 安装了mysql8.0，操作步骤以及依赖的东西似乎与官方的不一样，导致出现奇怪的问题。

  • 解决方案：重装mysql5.7

  • yum卸载后，重装yum繁琐,最终还是没有安装上（centos8系统下）

  • 一般情况下别卸载yum