mysqlbinlog工具使用

MySQL的 binlog日志文件会记录表结构变更和表数据变更， 如果想要查看这些日志文件的内容，就需要使用mysqlbinlog工具，它的主要作用是进行数据的恢复(基于时间点备份后重放操作)。

MySQL版本

本次测试验证使用的版本是5.7.34

MySQL开启binlog

查看是否开启binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
复制

开启binlog日志

修改my.cnf文件

[mysqld]
# 日志保存位置,默认与表空间同一目录
log_bin=mysql-bin
# binlog的格式有三种：STATEMENT，ROW，MIXED。
binlog-format=ROW
# binlog过期清理时间
expire_logs_days=30
# binlog每个日志文件大小
max_binlog_size=100m
# binlog缓存大小
binlog_cache_size=10m
max_binlog_cache_size=512m
# binlog文件刷新机制，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘，1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=1

#其它常用可选参数
# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=db
# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=no_db
复制

binlog-format的三种模式区别

• STATEMENT**：基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。该模式下产生的binlog日志量会比较少，但可能导致主从数据不一致。

• ROW：基于行的复制，不记录每一条具体执行的SQL语句，仅需记录哪条数据被修改了，以及修改前后的样子。该模式下产生的binlog日志量会比较大，但优点是会非常清楚的记录下每一行数据修改的细节，主从复制不会出错。

• Mixed：混合模式复制，以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

重启mysql服务后可以看到开启binlog的信息

mysql> show VARIABLES where variable_name in('datadir','log_bin','log_bin_basename','expire_logs_days','max_binlog_size','binlog_format','sync_binlog');
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| binlog_format    | ROW                          |
| datadir          | data/mysql5.7data/          |
| expire_logs_days | 30                           |
| log_bin          | ON                           |
| log_bin_basename | data/mysql5.7data/mysql-bin |
| max_binlog_size  | 104857600                    |
| sync_binlog      | 1                            |
+------------------+------------------------------+
复制

查询当前数据库中 BINLOG 名称及大小

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000021 |   6379868 |
| mysql-bin.000022 |       217 |
| mysql-bin.000023 |       194 |
+------------------+-----------+
复制

查看 master 正在写入的 BINLOG 信息

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000023
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 79d58879-c14c-11ec-b660-005056a284c9:1-9922
1 row in set (0.00 sec)
复制

mysqlbinlog工具使用

使用帮助

[mysql@yejf ~]$ mysqlbinlog --help
mysqlbinlog: [ERROR] unknown variable 'default_character_set=utf8mb4'
复制

解决办法：

在执行命令的时候增加参数 :  --no-defaults， 使用如下命令

mysqlbinlog --no-defaults
复制

binlog日志

数据库执行sql

mysql> use temp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table tmp;
Query OK, 0 rows affected (0.01 sec)
mysql> create table tmp(a int,b varchar(100));
Query OK, 0 rows affected (0.01 sec)
复制

解析命令

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000023|more
复制

可以看到有操作的语句

mysqlbinlog 转出来的数据操作sql是无法直接查看，需要加个参数

--base64-output=DECODE-ROWS -v
复制

也可以根据时间点去查询，以及使用-d 指定筛选的库

#示例
mysqlbinlog -d test --start-datetime='2021-09-18 17:25:08' --stop-datetime='2021-09-18 17:40:15' mysql-bin.000002
复制

内容解析

# at 440
#230316 14:35:41 server id 1  end_log_pos 552 CRC32 0xd6700f7c  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1678948541/*!*/;
create table tmp(a int,b varchar(100))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
复制

• at
  表示 offset 或者说事件开始的起始位置

• 230316 14:35:41 server id 1
  表示 server 1开始执行事件的日期

• end_log_pos 552
  表示事件的结束位置 + 1，或者说是下一个事件的起始位置。

• exec_time
  表示在 master 上花费的时间，在 salve 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。

• rror_code=0
  表示没有错误发生。

常用参数

• --database
  仅仅列出配置的数据库信息

• --no-defaults
  读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8
  指令

• --offset
  跳过 log 中 N 个条目

• --verbose

  将日志信息重建为原始的 SQL 陈述。

• -v
  仅仅解释行信息

• -vv
  不但解释行信息，还将 SQL 列类型的注释信息也解析出来

• --start-datetime
  

  显示从指定的时间或之后的时间的事件。

• 接收 DATETIME
  或者 TIMESTRAMP
  格式。

• --stop-datetime
  

  显示从指定的时间或之前的时间的事件

• --start-position
  

  开始的位置

• --stop-position
  

  结束的位置

• --include-gtids

   只解析出指定的 GTID 的事务

• --skip-gtids=true

  解析后的文件中不存在gtid信息

• --base64-output=decode-rows
  

  将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。

• AUTO
  默认参数，自动显示 BINLOG 中的必要的语句

• NEVER
  不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。

• DECODE-ROWS
  显示通过 -v
  显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。

使用mysqlbinlog恢复数据

场景模拟

数据库模拟数据删除

