完整备份（全量）         +          Binlog 归档
    ↓                                 ↓
恢复备份到某个时间点       →   重放 Binlog 到目标时间点

[mysqld]
log_bin = mysql-bin              # 开启 Binlog
binlog_format = ROW              # 推荐 ROW 格式（PITR 更精确）
binlog_row_image = FULL          # 记录完整的行镜像
expire_logs_days = 7             # 保留 7 天（调整到满足最长恢复需求）
# MySQL 8.0+
binlog_expire_logs_seconds = 604800  # 7 天

# 查看当前 Binlog 文件
mysql -e "SHOW BINARY LOGS;"

# 刷出新的 Binlog（通常在做全量备份前执行）
mysql -e "FLUSH LOGS;"

# 将 Binlog 定期归档到安全位置
# 如：同步到对象存储、远程备份服务器

# 建议全量备份周期
# 大型数据库：每周 1 次全量 + 每天增量
# 中型数据库：每天全量
# 重要业务：每天全量 + 每 6 小时增量

# 找到最近一次正常的全量备份
ls -la /backup/mysql/full/

# 使用 xtrabackup 恢复
xtrabackup --prepare --target-dir=/backup/mysql/full/20250614/
xtrabackup --copy-back --target-dir=/backup/mysql/full/20250614/
chown -R mysql:mysql /var/lib/mysql/

# 或者使用 mysqldump 备份的恢复
mysql -u root -p < /backup/mysql/mydb_20250614.sql

# 方法一：使用 mysqlbinlog 查看
mysqlbinlog --start-datetime="2025-06-15 14:59:59" \
            --stop-datetime="2025-06-15 15:00:01" \
            /var/lib/mysql/mysql-bin.000123 | grep -i "DROP TABLE"

# 方法二：查看备份记录的 Binlog 位置
# xtrabackup 备份目录中的 xtrabackup_binlog_info 文件记录了位置
cat /backup/mysql/full/20250614/xtrabackup_binlog_info
# 输出示例：mysql-bin.000120 438945618

# 从全量备份的位置开始，重放到目标时间点
mysqlbinlog \
  --start-position=438945618 \
  --stop-datetime="2025-06-15 14:59:59" \
  /var/lib/mysql/mysql-bin.000120 \
  /var/lib/mysql/mysql-bin.000121 \
  /var/lib/mysql/mysql-bin.000122 \
  /var/lib/mysql/mysql-bin.000123 \
  | mysql -u root -p

# 将所有需要的 Binlog 合并为一个 SQL 文件
mysqlbinlog \
  --start-datetime="2025-06-14 03:00:00" \
  --stop-datetime="2025-06-15 14:59:59" \
  /var/lib/mysql/mysql-bin.00012* \
  > /tmp/pitr_recovery.sql

# 检查生成的 SQL 中是否有敏感操作（如 DROP）
grep -i "DROP TABLE" /tmp/pitr_recovery.sql

# 确认无误后执行恢复
mysql -u root -p < /tmp/pitr_recovery.sql

# 步骤
# 1. 恢复全量备份到临时实例
# 2. 从 Binlog 中提取该表的操作
# 3. 仅导出该表数据
# 4. 恢复到主库

# 提取特定表的 Binlog
mysqlbinlog \
  --database=mydb \
  --start-datetime="2025-06-14 03:00:00" \
  --stop-datetime="2025-06-15 14:59:59" \
  /var/lib/mysql/mysql-bin.00012* | \
  grep -A 1000 "DELETE FROM users" | head -1000 > drop_recovery.sql

# 1. 准备新实例（防止影响生产环境）
docker run -d --name mysql_recovery -e MYSQL_ROOT_PASSWORD=xxx mysql:8.0

# 2. 恢复全量备份到新实例
# 3. 重放 Binlog
# 4. 验证数据正确性
# 5. 切换业务到新实例

# 精确跳过某个 DELETE 语句
# 找到 DELETE 的 Binlog 位置
mysqlbinlog mysql-bin.000123 | grep -n "DELETE FROM users"

# 分两段重放：在 DELETE 前停止，然后跳过 DELETE 继续
mysqlbinlog --stop-position=12345678 binlog.000123 | mysql -u root -p
mysqlbinlog --start-position=12345999 binlog.000123 | mysql -u root -p

