binlog的使用手册

binlog都能干嘛

binlog是一个二进制日志文件记录数据变更，它可以

• 数据恢复
• 主从同步
• 数据回滚

核心价值就是记录所有数据库的数据变更，实现数据追踪与复制

怎么用binlog

首先我们要在my.cnf中配置

[mysqld]
[mysqld]
# 基本设置
server-id = 1  # 必须设置！单机可设为1
log_bin = /var/lib/mysql/mysql-bin  # binlog路径（确保目录存在）mysql-bin为前缀
binlog_format = ROW  # 推荐ROW模式，数据最安全
expire_logs_days = 7  # 自动清理7天前的日志
max_binlog_size = 100M  # 单个日志文件大小
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 高级优化（可选）
sync_binlog = 1  # 每次事务都刷盘（最安全）
binlog_row_image = full  # 记录完整行数据

关于binlog-format的三种方式：

格式	原理	场景
STATEMENT	记录SQL语句原文	节省空间但函数调用可能不一致
ROW	记录每行数据变更的前后的值	数据安全但体积更大
MIXED	STATEMENT和ROW的混合	平衡安全与空间

核心操作

-- 查看Binlog文件列表（账本目录）
SHOW BINARY LOGS; 

-- 查看当前写入的Binlog文件（正在用的账本）
SHOW MASTER STATUS; 

-- 手动清理Binlog（撕掉旧账本）
PURGE BINARY LOGS BEFORE '2025-10-01'; 

-- 用mysqlbinlog工具解析日志（翻账本查明细）
mysqlbinlog --start-datetime="2023-10-01" mysql-bin.000001

binlog需要配合定期全量备份，如果只有binlog没有某时刻的数据快照，就无法恢复
方法：每周全备，每天增备

怎么用binlog实现数据回滚

核心原理：反向重放binlog事件

1. 定位误操作时间：找到误删前最后一个安全时间点
2. 提取反向sql：将binlog中误操作之后的所有事件转化为补偿操作（DELETE转INSERT，UPDATE转旧值）
3. 执行回滚：在临时库中执行这些反向的SQL，验证后覆盖生产库

步骤（以下是很麻烦的办法，可直接跳过这里看下面的MyFlash恢复数据）

1. 创建临时库

# 新建临时库（避免污染生产环境）
mysql -uroot -p -e "CREATE DATABASE recovery_db;"

# 还原全量备份（假设备份文件为backup_20231001.sql）
mysql -uroot -p recovery_db < backup_20251001.sql

2. 定位误操作时间和binlog位置

-- 查看误操作时间（假设14:05误删了数据）
SELECT NOW(); -- 记录当前时间用于推算

-- 查看Binlog文件列表
SHOW BINARY LOGS;
/* 输出示例：
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 10485760  |
| mysql-bin.000002 | 1500      | <-- 误操作在此文件
+------------------+-----------+
*/

3. 解析binlog生成反向sql

# 解析误操作时间点之后的Binlog（14:05后）
mysqlbinlog \
  --start-datetime="2025-10-01 14:05:00" \
  --stop-datetime="2025-10-01 14:10:00" \  # 假设14:10发现错误
  mysql-bin.000002 \                        # 目标Binlog文件
  | sed -n "/### DELETE FROM `test`.`user`/,/COMMIT/p" \  # 提取误删事件
  | tac \                                   # 倒序排列（关键！）
  | sed 's/### DELETE FROM/### INSERT INTO/; 
          s/### WHERE/### SET/;
          s/###   @1=/###   VALUES (@1,/;   # 将DELETE转换为INSERT
         ' \
  > rollback.sql                            # 输出到反向SQL文件

生成的反向sql 示例：

### INSERT INTO `test`.`user`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张三' /* STRING(20) meta=65044 nullable=0 is_null=0 */
###   @3=1000 /* DECIMAL(10,2) meta=1026 nullable=1 is_null=0 */

4. 在临时库执行回滚

# 执行反向SQL（先严格测试！）
mysql -uroot -p recovery_db < rollback.sql

# 验证数据
mysql -uroot -p -e "SELECT * FROM recovery_db.user;"

5. 确认无误再同步到生产库

# 导出恢复的数据
mysqldump -uroot -p recovery_db user > recovered_user.sql

# 覆盖生产库（务必停写维护窗口！）
mysql -uroot -p production_db < recovered_user.sql

但是以上方法还是太麻烦了，有没有更简单的办法呢？
有的兄弟 有的！

通过MyFlash实现数据回滚

1. 安装MyFlash
   git clone https://github.com/Meituan-Dianping/MyFlash.git
2. 安装依赖包
   yum install -y gcc pkg-config glib2 libgnomeui-devel
3. 进入目录编译
   gcc -w pkg-config --cflags --libs glib-2.0 source/binlogParseGlib.c -o binary/flashback
