MySQL 通过 binlog 日志恢复数据

binlog 日志，即 binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个 binlog 日志文件，从节点从 binlog 中同步数据，也可以通过 binlog 日志来恢复数据；

binlog 日志包括两类文件；第一个是二进制索引文件(后缀名为.index)，第二个为日志文件(后缀名为.00000*)，记录数据库所有的 DDL 和 DML (除了查询语句 select)语句事件

1.1 基础命令

-- 登录 MySQL 查看 binlog 日志的状态
SHOW VARIABLES LIKE '%log_bin%';
 
-- 查看所有 binlog 日志文件
SHOW MASTER LOGS;
 
-- 查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值
SHOW MASTER STATUS;
 
-- 刷新日志
FLUSH LOGS;
 
-- 清空所有 binlog 日志命令
RESET MASTER;

每当 mysql.service 服务重启时，会自动执行刷新 binlog 日志命令，mysqldump 备份数据时加 -F 选项也会刷新binlog 日志

1.2 恢复数据

1.2.1 场景

当前数据：

进行数据备份：

mysqldump -h 101.201.233.67 -P 10000 -u root --single-transaction --flush-logs --master-data=2 --all-databases > ./ali_all.sql -p

进行数据新增：

进行数据修改：

删库跑路！！

1.2.2 数据还原

dba 上场

Step1：刷新 binlog

刷新 binlog 重新创建一个日志的目的是接下来所有操作的数据都会写入到新的日志中，旧日志中不会在写入任何数据。旧日志的数据就是备份之后到删库之前的所有操作日志，重建日志就不会有过多的数据影响恢复

FLUSH LOGS;

Step2：备份文件恢复

mysql -h 101.201.233.67 -P 10000 -u root -p < ali_all.sql

可以看到此时数据只恢复到 mysqldump 那一时刻，接下来的数据需要用 binlog 进行恢复

Step 3：解析 binlog 信息

mysqlbinlog binlog.000002 > binlog.sql
 
-- 打开 binlog.sql

可以看到 end_log_pos 1919 是进行更新的最后位置

Step4：执行复原

mysqlbinlog --stop-position=1919 binlog.000002 | mysql -h 101.201.233.67 -P 10000 -u root -p

至此，数据就已经全部恢复啦

1.3 mysqlbinlog 语法

mysqlbinlog [options] logfile1 logfile2 ...

选项：

• -d，--database=name 仅显示指定数据库的转储内容
• -o，--offset=N 跳过前 N 行的日志条目
• -r，--result-file=name 将输入的文本格式的文件转储到指定的文件
• -s，--short-form 使用简单格式
• --set-charset=name 在转储文件的开头增加'SET NAMES character_set'语句
• --start-datetime=name 转储日志的起始时间
• --stop-datetime=name 转储日志的截止时间
• -j，--start-position=N 转储日志的起始位置
• --stop-position=N 转储日志的截止位置