恢复时间 ≈ 全量恢复时间 + Binlog 重放时间

Binlog 重放时间 ≈ Binlog 大小 / 重放速度
重放速度通常为：原写入速度的 50-70%（因为要执行 INSERT/UPDATE）

示例：
全量备份恢复：30 分钟（数据库 100GB）
Binlog 重放（6 小时的数据）：1-2 小时
总计：1.5-2.5 小时

# 备份整个实例
mysqldump -u root -p --all-databases > full_backup.sql

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份单张表
mysqldump -u root -p mydb users > users_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multi_db.sql

# 结构与数据分离
--no-data         # 只导出表结构，不导出数据
--no-create-info  # 只导出数据，不导出表结构

# 一致性与锁定
--single-transaction   # InnoDB 一致性快照（不锁表）
--lock-tables          # MyISAM 使用（锁所有表）
--lock-all-tables      # 全局锁所有数据库的表

# 内容控制
--where "id > 1000"     # 按条件导出数据
--ignore-table mydb.logs # 忽略指定表

# 对象导出
--routines    # 导出存储过程和函数
--triggers    # 导出触发器
--events      # 导出事件

# 压缩
mysqldump -u root -p mydb | gzip > mydb.sql.gz

# 指定字符集
--default-character-set=utf8mb4

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --quick \
  --flush-logs \
  --master-data=2 \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

mysqldump -u root -p \
  --lock-all-tables \
  --routines \
  --triggers \
  mydb > mydb_backup.sql

# 正确使用（InnoDB）
mysqldump --single-transaction --all-databases > backup.sql

# 错误使用（不能同时用 --single-transaction 和 --lock-tables）
mysqldump --single-transaction --lock-tables ...  # 冲突！

# 备份超大表时可能报错
# max_allowed_packet 限制单条 SQL 大小

# 查看当前限制
mysql -e "SHOW VARIABLES LIKE 'max_allowed_packet'"

# 备份时增加包大小限制
mysqldump --max_allowed_packet=1G mydb > backup.sql

# --quick 参数很重要
# 不指定 --quick 时，mysqldump 会将整个结果集加载到内存，大表可能 OOM

# 指定 --quick 后逐行读取，内存占用小
mysqldump --quick --single-transaction mydb > backup.sql

# 基本恢复
mysql -u root -p mydb < backup.sql

# 恢复大文件时要分段执行（使用 pv 查看进度）
pv backup.sql | mysql -u root -p mydb

# 禁用外键检查加速导入
mysql -u root -p mydb -e "SET FOREIGN_KEY_CHECKS=0; SOURCE backup.sql; SET FOREIGN_KEY_CHECKS=1;"

# 禁用自动提交（加快批量插入）
mysql -u root -p mydb -e "SET autocommit=0; SOURCE backup.sql; COMMIT;"

# 备份时就校验
mysqldump --single-transaction --disable-keys mydb > backup.sql

# 恢复后检查表
mysqlcheck -u root -p --check mydb

# 使用 pt-table-checksum 校验（推荐）
pt-table-checksum h=localhost,u=root,p=password,d=mydb

-- 事务 A 执行
UPDATE users SET name = 'new_name' WHERE id = 1;
-- Undo Log 记录：id=1 的 name 原来是 'old_name'
-- 如果事务回滚，可以通过 Undo Log 恢复
-- 如果其他事务并发读取，通过 Undo Log 构建旧版本数据

事务开始 → 生成 Undo Log → 写入 Undo 表空间
事务提交 → Undo Log 不再被任何事务需要
         → 等待 Purge 线程清理
Purge 完成 → Undo Log 空间被回收

# 开启独立 Undo 表空间
innodb_undo_tablespaces = 2   # 使用 2 个 Undo 表空间文件（最大值 126）

# 默认位置
/var/lib/mysql/undo_001
/var/lib/mysql/undo_002

innodb_undo_tablespaces = 2        # 默认 2 个
innodb_max_undo_log_size = 1G     # 每个 Undo 表空间最大 1GB（默认）
innodb_purge_rseg_truncate_frequency = 128  # Purge 检查 TRUNCATE 的频率

