说起MySQL binlog，并不陌生，经常听到DBA们提到，可以说是如雷贯耳。

那binlog到底是什么，有什么用途，以及如何使用，本文将围绕这个主题展开。

1.binlog是什么

与MySQL Server相关的日志主要有4种：

• Error log, 用来记录MySQL发生的错误
• General Query log，记录MySQL执行的每条SQL，非常详细，但对MySQL性能有影响，一般不会开启，除非排查问题。
• Slow Query log，即慢查询日志，用来记录执行时间超过一定阀值的SQL信息，这个经常用到。
• Binary log， 即二进制日志，用来记录所有的数据变更操作，例如insert，update，delete等。但并不记录像select 、show这些查询操作。

在binlog中，这些变更操作统称为事件(Event)。

对于支持事务的存储引擎来说，例如InnoDB，只有事务提交后才会记录binlog。至于binlog什么时候刷新到磁盘，这个跟参数sync_binlog 有关。

• 当sync_binlog是0时，会禁用MySQL server的刷盘操作，而是依赖操作系统的刷盘。这种设置，具有最好的性能，但是当机器突然宕机等异常情况时，已提交的事务可能没有记录到 binlog文件中。
• 当sync_binlog是1时，在事务提交前及时将binlog刷新到磁盘。这种设置是最安全的，但可能存在性能影响。在机器突然宕机情况下，binlog中丢失的事务处于Prepared状态，自动恢复机制可以回滚掉这些事务，从而保证binlog中没有事务丢失。
• 当sync_binlog是N时，这里N不是0，也不是1。当N个binlog commit group分组(一个事务算作一组)凑齐时，才会刷新到磁盘。这种设置对性能很友好，但是数据丢失的风险大大提高。

在使用InnoDB时，为了保证数据一致性，推荐的设置是

sync_binlog=1
innodb_flush_log_at_trx_commit=1

innodb_flush_log_at_trx_commit=1表示每次事务提交都会刷新到磁盘。

2.binlog的作用

当数据写入数据库后，会将变更操作写入binlog文件。

binlog的作用主要有两个：

• 主从同步
  在主从同步的过程中，Binlog用于记录主库的数据变更。然后这些记录被主库内的线程发送至从库。从库接收到变更事件后，在从库上重放，完成数据同步。主从同步是提升数据库吞吐的一种方法。
• 数据恢复
  在使用mysqldump或Xtrabackup 进行备份时，只是对一段时间内的数据进行全备。例如，mysqldump每天定时全备，mysqldump -A -B -F >/opt/$(date +%F).sql。 但是如果备份后，数据库突然故障，数据恢复就要依赖binlog了。

3.binlog的格式

binlog有三种格式：

• 基于Statement
  出现最早，记录每条执行的sql语句。
• 基于Row
  记录变更的数据行
• 基于Mixed
  前两者的混合。

由于基于Row的格式，数据准确性高等特点，是目前用的最多的。

4.binlog日志结构

binlog日志文件包括两种：索引文件和具体日志文件。

索引文件用于跟踪日志文件，每行一个日志文件。

默认情况下，索引文件名为{Host名}-bin.index。

日志文件是由一系列事件(Binary Log Events)组成。默认情况下，文件名为{Host名}-bin.NNNNNN。 后缀六个数字，是编号，用于区分不同的日志文件。

日志文件的开头是 Format_description 事件，这个事件记录主库的信息和日志文件的状态。如果主库突然宕机或者重启，会重新创建一个日志文件，并在开头写入Format_description。

当需要创建一个新日志文件时，会写入Rotate事件。Rotate事件会指定下一个日志文件的文件名和读取事件的起始点。

日志文件会把除了Format_description事件和Rotate事件之外的其他变更事件进行分组(Group)。在MySQL中，每一个事务会被分成一组，组中包含了这个事务下执行的所有语句。一些非事务性语句会被单独分成一组，如create和drop语句等。如下图所示。

每个binlog事件由四个部分组成：

• 通用Header：存放事件的基本信息：事件类型和事件数据大小。
• Post Header：存放特定事件类型的相关信息
• 事件实体：存储事件的数据，如执行过的语句和变更的实际数据
• Checksum：MySQL5.6新增的功能，用作检查数据是否损坏。

5.什么时候会新建binlog文件

当遇到以下3种情况时会重新生成一个新的日志文件，文件序号递增：

• MySQL重启后，会生成一个新的日志文件
• 使用flush logs命令
• 当binlog文件大小超过max_binlog_size上限时

6.binlog的开启和关闭

在MySQL配置文件my.cnf中增加log-bin参数即可开启binlog：

[mysqld]
log-bin=mysql-bin

也可指定具体的文件路径：

log_bin = /var/mysql/logs/mysql-bin

修改配置文件后，需要重启MySQL服务。

通过命令行的方式开启和关闭binlog：

