记录第一次恢复未备份数据库数据

YuXuanTM / 2024-10-19 / 原文

场景: 数据库未做快照, 通过BINLOG文件恢复错误操作

mysql:8.0.27, 数据库需提前开启BINLOG
-- 查看是否开启BINLOG: 
SHOW VARIABLES LIKE '%log_bin%';

1.查询最新的BINLOG, 获取BINLOG日志名称

SHOW MASTER STATUS;

2.查看BINLOG日志

SHOW BINLOG EVENTS IN 'binlog.000003';

我查的教程说在这里可以找到误操作的SQL语句, 但不知道是版本问题还是为什么, 我这里的日志没有任何SQL语句

3.通过mysqlbinlog解析二进制文件 'binlog.000003'

首先, 我找到指定的表通过update_time确定了误操作的时间

然后, 通过命令

mysqlbinlog --start-datetime="2024-10-15 11:10:00" --stop-datetime="2024-10-15 11:20:00" /var/lib/mysql/binlog.000003 > /var/lib/mysql/binlog_restore.sql

输出了二进制解析文件

再通过检索, 我拿到了误操作首pos: 44071681; 尾pos: 44347378
最后, 通过命令输出了只包含误操作的文件(这里其实多余的, 直接文本处理上一步的文件也可以)

mysqlbinlog --start-position=44071681 --stop-position=44347378 --database=zjtz_public --verbose /var/lib/mysql/binlog.000003 > /var/lib/mysql/operations.sql -u root -p

我这里加上--verbose 命令才将执行的sql语句输出.

4.拿到所有误操作的SQL后, 就是无聊的数据补录环节了, 将脏数据清除掉, 再插入旧数据.

5.防止再次出现这个问题, 增加定时备份脚本

备份脚本

#!/bin/bash

# 设置Docker容器名
DOCKER_CONTAINER_NAME="zjtz_mysql"

# 设置数据库信息
DATABASE_NAME="zjtz_public"

# 设置备份路径
BACKUP_PATH="/home/mysql/database-back/"

# 获取当前时间
CURRENT_TIME=$(date +"%Y%m%d_%H%M%S")

# 备份文件名
BACKUP_FILE="${DATABASE_NAME}_backup_${CURRENT_TIME}.sql"

# 在Docker容器中执行备份并压缩
docker exec $DOCKER_CONTAINER_NAME mysqldump --defaults-file=/var/lib/mysql/.my.cnf $DATABASE_NAME | gzip > "${BACKUP_PATH}${BACKUP_FILE}.gz"

# 删除7天前的备份文件
find "$BACKUP_PATH" -type f -name "*.gz" -mtime +7 -exec rm {} \;

# 输出备份结果
echo "备份已完成: ${BACKUP_FILE}.gz"

# 设置crontab任务，每8小时运行一次
(crontab -l 2>/dev/null; echo "0 */8 * * * /bin/bash /home/mysql/database-back/mysql_backup.sh >> /home/mysql/database-back/back.up 2>&1") | crontab -