-- 一个事务修改了 1000 万行
BEGIN;
UPDATE millions_of_rows SET status = 1;
-- 产生大量 Undo Log
COMMIT;
-- 即使提交了，Undo 也不会立即清理

-- 事务一直不提交
BEGIN;
UPDATE users SET name = 'test' WHERE id = 1;
-- 事务保持 1 小时不提交
-- 期间生成的所有 Undo Log 都不能清理

-- 会话 A：长时间运行的查询
SELECT * FROM users WHERE ...;  -- 可能扫描 30 分钟

-- 会话 B：同时更新表
UPDATE users SET ...;           -- 产生的 Undo 在 A 查询结束前不能释放

-- 在 REPEATABLE READ 级别下
-- 事务开始后第一个 SELECT 创建 Read View
-- 之后的所有旧版本数据都必须保留到事务结束
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM ... ;  -- 创建 Read View
-- 后续对该表的更新产生的 Undo 都不能清理

-- 查看 Undo 表空间大小（8.0）
SELECT TABLESPACE_NAME, FILE_NAME, 
       ROUND(SUM(FILE_SIZE) / 1024 / 1024, 2) AS size_mb
FROM information_schema.INNODB_TABLESPACES
WHERE TABLESPACE_NAME LIKE '%undo%';

-- 查看历史列表长度（pending purge 的记录数）
SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';
-- 这个值越大，说明需要 PURGE 的 Undo 越多

-- 查看当前活跃事务
SELECT * FROM information_schema.INNODB_TRX\G

-- 确认配置
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';  -- ON
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';  -- 1048576000 (1GB)

-- 1. 查看 Undo 表空间状态
SELECT * FROM information_schema.INNODB_UNDO_TABLESPACES;

-- 2. 停用并截断（如果支持）
-- 5.7 中需确保 purge 线程正常工作且无长事务阻塞

-- 找出运行时间最长的事务
SELECT trx_id, trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_running,
       trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 如果发现需要杀掉长时间运行的事务
KILL [trx_mysql_thread_id];

-- 不要这样（一个事务修改太多行）
BEGIN;
DELETE FROM logs WHERE created_at < '2023-01-01';  -- 一次删 1 亿行？
COMMIT;

-- 改为分批
BEGIN;
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
COMMIT;
-- 重复直到完成

-- 查看 Purge 线程是否在正常推进
SHOW ENGINE INNODB STATUS\G

TRANSACTIONS
------------
Trx id counter 1567890
Purge done for trx's n:o < 1567800
undo n:o < 0 state: running
History list length 456  -- 这个值越小越好

# 更多的 Undo 表空间 = 更多的空间可以并行 TRUNCATE
innodb_undo_tablespaces = 4

# 在 my.cnf 中添加，跳过 Undo 验证
innodb_undo_log_truncate = OFF

# 或使用
innodb_force_recovery = 1

事务提交 → 修改 Buffer Pool 中的页（变成脏页）
        → 写入 Redo Log（持久化保证）
        → 后台线程异步将脏页刷回磁盘
        → 脏页变干净

innodb_io_capacity = 200     # HDD 机械硬盘
innodb_io_capacity = 2000    # 普通 SATA SSD
innodb_io_capacity = 5000    # 企业级 NVMe SSD

innodb_io_capacity_max = 3000  # 建议为 io_capacity 的 1.5-2 倍

innodb_max_dirty_pages_pct = 75   # 默认 75%，建议值 70-85

innodb_max_dirty_pages_pct_lwm = 50  # 默认 10%（5.7+）

innodb_adaptive_flushing = ON   # 默认开启，强烈建议保持

innodb_flush_neighbors = 0  # HDD = 1，SSD = 0（推荐）

-- 脏页数量
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

-- 脏页比例（估算）
SELECT 
    Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total * 100 AS dirty_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty';

-- 查看刷脏行为
SHOW ENGINE INNODB STATUS\G

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8589934592
...
Pages read 152345, created 4321, written 89123
  **Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000**
  **Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s**
LRU len: 524287, unzip_LRU len: 0
**I/O sum[0]:cur[0], unzip sum[0]:cur[0]**

