MySQL BINLOG工具说明及恢复数据

Binlog 常见操作命令

Binlog 的查询方式一般分为两种，一种是进入 MySQL 控制台进行查询，另一种是通过 MySQL 提供的工具 mysqlbinlog 进行查询，两者的不同如下介绍

通过 MySQL Cli 查询 BINLOG 信息

在 cli 中，常见的命令如下：

# 查询 binlog 格式
show variables like  'binlong_format';

# 查询 binlog 存储位置
show variables like 'datadir';

# 查询 master 正在写入的 binlog 信息
show master status\G;

# 通过 offset 查看 binlog 信息
show binlog events in 'mysql-bin.000001' limit 10;

# 通过 position 查看 binlog 信息
show binlog events in 'mysql-bin.000001' from 123456 limit 10;

使用 SHOW BINLOG EVENTS 的问题：

使用该命令时，如果当前的 binlog 文件很大，而且没有指定 limit ，会引发对资源的过度消耗。因为 MySQL 客户端需要将 binlog 的全部内容处理，返回并显示出来。为了防止这种情况，mysqlbinlog 工具是一个很好的选择。

通过 mysqlbinlog 查询 BINLOG 信息

查看 binlog 文件的内容

# 查询 binlog 信息
$ mysqlbinlog --no-defaults mysql-bin.000001 less
# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

字段说明：

at 表示 offset 或者说事件开始的起始位置
100309 9:28:36 server id 123 表示 server 123 开始执行事件的日期时间点
end_log_pos 245 表示事件的结束位置 +1，或者说是下一个事件的起始位置。
exec_time 表示 master 上执行花费的时间，在 slave 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。
error_code=0 表示没有错误发生。

mysqlbinlog 用途如下：

mysqlbinlog 可以作为代理 cli 读取 binlog 的工具；
mysqlbinlog 可以讲执行过的 SQL 语句输出，用于数据的恢复或备份。

查看 BINLOG 日志

# 查询规定时间后发生的 binlog 日志
$ mysqlbinlog --no-defaults --base64-output=decode-rows -vvv --start-datetime="2021-07-30 17:25:36" --database=world  mysql-bin.000002
# at 718
#210730 17:25:36 server id 1  end_log_pos 784 CRC32 0xb9f16bcb  Table_map: `world`.`city` mapped to number 126
# at 784
#210730 17:25:36 server id 1  end_log_pos 873 CRC32 0xc38bf89a  Update_rows: table id 126 flags: STMT_END_F
### UPDATE `world`.`city`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Herat' /* STRING(140) meta=65164 nullable=0 is_null=0 */
###   @3='AFG' /* STRING(12) meta=65036 nullable=0 is_null=0 */
###   @4='Herat' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @5=186800 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gtidtest' /* STRING(140) meta=65164 nullable=0 is_null=0 */
###   @3='AFG' /* STRING(12) meta=65036 nullable=0 is_null=0 */
###   @4='Herat' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @5=186800 /* INT meta=0 nullable=0 is_null=0 */
# at 873
#210730 17:25:36 server id 1  end_log_pos 904 CRC32 0xc3ae9cbb  Xid = 77267
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

导出 BINLOG 日志，用于分析和排查 sql 语句

$ mysqlbinlog --no-defaults --base64-output=decode-rows -vvv --start-datetime="2021-07-30 17:25:36" --database=world  mysql-bin.000002 > /tmp/world.sql

导入 BINLOG 日志

# 通过 binloig 进行恢复
$ mysqlbinlog --start-position=718 --stop-position=784 --database=db_name mysql-bin.000001  mysql -uroot -p dbname

# 通过 binlog 导出的 SQL 进行恢复
$ mysql -uroot -p dbname < binlog_raw.sql

mysqlbinlog 参数说明

官方文档：https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

参数变化说明

# 版本号不同
$ /usr/local/mysql57/bin/mysqlbinlog --version
/usr/local/mysql57/bin/mysqlbinlog Ver 3.4 for linux-glibc2.12 at x86_64
$ /usr/local/mysql80/bin/mysqlbinlog --version  
/usr/local/mysql80/bin/mysqlbinlog  Ver 8.0.12 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

# 新增参数
--server-public-key-path=name 
--get-server-public-key
--ssl-fips-mode=name
--print-table-metadata
新增TLS的版本支持TLSv1.2

# 剔除参数：
--secure-auth
--ssl 使用参数替代--ssl-mode
--ssl-verify-server-cert 使用参数--ssl-mode=VERIFY_IDENTITY替代

mysqlbinlog 的常用参数