mysql> drop table tmp;
Query OK, 0 rows affected (0.01 sec)
mysql> create table tmp(a int,b varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tmp values(1,'aa'),(2,'bb'),(3,'cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
#时间点1：对表进行备份

mysql> delete from tmp where a=1;
Query OK, 1 row affected (0.00 sec)
#时间点2：执行删除操作

mysql> truncate table tmp;
Query OK, 0 rows affected (0.01 sec)
#时间点3：执行全表删除的误操作
复制

需求：在时间点1做了备份，在时间3做了truncate表的误操作，要求把数据恢复做时间点2。

基于时间点来回放binlog

#示例
mysqlbinlog  -d test --start-datetime='2021-09-18 17:25:08' --stop-datetime='2021-09-18 17:40:15' mysql-bin.000001|mysql test
复制

基于position回放binlog

实施步骤：

1. 发现问题尽快刷新日志    flush logs;  

2. 尽快改表名, 防止继续写入不正确的数据  

   alter table tmp rename to tmp_bak;  
   复制

3. 查看binlog, 找到TRUNCATE tmp;
   sql的position    

   mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000023 | grep -i -A 20 -B 20 'truncate' 
   或 
   show binlog events in 'mysql-bin.000023';
   复制

   执行truncate的pos为1238，执行之前的pos为1153

4. 新建实例, 拿最近的备份(全备/对应库/对应表都行)导入

   mysql> select * from tmp;
   +------+------+
   | a    | b    |
   +------+------+
   |    1 | aa   |
   |    2 | bb   |
   |    3 | cc   |
   +------+------+
   复制

   恢复到时间点1的位置

5. 通过 binlog 来恢复数据

   [mysql@yejf mysql5.7data]$ mysqlbinlog mysql-bin.000023 --start-position=828 --stop-position=1153 --base64-output=DECODE-ROWS -v
   复制

   --start-position=828 为时间点1后的pos位置

6. 查看恢复的数据，已恢复至时间点2

   mysql> select * from tmp;
   +------+------+
   | a    | b    |
   +------+------+
   |    2 | bb   |
   |    3 | cc   |
   复制

基于gtids回放binlog

GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

查看gtid开启状态

mysql>  select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+
复制

开启gtid

修改my.cnf文件

#开启gtid功能
gtid_mode=on 

#强制gtid的一致性 如果开启gtid功能则此参数最好(必须)要开启。slave在做同步复制时,无须找到binlog日志和POS点,直接change master to master_auto_position=1即可,自动根据gtid进行同步数据。
enforce_gtid_consistency=true
复制

重启生效

mysql>  select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+
复制

对场景模拟的内容重新执行一次，执行后的binlog信息如下

在数据库中删除表

mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
复制

对binlog重放至创建表时的状态，对应的gtid为79d58879-c14c-11ec-b660-005056a284c9:2

[mysql@yejf mysql5.7data]$ mysqlbinlog --include-gtids '79d58879-c14c-11ec-b660-005056a284c9:2'  mysql-bin.000001|mysql temp
复制

执行完，但发现表未成功创建，因为当前数据库状态的gtid已经过了，不会再重复执行。

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1198
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 79d58879-c14c-11ec-b660-005056a284c9:1-5
1 row in set (0.00 sec)
复制

此时需要模拟一个新环境，要重置一下当前mysql的gtid,以及备份当前的binlog，因为重置gtid时会清空之前的binlog

[mysql@yejf mysql5.7data]$ cp mysql-bin.000001 mysql-bin.000001.bak
复制

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1048
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 79d58879-c14c-11ec-b660-005056a284c9:1-4
1 row in set (0.00 sec)

mysql> reset master ;
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_purged= '79d58879-c14c-11ec-b660-005056a284c9:1';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
复制

再进行重放

[mysql@yejf mysql5.7data]$ mysqlbinlog --include-gtids '79d58879-c14c-11ec-b660-005056a284c9:1-3'  mysql-bin.000001.bak|mysql temp
复制

再次查看数据表已创建并有数据了

mysql> select * from tmp;
+------+------+
| a    | b    |
+------+------+
|    2 | bb   |
|    3 | cc   |
+------+------+
复制

或者我们可以使用参数--skip-gtids=true
来忽略gtid的影响

mysql> drop table tmp;
Query OK, 0 rows affected (0.01 sec)
复制

[mysql@yejf mysql5.7data]$ mysqlbinlog --include-gtids '79d58879-c14c-11ec-b660-005056a284c9:1-3' --skip-gtids=true  mysql-bin.000001.bak|mysql temp
复制

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1761
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 79d58879-c14c-11ec-b660-005056a284c9:1-7
1 row in set (0.00 sec)
复制

小结

• 基于时间点来恢复的方式，不能精确到具体事务

• 基于position恢复的方式，需要找pos

• 基于gtid恢复的方式， 使用方便， 不用手动指定pos位置 ，通常情况下tid全局唯一，恢复时会忽略已执行的gtid

• 在使用mysqlbinlog恢复时不写binlog 日志，这样可以增加恢复速度

  --disable-log-bin
  复制