innodb_io_capacity = 5000           # 根据 SSD 性能调整
innodb_io_capacity_max = 10000
innodb_max_dirty_pages_pct = 75     # 保持默认
innodb_max_dirty_pages_pct_lwm = 30 # 提前开始主动刷脏
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0          # SSD 不需要
innodb_log_file_size = 2G           # 加大 Redo Log，减少 Checkpoint

innodb_io_capacity = 8000           # 可以放高一些
innodb_max_dirty_pages_pct = 80     # 允许更多脏页积累
innodb_log_file_size = 4G           # 大日志空间减少 Checkpoint

innodb_io_capacity = 1000           # 写入少，不需要太高 IOPS
innodb_max_dirty_pages_pct = 50     # 脏页很少，保持干净
innodb_flush_neighbors = 0

-- 查看等待 Checkpoint 的次数
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
-- 如果这个值持续增加，说明 Redo Log 太小

-- 查看刷脏页线程的状态
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_fsyncs';

sync_binlog = 1

事务 T1 提交 → Binlog 写入 OS Cache → fsync 磁盘 ✅
事务 T2 提交 → Binlog 写入 OS Cache → fsync 磁盘 ✅
事务 T3 提交 → Binlog 写入 OS Cache → fsync 磁盘 ✅
...

sync_binlog = 0

事务 T1 提交 → Binlog 写入 OS Cache（内存）
事务 T2 提交 → Binlog 写入 OS Cache（内存）
事务 T3 提交 → Binlog 写入 OS Cache（内存）
...
（OS 在某个时间点自动刷盘，可能过几秒）

sync_binlog = 100

事务 T1 提交 → Binlog 写 OS Cache（不刷盘）
事务 T2 提交 → Binlog 写 OS Cache（不刷盘）
...
事务 T100 提交 → Binlog 写 OS Cache + fsync ✅
事务 T101 提交 → Binlog 写 OS Cache（不刷盘）
...

innodb_flush_log_at_trx_commit = 1  -- Redo Log 每次提交刷盘
sync_binlog = 1                      -- Binlog 每次提交刷盘

innodb_flush_log_at_trx_commit = 2  -- Redo Log 每秒刷盘
sync_binlog = 0                      -- Binlog 依赖 OS 刷盘

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1000

# 双 1 配置 —— 安全第一
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 平衡配置
sync_binlog = 0
innodb_flush_log_at_trx_commit = 2

-- 会话级别临时调整
SET SESSION sync_binlog = 1000;
SET SESSION innodb_flush_log_at_trx_commit = 2;
-- 执行大批量操作
-- 完成后恢复
SET SESSION sync_binlog = 1;
SET SESSION innodb_flush_log_at_trx_commit = 1;

崩溃发生在：Redo Log 已写 ✅ → 系统崩溃 ❌ → Binlog 未写 ❌

恢复后：
- 主库：事务已通过 Redo Log 恢复 ✅
- 从库：Binlog 中没有这个事务 ❌
→ 从库永远少了这条数据，主从不一致

崩溃发生在：Binlog 已写 ✅ → 系统崩溃 ❌ → Redo Log 未写 ❌

恢复后：
- 主库：事务没有在 Redo Log 中，未恢复 ❌
- 从库：从库已通过 Binlog 同步了该事务 ✅
→ 主库少了这条数据，主从不一致

事务提交
    │
    ├──　Phase 1：Prepare 阶段
    │   ├── 写入 Undo Log（便于回滚）
    │   ├── 将 Redo Log 写入磁盘，状态 = PREPARE
    │   └── Redo Log 刷新到磁盘（fsync）
    │
    ├──　Phase 2：Commit 阶段
    │   ├── 写入 Binlog，记录该事务的 XID
    │   ├── Binlog 刷新到磁盘（fsync）
    │   ├── 在 Redo Log 中标记该事务为 COMMIT
    │   └── 释放锁，提交完成
    │
    └──　事务成功

-- 假设执行：UPDATE users SET balance = 100 WHERE id = 1

BEGIN;

-- 1. 执行 SQL，在 Buffer Pool 修改数据，并记录 Undo Log
UPDATE users SET balance = 100 WHERE id = 1;

