通过binlog日志恢复数据库数据

1、什么是binlog日志

MySQL的binlog（二进制日志）是数据库核心日志之一，记录了所有写入性操作（如增删改）的逻辑日志，但不包含查询操作。使用binlog（二进制日志）来恢复数据库数据是一种常见的数据恢复方法，特别是在进行误操作或数据丢失的情况下。下面是如何使用binlog日志来恢复数据库数据的步骤

2、具体的操作步骤

2.1查看是否开启binlog日志

SHOW VARIABLES LIKE '%log_bin%'

若log_bin值为ON则表示已开启。若未开启需在配置文件（如my.cnf）中添加：

[mysqld]
log-bin=mysql-bin  # 指定binlog文件名及路径
server-id=1        # 主从复制需唯一
binlog_format=ROW  # 推荐ROW格式，记录行级操作
expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

重启MySQL服务使配置生效
2.2 查看二进制文件列表以及文件位置

SHOW BINARY LOGS  # 查看列表
SHOW VARIABLES LIKE '%log_bin_basename%' #查看文件位置

2.3使用binlog工具提取日志，查看事件位置

 SHOW BINLOG EVENTS IN 'mysql-bin.000751'

也可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容

mysqlbinlog --no-defaults -v --base64-output=decode-rows mysql-bin.000751

 # 查看bin‐log二进制文件（命令行方式，不用登录mysql）
 mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows /usr/local/mysql/logs/mysql-bin/mysql‐bi
nlog.000751

 # 查看bin‐log二进制文件（带查询条件）
mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows /usr/local/mysql/logs/mysql-bin/mysql‐bi
nlog.000751 start‐datetime="2025‐02‐21 00:00:00" stop‐datetime="2025‐03‐01 00:00:00" start‐
position="5000" stop‐position="20000"

2.4 恢复数据

 mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219 ‐‐stop‐position=701 ‐‐database=test /usr/local/mysql/logs/mysql-bin/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，
再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2023‐1‐27 23:32:24" ‐‐stop‐datetime="2023‐1‐27 23:34:
23" ‐‐database=test /usr/local/mysql/logs/mysql-bin/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v
test

3、注意事项
3.1日志刷新与隔离

恢复前执行 FLUSH LOGS 生成新日志文件，避免恢复过程中写入新操作干扰

若使用GTID（全局事务标识），需确保主从复制的GTID一致性

3.2存储引擎限制
binlog恢复适用于InnoDB等支持事务的引擎，MyISAM可能无法完全恢复

3.3日志格式影响
binlog_format=ROW记录行级变更细节，恢复更精确；STATEMENT记录SQL语句，可能导致主从不一致

3.4权限和路径问题
需确保mysqlbinlog工具和日志文件路径的访问权限

4、大量备份需做好全量备份
如果真用程序员删库到跑路，希望数据库做到权限隔离，还有就是数据库全量定期备份

4.1简单手工备份

mysqldump -u root -p123456 test_db > /backup/test_db_backup.sql

4.2脚本定期备份
创建如下sh脚本文件backupsql.sh

#!/bin/bash

# 数据库信息
DB_USER="root"
DB_PASSWORD="123456"
DB_NAME="test_db"

# 备份文件保存路径
BACKUP_DIR="/backup"

# 获取当前日期
DATE=$(date +%Y%m%d%H%M%S)

# 备份文件名
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql"

# 执行备份命令
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "数据库备份成功，备份文件为：$BACKUP_FILE"
else
    echo "数据库备份失败"
fi

给脚本赋权

chmod 775 backupsql.sh

使用cron实现定时任务

#编辑cron
crontab -e
#添加定时任务，每天凌晨两点定时执行
0 2 * * * sh /path/to/backupsql.sh