4. 使用

[root@masterdb binary]# pwd
/root/MyFlash/binary
[root@masterdb binary]# ./flashback --help
 Usage:
   flashback [OPTION?]

Help Options:
   -h, --help                  Show help options

Application Options:
   --databaseNames             databaseName to apply. if multiple, seperate by comma(,)
   --tableNames                tableName to apply. if multiple, seperate by comma(,)
   --start-position            start position
   --stop-position             stop position
   --start-datetime            start time (format %Y-%m-%d %H:%M:%S)
   --stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
   --sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
   --maxSplitSize              max file size after split, the uint is M
   --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)  
   --outBinlogFileNameBase     output binlog file name base
   --logLevel                  log level, available option is debug,warning,error
   --include-gtids             gtids to process
   --exclude-gtids             gtids to skip

语法解析

--databaseNames : 需要闪回的数据库名称，如果有多个数据库，用逗号”，”隔开。
--tableNames ： 要闪回的表名称，如果有多个表，用逗号”,”隔开。
--start-position ：闪回的起始位置，如不指定，从文件开始处回滚。
--stop-position : 闪回的终止位置，如不指定，回滚到文件结尾。
--start-datetime ： 闪回的开始时间。
--stop-datetime : 闪回的终止时间。
--sqlTypes ： 指定需要回滚的sql类型，支持INSERT、UPDATE、DELETE，多个类型使用逗号”,”分开。
--maxSplitSize ： 对文件进行固定尺寸的切割，以防止单次应用binlog尺寸较大，对线上造成压力。
--binlogFileNames : 指定需要回滚的binlog文件，美团文档说目前只支持单个binlog文件，经测试已经支持多个binlog文件同时闪回。
--outBinlogFileNameBase ：指定输出的binlog文件前缀，如不指定，则默认为binlog_output_base.flashback。
logLevel ： 仅供开发者使用，默认级别为error级别。在生产环境中不要修改这个级别，否则输出过多。
include-gtids ： 指定需要回滚的gtid,支持gtid的单个和范围两种形式。
exclude-gtids ： 指定不需要回滚的gtid，用法同include-gtids。

可以把flashback加入到系统环境中。
sudo cp /root/MyFlash/binary/flashback /usr/local/bin/
sudo chmod +x /usr/local/bin/flashback

测试实战

1. 创建临时库

create database testdatabase;
use testdatabase;

2. 创建测试表