--base64-output=name        binlog输出语句的base64解码 分为三类：       
                            默认是值auto ,仅打印base64编码的需要的信息，如row-based 事件和事件的描述信息。
                            never 仅适用于不是row-based的事件
                            decode-rows  配合--verbose选项一起使用解码行事件到带注释的伪SQL语句
--start-datetime=name  #binlog文件读取的起始时间点，可接受datetime和timestamp类型，格式2004-12-25 11:25:56
--stop-datetime=name   #binlog文件的结束的时间点。
--start-position=     #读取binlog文件的位置信息
--stop-position=      #读取binlog文件的结束的位置信息
-d, --database=name    #列出数据库的名称(仅限binlog文件存储在本地)
--rewrite-db=name      #将binlog中的事件信息重定向到新的数据库
--offset               #跳过 log 中 N 个条目
--no-defaults          #读取没有选项的文件，指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令
--binlog-row-event-max-size= #指定基于行的binlog的大小，改值必须是256的倍数
-v, --verbose 重新构建伪SQL语句的行信息输出，-v -v会增加列类型的注释信息。

## GTID相关
--skip-gtids            # 跳过GTID的幂等性机制的检查，也可以理解为截取日志的时候不带有gtid的信息
--include-gtids=name    # 截取指定值的gtid
--exculde-gtids=name    # 排除指定值的gtid

GTID截取示例

# 注意：以下示例 都是在binlog日志文件 所在的目录中执行的
 # 示例1：截取多个日志文件的多个范围的gtid记录。（示例就直接来个多范围的，这样单文件或单个gtid截取就更不在话下了）
$ /usr/local/mysql/bin/mysqlbinlog --include-gtids '0a27af20-28d1-11ea-8a83-fa163e680ae8:5-11' mysql-bin.000002 mysql-bin.000003 >/tmp/gtid.sql 
# 截取5-11号的gtid记录,从 mysql-bin.000002 mysql-bin.000003 这2个日志文件进行截取,将截取的结果保存到/tmp/gtid.sql文件中。
# 注意:5-11号gtid必须在mysql-bin.000002 mysql-bin.000003 这2个日志文件中存在(有记录)

# 示例2：截取指定数据库的gtid 就比上面的示例1 多了一个-d选项
$ /usr/local/mysql/bin/mysqlbinlog -d xxx --include-gtids '0a27af20-28d1-11ea-8a83-fa163e680ae8:5-11' mysql-bin.000002 mysql-bin.000003 >/tmp/gtid2.sql 
# xxx 替换为 你要截取的数据库名称

使用 BINLOG 恢复数据

准备数据

# 创建临时库
mysql> create database if not exists test_binlog default charset=utf8mb4;
# 创建临时表
mysql> use test_binlog;
mysql> create table sync_test (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(30) NOT NULL) engine=innodb default charset=utf8mb4;
mysql> desc sync_test;
+-------+-------------+------+-----+---------+----------------+
 Field  Type         Null  Key  Default  Extra          
+-------+-------------+------+-----+---------+----------------+
 id     int          NO    PRI  NULL     auto_increment 
 name   varchar(30)  NO         NULL                    
+-------+-------------+------+-----+---------+----------------+

# 添加数据
mysql> insert into sync_test(name) values('jinzha'),('muzha'),('nezha');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from sync_test;
+----+--------+
 id  name   
+----+--------+
  1  jinzha 
  2  muzha  
  3  nezha  
+----+--------+

删除表或者数据

误删操作

# 删除 name=nezha 的数据
mysql> delete from sync_test where name='nezha';

# 插入几条数据
mysql> insert into sync_test (name) values('jinwa'),('muwa'),('shuiwa');
# 删除表
mysql> drop table sync_test;

数据的恢复

数据恢复前工作

在执行数据恢复前，如果操作的是生产环境，会有如下建议：

使用 flush logs 前滚 binlog 日志序列号，好处如下：
- 可将误删除操作，定位在一个 binlog 文件中，便于之后的数据分析和恢复；
- 避免操作正在被使用的 binlog 文件，防止意外情况；
数据的恢复不要在生产库中执行，现在临时库恢复，确认无误后，再导入生产库，防止对数据产生二次破坏。

通常来说，恢复主要有两个步骤：

在临时库中，恢复定期执行的全量备份数据。
基于全量备份的数据点，通过 binlog 来恢复误操作和正常的数据

使用 BINLOG 做数据恢复前：

# 查看正在使用的 binlog文件
mysql> show master status;  # 当前正在使用binlog序列号是 mysql-bin.000003
+------------------+----------+--------------+------------------+-------------------------------------------+
 File              Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set                         