• 关闭binlog：
  SET SQL_LOG_BIN=0
• 开启binlog：
  SET SQL_LOG_BIN=1

7.binlog的删除

（1）过期删除

mysql> show variables like 'expire_log_days';
mysql> set global expire_log_days=7;

（2）reset master 会删除所有binlog

（3） reset slave 会删除所有relay log

8.如何查看 binlog

查看日志文件

> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16899 |
+------------------+-----------+
1 row in set (0.00 sec)

或者

>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16899 |
+------------------+-----------+
1 row in set (0.01 sec)

查看日志文件位置

show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000001 |    16899 |              |                  | 10035945-aaaa-11e9-89f3-0022ac533a99:1-67|
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看日志文件内容

binlog日志文件不是文本文件，不能直接通过vim,cat等查看。

需要使用mysql命令或者mysqlbinlog命令。

>show binlog events in 'mysql-bin.000001';
+------------------+-------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos   | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                   |
+------------------+-------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |     4 | Format_desc    |  20201066 |         120 | Server ver: 5.6.36-82.1-log, Binlog ver: 4                                                                                                                                                                             |
| mysql-bin.000001 |   120 | Previous_gtids |  20201066 |         151 |                                                                                                                                                                                                                        |
| mysql-bin.000001 |   151 | Gtid           |  20201066 |         199 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:1'                                                                                                                                                      |
| mysql-bin.000001 |   199 | Query          |  20201066 |         319 | create database if not exists app_test                                                                                                                                                                                 |
| mysql-bin.000001 |   319 | Gtid           |  20201066 |         367 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:2'                                                                                                                                                      |
| mysql-bin.000001 |   367 | Query          |  20201066 |         488 | create table if not exists app_test.abc (a int)                                                                                                                                                                        |
| mysql-bin.000001 |   488 | Gtid           |  20201066 |         536 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:3'                                                                                                                                                      |
| mysql-bin.000001 |   536 | Query          |  20201066 |         776 | GRANT CREATE, SHUTDOWN, SUPER, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, PROCESS, TRIGGER ON *.* TO 'orange_manager'@'10.10.116.88'                                                                    |
| mysql-bin.000001 |   776 | Gtid           |  20201066 |         824 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:4'                                                                                                                                                      |
| mysql-bin.000001 |   824 | Query          |  20201066 |         993 | GRANT SELECT ON `mysql`.`slave_master_info` TO 'orange_manager'@'10.10.116.88'                                                                                                                                           |
| mysql-bin.000001 |   993 | Gtid           |  20201066 |        1041 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:5'                                                                                                                                                      |
| mysql-bin.000001 |  1041 | Query          |  20201066 |        1213 | GRANT SELECT ON `mysql`.`slave_relay_log_info` TO 'orange_manager'@'10.10.116.88'                                                                                                                                        |

或者使用mysqlbinlog命令，在shell终端输入：

mysqlbinlog /var/logs/mysql-bin.000001

类似如下内容：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161020 11:07:29 server id 2  end_log_pos 107   Start: binlog v 4, server v 5.5.51-log created 161020 11:07:29
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
8TQIWA8CAAAAZwAAAGsAAAABAAQANS41LjUxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#161020 11:08:50 server id 2  end_log_pos 181   Query   thread_id=162   exec_time=1 error_code=0
SET TIMESTAMP=1476932930/*!*/;
SET @@session.pseudo_thread_id=162/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

提取指定的binlog日志

例如，insert相关的：

# mysqlbinlog /opt/logs/mysql-bin.000001 | grep insert

或者提取指定位置的binlog日志

# mysqlbinlog --start-position="109" --stop-position="320" /opt/logs/mysql-bin.000001

提取指定位置的binlog日志并输出到压缩文件

# mysqlbinlog --start-position="109" --stop-position="320" /opt/logs/mysql-bin.000001 |gzip  >tmp.sql.gz

提取指定位置的binlog日志导入数据库

# mysqlbinlog --start-position="108" --stop-position="226" /opt/logs/mysql-bin.000001 | mysql -uroot -p

提取指定开始时间的binlog并输出到日志文件

# mysqlbinlog --start-datetime="2020-07-02 22:16:23" /opt/log/mysql-bin.000002 --result-file=tmp02.sql

提取多个binlog日志文件的指定位置的日志

# mysqlbinlog --start-position="108" --stop-position="226" /opt/logs/mysql-bin.000001 /opt/logs/mysql-bin.000002|more

提取指定数据库binlog并转换字符集到UTF8

# mysqlbinlog --database=test --set-charset=utf8 /opt/logs/mysql-bin.000001 /opt/logs/mysql-bin.000002 >test.sql

远程提取日志，指定结束时间

# mysqlbinlog -uroot -p -h192.168.1.186 -P3306 --stop-datetime="2020-06-23 20:30:23" --read-from-remote-server mysql-bin.000033 |more