-- 2. Prepare 阶段
--    分配 XID = 123
--    写入 Redo Log：[XID=123, PREPARE, space=5, page=100, offset=800, data='100']
--    fsync Redo Log 到磁盘

-- 3. Commit 阶段（写 Binlog）
--    写入 Binlog：[XID=123] ... UPDATE `test`.`users` ... SET @3=100 WHERE @1=1
--    fsync Binlog 到磁盘

-- 4. 标记提交
--    更新 Redo Log：[XID=123, COMMIT]
--    释放锁

COMMIT;

系统崩溃 → 重启
    │
    ├── 扫描 Redo Log 中所有 PREPARE 状态的事务
    │
    ├── 对每个 PREPARE 状态的事务：
    │       │
    │       ├── 检查 Binlog 中是否有该事务的 XID
    │       │       │
    │       │       ├── 如果 Binlog 中有该 XID ✅
    │       │       │    → 说明两阶段都执行了
    │       │       │    → 提交事务（标记 Redo Log 为 COMMIT）
    │       │       │    → 重做该事务的修改
    │       │       │
    │       │       └── 如果 Binlog 中没有该 XID ❌
    │       │            → 说明事务未完成提交
    │       │            → 回滚事务（使用 Undo Log）
    │       │
    │       └── 决策完成
    │
    └── 恢复结束，Redo Log 和 Binlog 一致

无 group commit（每个事务独立）：
T1: Prepare(fsync) → Binlog(fsync) → Commit
T2: Prepare(fsync) → Binlog(fsync) → Commit
T3: Prepare(fsync) → Binlog(fsync) → Commit
→ 6 次 fsync

有 group commit（合并刷盘）：
T1 Prepare → T2 Prepare → T3 Prepare
    ↓ （合并 fsync Redo Log）
3 个事务一起 fsync Redo Log

T1 Binlog → T2 Binlog → T3 Binlog
    ↓ （合并 fsync Binlog）
3 个事务一起 fsync Binlog

T1 Commit → T2 Commit → T3 Commit
→ 2 次 fsync（而非 6 次）

-- 主库执行
INSERT INTO logs(user_id, login_time) VALUES(1, NOW());

-- 从库执行同样的 SQL
INSERT INTO logs(user_id, login_time) VALUES(1, NOW());
-- 问题：从库的 NOW() 返回时间 ≠ 主库
-- 结果：主从数据不一致！

-- 主库执行
DELETE FROM users ORDER BY id LIMIT 1;
-- 删除的是 id 最小的那条记录

-- 问题：如果从库数据行顺序不同
-- 从库可能删除不同的记录！

-- 没有 ORDER BY 的 LIMIT 操作
UPDATE users SET status = 1 LIMIT 100;
-- 主库和从库更新的"前 100 行"可能不同

-- 主库
CREATE PROCEDURE update_balance(IN user_id INT)
BEGIN
    UPDATE users SET balance = balance * 1.1 WHERE id = user_id;
END;

CALL update_balance(1);

-- 问题：如果从库的存储过程被修改或不存在
-- 或者存储过程内部使用了 NOW() 等函数

-- 主库执行
UPDATE users SET rank = (
    SELECT COUNT(*) + 1 FROM users u2 WHERE u2.score > users.score
);

-- 从库执行同样的 SQL
-- 但如果主从之间有微小差异，排名计算就会不同

-- 主库默认字符集 utf8mb4
SET NAMES utf8mb4;
INSERT INTO names VALUES('😀');  -- emoji

-- 从库默认字符集 latin1
-- 重放该 SQL 时报错或插入乱码

-- 主库
SET sql_mode = '';
UPDATE users SET balance = '100abc';  -- 隐式转换为 100

-- 从库
-- sql_mode = STRICT_TRANS_TABLES
-- 同样的 SQL 报错！主从不一致

binlog_format = STATEMENT

# 当检测到非确定性语句时记录警告但不阻止
binlog_direct_non_transactional_updates = OFF

# 记录使用非确定性函数的使用情况
log_bin_trust_function_creators = 0  # 限制函数创建