+------------------+----------+--------------+------------------+-------------------------------------------+
 mysql-bin.000003      2641                                  5b07de60-66d1-11eb-a073-5254003716cd:1-28 
+------------------+----------+--------------+------------------+-------------------------------------------+
# 执行 flush logs 前滚binlog序列号，切换使用的binlog
mysql> flush logs;
# 查看正在使用的 binlog 文件序列号
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
 File              Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set                         
+------------------+----------+--------------+------------------+-------------------------------------------+
 mysql-bin.000004       196                                  5b07de60-66d1-11eb-a073-5254003716cd:1-28 
+------------------+----------+--------------+------------------+-------------------------------------------+

确定恢复数据的步骤

这里主要有两条误删的操作，数据行的误删和表的误删。有两种方式进行恢复。

方式一：首先恢复到删除表操作之前的位置，然后再单独恢复误删的数据行
方式二：首先恢复到误删数据行之前的位置，然后跳过误删时间再恢复数据表操作之前的位置

查询创建表的事件位置和删除表的事件位置

# 根据时间过滤binlog的事件信息
$ mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime='2021-08-04 16:45:35' --stop-datetime='2021-08-04 16:56:30' -d test_binlog mysql-bin.000003

# 创建表的事件位置
# at 1211
#210804 16:45:35 server id 1  end_log_pos 1447 CRC32 0xe9f84572         Query   thread_id=8     exec_time=1     error_code=0    Xid = 30
use `test_binlog`/*!*/;
SET TIMESTAMP=1628066735/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table sync_test (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(30) NOT NULL) engine=innodb default charset=utf8mb4
/*!*/;

# 删除表的事件位置
# at 2416
#210804 16:51:36 server id 1  end_log_pos 2493 CRC32 0x8fa2002d         GTID    last_committed=8        sequence_number=9       rbr_only=no     original_committed_timestamp=1628067096868271   immediate_commit_timestamp=1628067096868271     transaction_length=225
# original_commit_timestamp=1628067096868271 (2021-08-04 16:51:36.868271 CST)
# immediate_commit_timestamp=1628067096868271 (2021-08-04 16:51:36.868271 CST)
/*!80001 SET @@session.original_commit_timestamp=1628067096868271*//*!*/;
/*!80014 SET @@session.original_server_version=80023*//*!*/;
/*!80014 SET @@session.immediate_server_version=80023*//*!*/;
SET @@SESSION.GTID_NEXT= '5b07de60-66d1-11eb-a073-5254003716cd:28'/*!*/;
# at 2493
#210804 16:51:36 server id 1  end_log_pos 2641 CRC32 0xc1fea153         Query   thread_id=8     exec_time=0     error_code=0    Xid = 39
SET TIMESTAMP=1628067096/*!*/;
DROP TABLE `sync_test` /* generated by server */

# 删除 name=nezha 的事件位置
# at 2009
#210804 16:49:35 server id 1  end_log_pos 2055 CRC32 0x95bc7a45         Delete_rows: table id 97 flags: STMT_END_F
### DELETE FROM `test_binlog`.`sync_test`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='nezha' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 2055

根据位置导出 SQL 文件

# 跳过gtid，导出第一次插入的数据
$ mysqlbinlog  --skip-gtids=true  --start-position='1211' --stop-position='1778'  mysql-bin.000003 > /tmp/test_binlog_step1.sql 

# 跳过删除 name = nezha 的SQL 事务，结束点是删除 sync_test 表之前
mysqlbinlog  --skip-gtids --start-position='2165' --stop-position='2416'  mysql-bin.000003 > /tmp/test_binlog_step2.sql

# 导入数据
mysql> source /tmp/test_binlog_step1.sql;
mysql> source /tmp/test_binlog_step2.sql;

# 查看数据
mysql> select * from sync_test;
+----+--------+
 id  name   
+----+--------+
  1  jinzha 
  2  muzha  
  3  nezha  
  4  jinwa  
  5  muwa   
  6  shuiwa 
+----+--------+

说明： 因为恢复数据的时候，这些SQL语句对应的 gtid 事务已经执行过了，由于gtid 特性是只要binlog中记录了对应的gtid，就不会执行，这个是gtid 的幂等性检查机制，因此 mysqlbinlog 过滤数据的时候一定要开启参数 --skip-gtids, 默认值是 true

database > MySQL

MySQL BINLOG工具说明及恢复数据

http://www.qiqios.cn/2021/08/05/mysql-binlog工具说明及恢复数据/

作者

一亩三分地

发布于

2021年8月5日

许可协议

MySQL慢日志说明上一篇

MySQL库/表名大小写修改说明下一篇