前言

数据是核心,没有数据,都是空谈。只要稍有不慎,就会造成数据损失。数据误删除,误更新等,会导致数据的不一致。
使用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_binon的状态,则为开启状态,若没有开启则需要开启

开启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