MySQL Server 层（Binlog）
    │
    ├── InnoDB 引擎（Redo Log + Undo Log）
    ├── MyISAM 引擎（无 Redo Log，无事务）
    └── Memory 引擎（无持久化日志）

// 记录的是"第5号表空间、第100号页、偏移800处，写了'hello'"
REDO: space=5, page_no=100, offset=800, len=5, data='hello'

-- 记录的是"执行了这条 SQL"
SET TIMESTAMP=...;
UPDATE users SET name = 'hello' WHERE id = 1;

BEGIN;
UPDATE users SET balance = 100 WHERE id = 1;
-- Redo Log 写入：巴拉巴拉巴拉（prepare）
UPDATE users SET balance = 200 WHERE id = 2;
-- Redo Log 写入：巴拉巴拉（prepare）
COMMIT;
-- Binlog 写入：一条事务记录
-- Redo Log 更新：prepare → commit

┌─────────────────────────────────────────────┐
│  [已清理] [活跃区域] [未使用] [已清理]        │
│            ↑ write         ↑ checkpoint      │
└─────────────────────────────────────────────┘

mysql-bin.000001  → 满了 →  mysql-bin.000002  → 满了 →  mysql-bin.000003
     ↑ 顺序追加                   ↑ 顺序追加

事务提交时的 I/O 操作：

sync_binlog=1 + innodb_flush_log_at_trx_commit=1 （最安全）
    1. Redo Log 刷盘（prepare）
    2. Binlog 刷盘
    3. Redo Log 刷盘（commit）
    → 最多 3 次 fsync，绝对安全

sync_binlog=0 + innodb_flush_log_at_trx_commit=2 （高性能）
    1. Redo Log 写 OS Cache
    2. Binlog 写 OS Cache
    → 0 次 fsync，OS 崩溃可能丢数据

-- 问题：以下场景用哪个日志？
-- 1. MySQL 突然断电重启，某条已提交的数据丢了 → Redo Log
-- 2. 要从备份恢复到昨天下午 3 点的状态 → Binlog
-- 3. 从库要同步主库的数据 → Binlog
-- 4. 事务回滚 → Undo Log（不是 Redo 也不是 Binlog）
-- 5. 查看谁在什么时间改了哪些数据 → Binlog

-- 原始 SQL
UPDATE users SET name = 'Bob' WHERE id = 1;

-- ROW 格式实际记录
Table_map: `test`.`users` mapped to number 89
Update_rows:
  BEFORE: @1=1 @2='Alice' @3=500  ← Before Image
  AFTER:  @1=1 @2='Bob'   @3=500  ← After Image

# 推荐设置（节省空间且安全）
binlog_row_image = minimal

UPDATE users SET name = 'Bob' WHERE id = 1;
-- Before: @1=1 （仅记录主键）
-- After:  @1=1 @2='Bob' （记录主键和变更列）

-- 即使是非确定性操作也不会出问题
UPDATE users SET create_time = NOW();       -- ✅ ROW 精确复制结果
UPDATE users SET balance = RAND() * 1000;  -- ✅ 没问题
DELETE FROM users ORDER BY RAND() LIMIT 1; -- ✅ 没问题

# 解析 ROW 格式 Binlog，反向操作实现闪回
mysqlbinlog --flashback mysql-bin.000001 | mysql -u root -p

# 原理：把 INSERT 转成 DELETE，DELETE 转成 INSERT，UPDATE 交换 Before/After

-- 批量操作时差异巨大
UPDATE users SET status = 1;  -- 100 万行

-- STATEMENT：只记录一行 SQL
SET TIMESTAMP=...; UPDATE users SET status = 1;

-- ROW：记录 100 万行的变更
Row 1: BEFORE(id=1, status=0) AFTER(id=1, status=1)
Row 2: BEFORE(id=2, status=0) AFTER(id=2, status=1)
...
-- 日志大小从几十字节 -> 几百 MB

# 必须用 -v 参数解码
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000001

主库 ── 产生 Binlog ──→ 从库 ── 读取 Binlog → 回放 Binlog
                                  ↑
                              I/O 线程拉取
                              SQL 线程回放

# 从全量备份恢复
mysql -u root -p < full_backup.sql

