mysql的二进制日志（binlog）会按顺序记录数据库执行的所有数据修改类操作，包括增删改语句以及表结构变更操作，是mysql实现增量恢复、主从复制的核心组件。当数据库出现误删数据、误操作等故障时，我们可以通过备份的二进制日志还原故障前的操作，最大程度降低数据损失。
二进制日志的基础配置
首先需要确认mysql已经开启二进制日志功能，默认情况下部分mysql安装包不会自动开启该配置。可以登录mysql执行以下命令查看状态：
-- 查看二进制日志是否开启 SHOW VARIABLES LIKE 'log_bin'; -- 查看当前二进制日志文件列表 SHOW BINARY LOGS;
如果log_bin的值为OFF，需要修改mysql的配置文件my.cnf（Linux系统）或my.ini（Windows系统），添加以下配置后重启mysql服务：
[mysqld] # 开启二进制日志，指定日志文件前缀 log_bin=mysql-bin # 二进制日志格式，推荐设置为ROW，记录更详细的行变更信息 binlog_format=ROW # 单个二进制日志文件大小，默认1G max_binlog_size=1073741824
手动备份二进制日志
手动备份适合临时需要备份日志的场景，操作步骤如下：
步骤1：刷新日志生成新文件
执行FLUSH BINARY LOGS命令，让mysql关闭当前正在写入的二进制日志文件，生成新的日志文件，这样已经写满或者已经关闭的日志文件就不会再被修改，方便备份：
FLUSH BINARY LOGS;
步骤2：复制日志文件到备份目录
二进制日志文件默认存储在mysql的数据目录下，文件名称格式为mysql-bin.000001、mysql-bin.000002等。我们可以直接将这些文件复制到指定的备份目录，比如备份到/data/mysql_binlog_backup/目录下：
# 查看当前二进制日志文件列表 ls -l /var/lib/mysql/mysql-bin.* # 复制所有二进制日志文件到备份目录 cp /var/lib/mysql/mysql-bin.* /data/mysql_binlog_backup/
自动备份二进制日志
手动备份效率较低，实际生产环境中建议配置自动备份策略，常用的方式有两种：
方式1：使用mysqlbinlog远程备份
mysql自带的mysqlbinlog工具支持将二进制日志实时备份到远程服务器，执行以下命令即可实现持续备份：
# 实时备份二进制日志到远程服务器的指定目录 mysqlbinlog --read-from-remote-server --host=127.0.0.1 --user=backup_user --password=backup_pass --raw --result-file=/data/mysql_binlog_backup/ mysql-bin.000001
上述命令中--raw表示输出原始二进制日志内容，--result-file指定备份文件的存储路径前缀，工具会自动持续读取新的二进制日志并备份。
方式2：使用定时任务备份
可以编写脚本结合crontab定时任务定期备份二进制日志，以下是一个简单的备份脚本示例：
#!/bin/bash
# 定义备份目录
BACKUP_DIR="/data/mysql_binlog_backup"
# 定义mysql数据目录
DATA_DIR="/var/lib/mysql"
# 获取当前所有二进制日志文件
BINLOG_FILES=$(ls $DATA_DIR/mysql-bin.* 2>/dev/null)
# 遍历文件，复制不存在的文件到备份目录
for file in $BINLOG_FILES; do
filename=$(basename $file)
if [ ! -f "$BACKUP_DIR/$filename" ]; then
cp $file $BACKUP_DIR/
echo "$(date) 备份文件 $filename 完成" >> /var/log/binlog_backup.log
fi
done
# 刷新日志，避免重复备份正在写入的文件
mysql -u root -p'your_password' -e "FLUSH BINARY LOGS;"
给脚本添加执行权限后，配置crontab每小时执行一次：
chmod +x /opt/binlog_backup.sh # 编辑crontab任务 crontab -e # 添加以下内容，每小时0分执行备份脚本 0 * * * * /opt/binlog_backup.sh
基于备份的二进制日志做数据恢复
当数据库出现故障需要恢复时，需要结合全量备份和二进制日志共同完成，步骤如下：
步骤1：恢复全量备份
首先恢复最近一次的全量备份数据到数据库中，假设全量备份的截止时间对应的二进制日志位置是mysql-bin.000003文件的1234位置。
步骤2：应用二进制日志恢复增量数据
使用mysqlbinlog工具解析备份的二进制日志，提取全量备份之后的操作，然后应用到数据库中：
# 解析从指定日志位置开始的所有操作，导出为sql文件 mysqlbinlog --start-position=1234 /data/mysql_binlog_backup/mysql-bin.000003 /data/mysql_binlog_backup/mysql-bin.000004 | mysql -u root -p'your_password'
如果需要恢复到指定的时间点，比如恢复到2024年3月10日10点30分，可以使用--stop-datetime参数：
mysqlbinlog --start-position=1234 --stop-datetime="2024-03-10 10:30:00" /data/mysql_binlog_backup/mysql-bin.000003 | mysql -u root -p'your_password'
备份注意事项
- 二进制日志备份需要和全量备份配合，单独备份二进制日志无法完成完整的数据恢复。
- 备份目录需要设置合适的权限，避免日志文件被篡改或者误删。
- 定期清理过期的二进制日志和备份文件，避免占用过多磁盘空间，清理前需要确认对应的数据已经不需要恢复。
- 生产环境建议将二进制日志备份到独立的存储服务器，避免和数据库服务器同时出现故障导致备份丢失。