CREATE TABLE ship_log (
                          id INT PRIMARY KEY AUTO_INCREMENT,
                          ship_name VARCHAR(50),
                          action VARCHAR(50),
                          log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入初始数据
INSERT INTO ship_log (ship_name, action) VALUES
                                             ('黑珍珠号', '起航'),
                                             ('飞翔荷兰人', '幽灵化'),
                                             ('安妮女王复仇号', '诅咒激活');

-- 确认数据
SELECT * FROM ship_log;

3. 进行操作

-- 记录当前时间（重要！）
SELECT NOW(); -- 例如：2024-10-14 16:30:00

-- 执行"误操作"
DELETE FROM ship_log;  -- 全删了！

-- 查看空表（心痛！）
SELECT * FROM ship_log; -- 返回空

4. 查看当前binlog

show master status;  -- mysql-bin.000002

flush logs; -- 生成一个新的binlog文件，让后面的操作记录都在新的binlog文件中

5. 用flashback反向解析刚才的binlog 根据时间范围定位

flashback \
   --databaseNames="testdatabase" \
   --tableNames="ship_log" \
   --start-datetime="2025-10-14 16:30:00" \
   --stop-datetime="2025-10-14 16:40:00"  \
   --sqlTypes="DELETE" \
   --binlogFileNames=/var/lib/mysql/mysql-bin.000002 \
   --outBinlogFileNameBase=user.sql

执行之后可以在/root/MyFlash/binary目录下看到一个名字为user.sql.flashback的文件，我们用mysqlbinlog查看

mysqlbinlog -vv user.sql.flashback

看到如下字段

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#251014 16:38:04 server id 1  end_log_pos 123 CRC32 0x6f2768c1  Start: binlog v 4, server v 5.7.44-log created 251014 16:38:04 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7AvuaA8BAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADsC+5oEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcFoJ28=
'/*!*/;
# at 123
#251014 16:38:50 server id 1  end_log_pos 190 CRC32 0x94f81d34  Table_map: `testdatabase`.`ship_log` mapped to number 109
# at 190
#251014 16:38:50 server id 1  end_log_pos 333 CRC32 0xa22a3836  Write_rows: table id 109 flags: STMT_END_F

BINLOG '
GgzuaBMBAAAAQwAAAL4AAAAAAG0AAAAAAAEADHRlc3RkYXRhYmFzZQAIc2hpcF9sb2cABAMPDxEF
yADIAAAGNB34lA==
GgzuaB4BAAAAjwAAAE0BAAAAAG0AAAAAAAEAAgAE//ABAAAADOm7keePjeePoOWPtwbotbfoiKpo
7guW8AIAAAAP6aOe57+U6I235YWw5Lq6CeW5veeBteWMlmjuC5bwAwAAABXlronlpq7lpbPnjovl
pI3ku4flj7cM6K+F5ZKS5r+A5rS7aO4LljY4KqI=
'/*!*/;
### INSERT INTO `testdatabase`.`ship_log`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='黑珍珠号' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3='起航' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @4=1760430998 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### INSERT INTO `testdatabase`.`ship_log`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='飞翔荷兰人' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3='幽灵化' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @4=1760430998 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### INSERT INTO `testdatabase`.`ship_log`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='安妮女王复仇号' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3='诅咒激活' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @4=1760430998 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 333
#251014 16:38:50 server id 1  end_log_pos 364 CRC32 0x9619e6a7  Xid = 34
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

6. 恢复数据

mysqlbinlog user.sql.flashback | mysql -uroot -p

7. 查看数据

select * from ship_log;

我们会惊喜地发现，数据已经恢复了！

binlog实现主从同步

实现方式

主库将binlog时间发送给从库，从库的IO Thread接收并写入Relay Log， SQL Thread读取Relay Log并执行其中的SQL语句，从而保持数据一致

好处

• 高可用：主库挂了，可以快速的把从库提升为新的主库，减少停机时间。
• 读写分离：主库专心处理写数据，从库处理大量的读操作。
• 数据备份：可以在从库上进行备份操作，不影响主库性能。
• 数据分析：复杂的分析查询跑在从库上，不影响主库处理数据。

怎么做

主库配置：

[mysqld]
server-id = 1           # 唯一服务器ID，主从不能相同
log_bin = /var/log/mysql/mysql-bin.log # 启用Binlog并指定路径
binlog_format = ROW     # 推荐使用ROW格式（记录行变更），更安全精确
expire_logs_days = 7    # 自动清理7天前的Binlog

创建、复制用户（主库）

CREATE USER 'repl'@'%' IDENTIFIED BY '这里输入强密码!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

获取主库状态

SHOW MASTER STATUS;

记录下返回的File和Position

从库配置：

[mysqld]
server-id = 2           # 唯一服务器ID，不同于主库
relay_log = /var/lib/mysql/mysql-relay-bin # 指定Relay Log路径
read_only = ON          # 推荐设置从库为只读（防止误写）

配置从库链接主库（从库执行）

CHANGE MASTER TO
MASTER_HOST = '这里填写主库主机ip或者地址',
MASTER_USER = 'repl',
MASTER_PASSWORD = '这里填写强密码!',
MASTER_LOG_FILE = 'mysql-bin.000002', -- 替换为SHOW MASTER STATUS得到的File
MASTER_LOG_POS = 1878;                  -- 替换为SHOW MASTER STATUS得到的Position

启动复制（从库执行）

START SLAVE;

检查复制状态

SHOW SLAVE STATUS;

然后看以下几个值：

• Slave_IO_Running: Yes(IO Thread 正常运行)

• Slave_SQL_Running: Yes(SQL Thread 正常运行)

• Seconds_Behind_Master: 0或较小的数 (表示复制延迟很小)

注意事项

1. 主从同步是异步或者半同步的。默认异步模式下，主库提交事务后不会等从库确认就返回给客户端。如果主库在binlog发送到从库之前崩溃，已提交的数据
   可能就丢失了。半同步（rpl_semi_sync_master_enabled=ON）能缓解这个问题，但是如果从库确认后但是在执行之前挂了也会造成数据丢失。
   所以，需要强一致读的应用，读操作应该走主库。
   主库my.cnf里面打开半同步功能

plugin_load_add = "semisync_master.so"  # Linux 加载插件
# 或者 Windows 下通常是：
# plugin_load_add = "semisync_master.dll"
rpl_semi_sync_master_enabled = ON       # 启用主库半同步功能
rpl_semi_sync_master_timeout = 1000     # 设置超时时间（毫秒），重要！

从库my.cnf里面打开半同步功能

plugin_load_add = "semisync_slave.so"   # 或 .dll
rpl_semi_sync_slave_enabled = ON

然后 sudo systemctl restart mysql 重启mysql
2. 从库不是越多越好，每个从库都会在主库上创建一个链接（IO Thread），大量的从库会给主库带来网络和磁盘的IO压力。所以可以考虑Slave->Slave这样的方法
3. 主从切换不是只改ip就行。一定要严格按照流程走，直接切换可能导致新主库（原从库）丢失最后一部分数据；应用可能还在王旧主库写数据，导致冲突或数据丢失