# 应用 Binlog 恢复到崩溃前最后一刻
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

# 恢复到指定时间点
mysqlbinlog --stop-datetime="2026-05-18 14:30:00" mysql-bin.000001 | mysql

# 恢复到指定位置
mysqlbinlog --stop-position=123456 mysql-bin.000001 | mysql

# 必须配置 server-id（否则无法启用 Binlog）
server-id = 1

# 启用 Binlog（指定文件名前缀）
log_bin = /var/log/mysql/mysql-bin

# Binlog 格式
binlog_format = ROW

# 单个 Binlog 文件最大大小（默认 1G）
max_binlog_size = 1G

# Binlog 保留天数
expire_logs_days = 7
# 或者 MySQL 8.0 用
binlog_expire_logs_seconds = 604800

# 每次事务提交刷盘策略
sync_binlog = 1

mysql-bin.000001  ← 第一个 Binlog 文件
mysql-bin.000002  ← 第二个
mysql-bin.000003  ← 第三个
mysql-bin.index   ← Binlog 索引文件（记录有哪些 Binlog 文件）

SHOW BINARY LOGS;
-- +------------------+-----------+
-- | Log_name         | File_size |
-- +------------------+-----------+
-- | mysql-bin.000001 |  1073741  |
-- | mysql-bin.000002 |  5242880  |
-- +------------------+-----------+

事务提交
    │
    ├── 1. 事务修改记录写入 Binlog Cache（事务私有）
    │
    ├── 2. 事务提交时，Binlog Cache 刷入 Binlog 文件
    │
    ├── 3. Binlog 写入 OS Cache
    │
    └── 4. 根据 sync_binlog 配置刷盘

binlog_cache_size = 32768       -- 事务的 Binlog Cache 大小（默认 32KB）
max_binlog_cache_size = 2G      -- 单个事务最大 Binlog 大小
binlog_stmt_cache_size = 32768  -- 非事务表的 Binlog Cache

-- 查看当前正在写入的 Binlog
SHOW MASTER STATUS;

-- 刷新 Binlog（产生新文件）
FLUSH LOGS;

-- 清理过期 Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';        -- 删除指定文件之前的
PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00'; -- 删除指定时间之前的

-- 查看 Binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 执行的 SQL
UPDATE users SET balance = balance + 100 WHERE age > 30;

-- Binlog 中的记录
# at 211
#2026-05-18 10:00:00 server id 1  end_log_pos 321  Query  thread_id=14
# exec_time=0  error_code=0
SET TIMESTAMP=1718600000/*!*/;
UPDATE users SET balance = balance + 100 WHERE age > 30
/*!*/;

-- 执行的 SQL
UPDATE users SET balance = balance + 100 WHERE age > 30;

-- Binlog 中的记录（实际内容）
# at 211
#2026-05-18 10:00:00 server id 1  end_log_pos 321  Table_map: `test`.`users` mapped to number 89
# at 321
#2026-05-18 10:00:00 server id 1  end_log_pos 421  Update_rows: table id 89 flags: STMT_END_F

BINLOG '
--- 二进制格式，记录了具体的行变更 ---
# 修改前: id=1, name='Alice', balance=500, age=35
# 修改后: id=1, name='Alice', balance=600, age=35
# 修改前: id=2, name='Bob',   balance=200, age=40
# 修改后: id=2, name='Bob',   balance=300, age=40
'

# 解码 ROW 格式的 Binlog
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000001

# 输出示例：
### UPDATE `test`.`users`
### WHERE
###   @1=1  -- id
###   @2='Alice'  -- name
###   @3=500      -- old balance
### SET
###   @3=600      -- new balance

执行的 SQL
    │
    ├── 语句是确定性的（安全）→ STATEMENT 格式
    │   └── 如：UPDATE users SET name = 'fixed' WHERE id = 1;
    │
    └── 语句是非确定性的（不安全）→ ROW 格式
        └── 如：UPDATE users SET balance = NOW(); -- 包含函数

# MySQL 5.7+ 默认已经是 ROW 格式
binlog_format = ROW

# 可以配合以下参数减少 ROW 格式的日志量
binlog_row_image = full   # 或 minimal, noblob