MySQL的二进制日志（Binlog）是数据库层面记录所有数据变更操作的日志文件，包含数据的增删改操作以及对应的执行时间、事务信息等内容，是数据恢复和主从复制的核心依赖组件。
二进制日志的基础配置
要使用二进制日志，首先需要确保MySQL服务已开启该功能，默认情况下部分版本的MySQL不会自动开启二进制日志，需要手动修改配置文件。
开启二进制日志的步骤
修改MySQL的配置文件my.cnf（Linux系统）或my.ini（Windows系统），添加以下配置项：
[mysqld] # 开启二进制日志，指定日志文件前缀 log_bin=mysql-bin # 设置服务器唯一ID，主从复制场景下每个节点ID不能重复 server_id=1 # 二进制日志格式，推荐设置为ROW，记录每行数据的变化，恢复更准确 binlog_format=ROW # 二进制日志过期时间，单位天，避免日志文件占用过多磁盘空间 expire_logs_days=7
修改完成后重启MySQL服务，执行以下SQL语句验证二进制日志是否开启：
-- 查看二进制日志状态 SHOW VARIABLES LIKE 'log_bin'; -- 查看当前二进制日志文件列表 SHOW BINARY LOGS;
基于二进制日志实现数据恢复
当数据库出现误删数据、误更新数据等故障时，可以通过二进制日志回放指定时间段的变更操作，恢复到故障前的状态。
数据恢复的核心步骤
- 第一步：找到故障发生的时间点，确定需要回放的二进制日志文件和时间范围
- 第二步：使用
mysqlbinlog工具解析二进制日志，生成可执行的SQL语句 - 第三步：将生成的SQL语句导入到数据库中执行，完成数据恢复
恢复示例
假设在2024年5月10日14:30执行了误删user表数据的操作，需要恢复到14:29的状态，操作如下：
首先查看二进制日志文件，找到包含该时间段的日志文件，比如mysql-bin.000003：
# 解析指定时间段的二进制日志，生成SQL文件 mysqlbinlog --start-datetime="2024-05-10 00:00:00" --stop-datetime="2024-05-10 14:29:59" /var/lib/mysql/mysql-bin.000003 > recover.sql
然后将生成的recover.sql文件导入到数据库中：
mysql -u root -p < recover.sql
如果是恢复单个表的删除操作，还可以在解析时通过--database参数指定数据库，减少无关日志的干扰：
mysqlbinlog --database=test_db --start-datetime="2024-05-10 14:00:00" --stop-datetime="2024-05-10 14:29:59" /var/lib/mysql/mysql-bin.000003 > recover.sql
基于二进制日志实现主从复制
主从复制是指将主库的变更操作同步到从库，实现数据冗余和读写分离，二进制日志是主从同步的数据来源。
主从复制的原理
- 主库将所有数据变更操作记录到二进制日志中
- 从库的IO线程连接到主库，读取主库的二进制日志，写入到从库的中继日志（Relay Log）中
- 从库的SQL线程读取中继日志的内容，在从库上执行对应的操作，完成数据同步
主从复制搭建步骤
1. 主库配置
除了之前开启二进制日志的配置外，还需要创建用于主从同步的账号：
-- 创建同步账号，允许从库连接 CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'repl_password'; -- 授予同步权限 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%'; -- 刷新权限 FLUSH PRIVILEGES; -- 查看主库当前的二进制日志状态，记录File和Position值 SHOW MASTER STATUS;
2. 从库配置
修改从库的my.cnf配置文件，设置唯一的server_id：
[mysqld] server_id=2 # 可选：开启从库自身的二进制日志，用于级联复制 log_bin=mysql-bin # 可选：设置从库只读，避免误写 read_only=1
重启从库服务后，执行以下命令配置主库连接信息：
CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.000003', -- 替换为之前主库SHOW MASTER STATUS查到的File值 MASTER_LOG_POS=156; -- 替换为之前主库SHOW MASTER STATUS查到的Position值
然后启动从库同步线程：
START SLAVE;
最后查看从库同步状态，确认Slave_IO_Running和Slave_SQL_Running两个值都为Yes：
SHOW SLAVE STATUSG
注意事项
- 二进制日志会占用磁盘空间，需要定期清理过期日志，避免磁盘满导致数据库故障
- 恢复数据前建议先备份当前数据库，避免恢复操作失败导致数据进一步丢失
- 主从复制场景下，主从库的MySQL版本建议保持一致，避免版本差异导致同步异常
- 如果二进制日志格式设置为STATEMENT，部分函数（如UUID、NOW）可能导致主从数据不一致，推荐优先使用ROW格式