- 背景
- MySQL中的日志分类
- Binlog
- 验证binlog日志是否开启
- 开启和关闭binlog日志
- 如何选择性的开启binlog
- 开启binlog日志后的效果
- binlog日志文件记录的格式
- binlog日志文件的大小
- 如何查看binlog日志的内容
- 如何使用binlog日志做增量恢复
- 清理binlog日志文件
- 最后
- Binlog
背景
MySQL中到底有多少种log？你是否经常听别人说什么binlog、binary log、redo log、undo log、回滚日志、重做日志、中继日志、错误日志、慢查询日志、一般日志等等。这些概念都分别是什么？MySQL生成这些日志的目的都是什么？
接下来我们一起来分析一下MySQL中到底有哪些log吧。
MySQL中的日志分类
MySQL中经常遇到的日有以下几种：
- Binlog：全称是binary log，就是我们平时说的二进制日志，
- relay log：中继日志
- Redo log：重做日志
- Undo log：回滚日志
- Slow query log: 慢查询日志
- General log：一般日志
- Error log：错误日志
接下来，我们一个一个分别了解一下它们。今天这篇文章主要是分析一些binlog日志，其他的日志会在后续的文章中逐个分析。
Binlog
binlog是大家学习MySQL中经常遇到的，也是必须要了解的。它在MySQL中所起到的作用可是举足轻重的。
binlog在MySQL中默认是不开启的，因为开启它需要消耗一定的性能。如果我们需要使用到binlog则可以选择性的开启它。比如我们需要做主从同步的集群环境的时候，master主库上面的binlog是必须要打开的。这也是binlog存在的一个重要的功能，为主从同步提供支撑。
在做增量恢复的时候，binlog日志也起着至关重要的作用。比如：我们在今天凌晨零点的时候有一次全量的备份，然后今天上午有一个误操作把数据库给删除了。此时如果我们想要恢复数据库，则可以先恢复今天凌晨零担的全量备份文件，然后再基于还原后的数据库，再使用凌晨零点之后到当前删除数据库的时间点为止，这一段时间内的binlog做增量恢复，这样的数据库在还原之后，就是我们一个完整的数据库。
验证binlog日志是否开启
验证我们的MySQL数据库是否开启binlog可以使用show variables like '%log_bin%';命令，下面分别是在一个没有开启binlog和开启binlog日志的MySQL实例上验证的效果。
- 下面是在没有开启binlog日志的MySQL实例上验证的效果：
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |/*这里显示OFF，表示没有开启binlog日志。*/
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.02 sec)
- 下面是在开启了binlog日志的MySQL实例上验证的效果：
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |/*这显示ON，表示已经开启binlog*/
| log_bin_basename | /var/lib/mysql/mysql-bin |/*这是binlog日志文件存放的目录和名称*/
| log_bin_index | /var/lib/mysql/mysql-bin.index |/*这是binlog日志文件的索引文件目录和名称*/
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.02 sec)
需要我们注意其中的两个参数：
- log_bin_basename：这里是配置的binlog日志文件的存放的目录和名称，可以从上面查看的结果中看出，这里的没有配置binlog日志文件的后缀名。因为binlog日志是一个一直滚动追加的日志文件，当超过一个指定的默认值（通常是1GB大小）之后，他会自动切分为一个新的binlog日志文件。文件的后缀名就是它滚动增长的标识符。它是一个有规律增长的序列，000001,000002,000003...999999
- log_bin_index：这个参数配置的是binlog日志的索引文件存储目录和名称，这里面记录了MySQL目前所有的有效的binlog日志文件列表。每一个binlog日志文件一行，按照binlog日志生成的时间顺序，依次排列下来。
开启和关闭binlog日志
如果我们想开启binlog日志，该如何开启呢？我们可以通过修改MySQL的my.cnf配置文件来开启。在MySQL的my.cnf配置文件中增加如下的配置即可开启binlog：
[mysqld]
# 启用二进制日志
log-bin=mysql-bin开启binlog之后，它会记录我们的哪些操作呢？我们平时对MySQL的操作有增、删、改、查，这些操作都会被记录在MySQL的binlog日志文件中吗？其实它只会记录我们的增、删、改的操作，查询的操作它是不会记录的。也就是说，它只记录对数据库有变更的操作，如果没有变更则不会记录在binlog日志文件中。
对于平时我们的一些DDL操作也会被记录在binlog日志文件中。比如我们删除一个表、增加一个列、创建一个索引、删除一个索引等这样的操作，也会被记录在binlog日志文件中。
我们知道，一个MySQL数据库实例下面可以有多个schema，也就是我们平时说的数据库。对于一个实例下面所有的数据库的操作的SQL语句，它们不会分开来存储在binlog日志文件中的。在binlog日志文件还没有切换新的文件的时候，在一定时间段内，所有的schema的操作语句都会记录在同一个binlog日志文件中。
如果我们要关闭binlog日志功能，可以把上面的参数log-bin=mysql-bin使用#注释掉这一行配置，然后重启MySQL实例就可以关闭binlog了。
如何选择性的开启binlog
如果一个MySQL数据库实例开启了binlog日志功能，那么能否做到只针对某几个schema（数据库）的操作才记录binlog日志，对其他schema（数据库）的操作不记录呢？对于有些主从同步的业务场景下可能存在这样的业务需求。MySQL中可以使用两个参数binlog_do_db和binlog_ignore_db来满足这样的需求，如果不配置这两个参数，默认是会记录所有数据库的变更操作到binlog日志中。具体配置可以在my.cnf配置文件中参考如下的配置：
[mysqld]
# 此参数表示只记录制定数据库的二进制日志，下面配置的是只记录mydb1,mydb2,mydb3,mydb4着四个数据库的binlog日志，其他数据库的binlog日志不记录。
binlog_do_db=mydb1,mydb2,mydb3,mydb4
# 此参数标示不记录指定的数据库的二进制日志，下面的配置是忽略MySQL自带的几个数据库的binlog日志。
binlog_ignore_db=mysql,sys,information_schema,performance_schema上面我们已经可以通过参数来为指定的数据库开启记录binlog日志，那么我们再细粒度分析一下，对于一个已经配置为需要记录binlog日志的schema（数据库）来说，难道对这个数据库所有有变更操作的SQL，都要被记录在binlog中吗？有没有办法让某一些操作不被记录在二进制日志中呢？
答案是肯定的。我们可以通过参数sql_log_bin参数来控制某些有变更的SQL语句是否可以被记录在binlog中。该参数在配置开启binlog之后是默认为on的状态，表示所有有变更的SQL语句都会被记录在binlog中。如果不想记录某些SQL语句，只要在执行对应的SQL语句之前将这个参数设置为off就可以避免接下来执行的SQL语句被记录在binlog日志中。
下面是使用sql_log_bin参数来控制是否记录某些SQL语句到binlog日志中的实验示例，因为我没有在my.cnf配置文件中配置binlog_do_db和binlog_ignore_db参数，所以，对于MySQL中所有的数据库的变更操作都会别记录在binlog日志文件中。实验步骤和结果如下:
- 先检查当前session级别的sql_log_bin参数的值。
- 将参数sql_log_bin的值设置为OFF。
- 查看设置的结构是否为OFF。
- 当前binlog日志文件的名称和位置。
- 创建一个表t。
- 再次检查binlog日志文件的名称和位置，看是否发生变化。
- 查看binlog日志文件的具体内容，查看是否有记录前面创建表t的DDL语句。
- 把当前session级别的sql_log_bin参数设置为ON。
- 检查设置的结果是否为ON。
- 检查当前binlog日志的文件名称和位置。
- 创建一个表a。
- 再次检查当前binlog日志的文件名称和位置，看是否发生变化。
- 检查binlog日志的具体内容，看是否包含创建表a的DDL语句。
mysql>
mysql> show variables like 'sql_log_bin';/*查看当前sql_log_bin参数的值为on,表示会记录所有当前session操作的SQL语句。*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set session sql_log_bin=off;/*关闭当前session记录binlog的功能*/
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_log_bin';/*查看后，确实关闭的session的binlog记录功能。*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show master status;/*查看当前binlog日志的文件名称和位置。*/
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 632 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> create table t(id int);/*创建了一个表t，这个DDL语句应该不会被记录在binlog日志中。*/
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;/*再次查看当前binlog日志的文件名称和位置，和创建表t之前的文件名称和位置是相同的，就是说binlog日志并没有向后滚动增加。*/
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 632 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> show binlog events in 'mysql-bin.000013';/*查看binlog日志文件中内容，确实没有看到关于创建表t的SQL语句，证明我们的操作是正确的。*/
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000013 | 123 | Previous_gtids | 100 | 154 | |
| mysql-bin.000013 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000013 | 219 | Query | 100 | 367 | use `feng`; create table test(id int primary key auto_increment, a varchar(16), b int) |
| mysql-bin.000013 | 367 | Anonymous_Gtid | 100 | 432 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000013 | 432 | Query | 100 | 504 | BEGIN |
| mysql-bin.000013 | 504 | Table_map | 100 | 555 | table_id: 115 (feng.test) |
| mysql-bin.000013 | 555 | Write_rows | 100 | 601 | table_id: 115 flags: STMT_END_F |
| mysql-bin.000013 | 601 | Xid | 100 | 632 | COMMIT /* xid=30 */ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> set session sql_log_bin=on;/*开启当前session，记录binlog日志的功能。*/
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'sql_log_bin';/*验证是否真正的开启，下面结果为ON，表示确实开启了session级别记录binlog日志的功能。*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show master status;/*查看当前binlog日志文件的名称和位置。*/
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 632 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> create table a(id int);/*创建一个表a，这个创建表a的DDL语句应该被记录在binlog日志文件中。*/
Query OK, 0 rows affected (0.05 sec)
mysql> show master status;/*创建完表a之后，再次查看binlog日志文件的名称和位置，发现位置比之前增加了一些。从632变成了793。*/
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 793 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000013';/*查看binlog日志文件中的内容。发现里面确实记录了创建表a的DDL语句*/
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000013 | 123 | Previous_gtids | 100 | 154 | |
| mysql-bin.000013 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000013 | 219 | Query | 100 | 367 | use `feng`; create table test(id int primary key auto_increment, a varchar(16), b int) |
| mysql-bin.000013 | 367 | Anonymous_Gtid | 100 | 432 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000013 | 432 | Query | 100 | 504 | BEGIN |
| mysql-bin.000013 | 504 | Table_map | 100 | 555 | table_id: 115 (feng.test) |
| mysql-bin.000013 | 555 | Write_rows | 100 | 601 | table_id: 115 flags: STMT_END_F |
| mysql-bin.000013 | 601 | Xid | 100 | 632 | COMMIT /* xid=30 */ |
| mysql-bin.000013 | 632 | Anonymous_Gtid | 100 | 697 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000013 | 697 | Query | 100 | 793 | use `feng`; create table a(id int) /*这里就是记录的表a的DDL语句*/ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
11 rows in set (0.01 sec)参数sql_log_bin在某些时候很有好用，比如在主从同步读写分离的情况下，为了避免在执行某些DDL语句而导致主从同步延迟的问题发生，我们会在主和从上分别执行我们的DDL语句，这个时候，我们是不希望在主上执行的DDL语句被记录在binlog日志文件中，因为我们不想通过组从同步的方式在从节点上执行DDL，我们想自己再从节点上执行DDL，此时参数sql_log_bin就可以满足我们的这个需求。只要我们在主节点上执行DDL之前，把参数sql_log_bin设置为OFF，然后再去执行我们的DDL语句，这样这个DDL语句就不会被记录在binlog日志中，也就不会被同步到从节点上。
开启binlog日志后的效果
开启binlog日志文件之后，我们可以在对应的日志文件目录下面看到binlog日志，如下所示:
root@test:/var/lib/mysql# pwd
/var/lib/mysql
root@test:/var/lib/mysql# ls -lsr mysql-bin*
4 -rw-r----- 1 mysql mysql 133 Dec 30 18:00 mysql-bin.index
4 -rw-r----- 1 mysql mysql 154 Dec 30 18:00 mysql-bin.000007
4 -rw-r----- 1 mysql mysql 177 Dec 30 18:00 mysql-bin.000006
4 -rw-r----- 1 mysql mysql 177 Dec 30 17:56 mysql-bin.000005
8 -rw-r----- 1 mysql mysql 5703 Dec 30 17:39 mysql-bin.000004
4 -rw-r----- 1 mysql mysql 177 Dec 29 15:00 mysql-bin.000003
4 -rw-r----- 1 mysql mysql 177 Dec 28 15:45 mysql-bin.000002
4 -rw-r----- 1 mysql mysql 177 Dec 28 15:45 mysql-bin.000001
root@test:/var/lib/mysql# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
root@test:/var/lib/mysql#从上面我们可以看到，binlog日志文件的索引文件中记录的是当前有效的binlog日志文件列表。
除了去服务器对应的日志文件目录上去查看binlog有哪些之外，我们可以直接在MySQL客户端工具或命令行中使用show binary logs;命令来查看当前有哪些binlog日志文件。如下所示：
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000011 | 177 |
| mysql-bin.000012 | 201 |
| mysql-bin.000013 | 154 |
+------------------+-----------+
7 rows in set (0.00 sec)如果是在一个主从同步、读写分离集群环境中的主节点上，我们还可以使用下面的命令来查看binlog日志文件有哪些，已经查看当前使用的binlog日志文件名称和日志的偏移量是多少。
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
| mysql-bin.000011 | 177 |
| mysql-bin.000012 | 201 |
| mysql-bin.000013 | 793 |
+------------------+-----------+
7 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 793 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)binlog日志文件记录的格式
我们知道在MySQL主从同步的时候，从库是靠读取主库的binlog日志的内容来同步主库的数据的。那么在binlog日志文件中，记录的SQL语句的方式可能会导致从库中的数据和主库不一致。
举例说明一下：假如我们在主库上有一个表t1，他们表结构如下，此时我们向表t1中执行一个insert语句：
mysql> desc t1;
+-------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+----------------+
| c1 | int(11) | NO | PRI | NULL | auto_increment |
| c2 | varchar(16) | YES | | NULL | |
| c3 | decimal(16,16) | YES | | NULL | |
+-------+----------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into t1(c2,c3) select @@hostname, rand();
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------------+--------------------+
| c1 | c2 | c3 |
+----+------------+--------------------+
| 1 | test.mysql | 0.6296363022454421 |
+----+------------+--------------------+
1 row in set (0.01 sec)上面的insert语句我们在binlog日志中该如何记录？如果我们就记录insert into t1(c2,c3) select @@hostname, rand();，此时这个SQL在从库上被执行的时候，会获取从库的主机名称，并且执行随机函数rand()的时候生成的随机数很主节点上生成的随机数有99.9%的可能性是不相同的。此时从库上面的数据内容和主库上就出现了主从不一致的情况。所以这里我们要讨论一下binlog日志在记录的时候，到底应该如何记录我们的SQL语句。
binlog日志在记录的时候，分为3种格式。分别如下：
- statement：statement-based replication，简称 SBR，基于SQL语句的复制。5.7以前默认是这个格式。
- row：row-based replication，简称 RBR，基于行的复制。5.7版本之后采用row作为默认的格式。
- mixed：mixed-based replication，简称 MBR，混合模式的复制，由MySQL自行决定某个SQL是应该采用statement的方式来记录还是该采用row的方式来记录。
binlog日志格式通过参数binlog_format来控制，查看当前binlog日志的格式使用如下语句：
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |/*这样是MySQL5.7版本中默认的binlog日志的记录格式。*/
+---------------+-------+
1 row in set (0.01 sec)针对我们上面提到的那个insert语句，执行insert into t1(c2,c3) select @@hostname, rand();这个SQL语句，在不同的日志格式下，binlog中日志记录的内容到底是什么样子的呢？我们来做下实验验证一下。在实验之前，我们需要做一些准备操作，以便于我们进行分析不被其他因素影响。
mysql> truncate table t1;/*清空表t1原来的数据。*/
Query OK, 0 rows affected (0.04 sec)
mysql> flush logs; /*执行该命令的目的是为了让binlog执行切换日志文件的操作，新开一个binlog日志文件，用于存储我们接下来所有的实验操作锁执行的日志记录。这样便于我们进行分析*/
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;/*查看当前的日志文件名称的日志的偏移量。*/
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 | 154 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)执行上面的操作后，我们开始实验部分。我们会分别在三种日志模式下面，分别向表t1中插入数据。然后再去看下这些插入的SQL语句会在binlog中怎么样记录。
- statement格式的binlog日志模式下，实验步骤如下所示：
mysql> set binlog_format='statement';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)
mysql> insert into t1(c2,c3) select @@hostname, rand();
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 2
mysql> select * from t1;
+----+------------+--------------------+
| c1 | c2 | c3 |
+----+------------+--------------------+
| 1 | test.mysql | 0.9992758436121753 |/*在binlog日志格式为statement的时候，插入的数据*/
+----+------------+--------------------+
1 row in set (0.01 sec)- row格式的binlog日志模式下，实验步骤如下所示：
mysql> set binlog_format='row';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
mysql> insert into t1(c2,c3) select @@hostname, rand();
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------------+--------------------+
| c1 | c2 | c3 |
+----+------------+--------------------+
| 1 | test.mysql | 0.9992758436121753 |/*在binlog日志格式为statement的时候，插入的数据*/
| 2 | test.mysql | 0.5065404861295042 |/*在binlog日志格式为row的时候，插入的数据*/
+----+------------+--------------------+
2 rows in set (0.01 sec)- mixed格式的binlog日志模式下，实验步骤如下所示：
mysql> set binlog_format='mixed';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)
mysql> insert into t1(c2,c3) select 'abcd', 0.11;/*不使用函数，直接指定c2和c3两列的值。*/
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------------+--------------------+
| c1 | c2 | c3 |
+----+------------+--------------------+
| 1 | test.mysql | 0.9992758436121753 |/*在binlog日志格式为statement的时候，插入的数据*/
| 2 | test.mysql | 0.5065404861295042 |/*在binlog日志格式为row的时候，插入的数据*/
| 3 | test.mysql | 0.5348749305446399 |/*在binlog日志格式为mixed的时候，插入的数据*/
| 4 | abcd | 0.1100000000000000 |/*在binlog日志格式为mixed的时候，插入的数据*/
+----+------------+--------------------+
4 rows in set (0.01 sec)实验完成之后，我们来查看binlog日志中的内容，日常有很多输出，为了便于查看，这里我把我们所关心的内容给筛选出来，其他无关紧要的内容就不贴在这里了，我们所关系的内容如下所示：
root@test:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000014
......此处省略很多输出
########################################################################################
# 下面是在binlog日志格式为statement的时候，插入的第一行数据的时候所记录的binlog日志。
# 我们执行的SQL语句为：insert into t1(c2,c3) select @@hostname, rand();
########################################################################################
insert into t1(c2,c3) select @@hostname, rand()
......此处省略很多输出
########################################################################################
# 下面是在binlog日志格式为row的时候，插入的第二行数据的时候所记录的binlog日志。
# 我们执行的SQL语句为：insert into t1(c2,c3) select @@hostname, rand();
########################################################################################
### INSERT INTO `feng`.`t1`
### SET
### @1=2
### @2='test.mysql'
### @3=0.5065404861295042
......此处省略很多输出
########################################################################################
# 下面是在binlog日志格式为mixed的时候，插入的第三行数据的时候所记录的binlog日志。
# 我们执行的SQL语句为：insert into t1(c2,c3) select @@hostname, rand();
########################################################################################
### INSERT INTO `feng`.`t1`
### SET
### @1=3
### @2='test.mysql'
### @3=0.5348749305446399
......此处省略很多输出
########################################################################################
# 下面是在binlog日志格式为mixed的时候，插入的第四行数据的时候所记录的binlog日志。
# 我们执行的SQL语句为：insert into t1(c2,c3) select 'abcd', 0.11;
########################################################################################
insert into t1(c2,c3) select 'abcd', 0.11
......此处省略很多输出
root@test:/var/lib/mysql#下面的我截图的输出，请参考：
第一行数据的日志内容为：
第二行数据的日志内容为：
第三行数据的日志内容为：
第四行数据的日志内容为：
通过以上实验，我们可以知道。
- statement格式
- 优点：由于是记录的原始的SQL语句，不需要记录具体数据的变化内容，所以binlog日志不大，占用磁盘少。主从同步的时候，减少了磁盘的I/O竞争。从库的触发器函数等都会被在从库上调用，因为同步过到从库上面的是原始的SQL语句。
- 缺点：可能导致主从的数据不一致。可能导致主从延迟，因为在主库上执行一个DDL语句后，这个DDL语句执行的过程可能需要5mins，如果在主库只完成后，这个DDL语句同步到从库上执行，也是需要5mins的。而这个时候，因为这个DDL没有执行完成，所以，其他的在主库执行完成DDL之后的SQL语句就不能再从库上马上被执行，需要等待从库的DDL执行完成后才可以执行从主库上同步过来的后续的SQL语句。从库在重放主库的binlog日志文件的时候，是顺序执行的。不能多线程并发执行。
- row格式
- 优点：由于是记录的每一行的数据变化的内容，所以数据在从库上执行的时候，是严格按照主库的数据内容来的。它不会导致主从数据不一致的现象发生。同时，避免了主从延迟的现象。因为没有对应的锁在从上占用，它只是在从库上应用主库同步过来的数据变化的内容。所以速度很快。
- 缺点：由于是记录的具体数据变化的内容，所以binlog日志的提交会很大，占用很多磁盘空间。比如修改表中某一个列的值执行加1的操作，那么row格式下的binlog，会把表符号条件的所有数据行都加1后记录在binlog中，有多少行数据满足where条件，就记录多少行的数据变动记录在binlog日志中。所以它会占用很多磁盘空间，并且在主从同步的时候有可能会有很多的IO资源竞争。同时如果在主库上有触发器的操作，在从库上不会触发对应的触发器。因为它不会在从库上执行原始的SQL语句。而是直接应用原始SQL语句在主库执行后所产生的数据内容。
- mixed格式下
- 它是基于以上两种格式的综合体。当一个SQL语句有不确定性的时候，比如包含随机函数的时候，它会采用row格式记录，对于一个确定性的SQL它会采用statement格式的方式记录，以便减少binlog日志的大小。
我们推荐使用row格式的binlog日志，这也是MySQL5.7之后的版本默认的日志格式。但是考虑到这种格式的日志，占用磁盘会比较大，MySQL还有一个优化的方式来优化这样格式下的日志，尽量减少日志的大小。这个可以优化的参数就是：binlog_row_image。这个参数可以控制row格式下面的binlog日志在记录的时候，到底需要记录哪些变更的字段内容。它的取值有三种：
- full：这个是默认值，如果不配置参数binlog_row_image，那就是模式采用改值。此时会记录有数据变动的数据行中的所有的字段内容在binlog日志中。
- minimal：此时只记录真正需要变动的字段的内容，对于没有做任何变更的字段，不记录在binlog日志中。
- noblob：不记录blob字段在binlog中，其他字段，都记录在内。
举例说明一下，如果一个10行10列的表，其中第1个列是自增主键，第2~9是普通的字段，第10个列是一个blob类型的字段。那么当我们执行一个update语句，只修第5行数据中第5个列的值时，
- binlog_row_image的值为full的时候，此时会把第5行数据中其他没有被修改的9个列的值也一起记录在binlog中。
- binlog_row_image的值为minimal的时候，此时只会记录第5行数据中第1列主键和第5个被修改的列的值在binlog中。
- binlog_row_image的值为noblob的时候，此时会记录第5行数据中除了第10列blob字段之外的其他9个列的值在binlog中。
所以，在row格式下面的binlog，推荐使用binlog_row_image为minimal的组合。这就是我们经常在MySQL的配置文件my.cnf中看到的row + minimal组合的根源所在。
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'binlog_row_image';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| binlog_row_image | MINIMAL |
+------------------+---------+
1 row in set (0.01 sec)binlog日志文件的大小
前面我们提到MySQL的binlog日志文件默认为1GB一个日志文件，这个默认值的大小是通过参数max_binlog_size来控制的。最小值为4096byte，最大值为1GB，默认是它的最大值1GB大小一个binlog日志文件。
可以通过如下命令进行查看binlog日志文件的大小：
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)
mysql> select 1073741824/1024/1024/1024 as 'Size in GB';
+----------------+
| Size in GB |
+----------------+
| 1.000000000000 |
+----------------+
1 row in set (0.01 sec)但是有时候，我们去看我们的binlog日志文件的时候，发现它的大小并不是都遵循1GB大小一个binlog日志文件。有的大于1GB，有的远远小于1GB，有的约定于1GB。这是为什么呢？
- binlog日志文件远远小于1GB：当我们重启MySQL数据库实例的时候，MySQL会重新开启一个新的binlog日志文件，而不是基于上一次的日志文件继续追加binlog日志。另外，当我们对MySQL执行mysqldump备份数据的时候，当我们备份完成后通常也会对binlog进行一个新的切分。表示从该binlog文件之后的文件是此次备份之后所产生的二进制日志。在做增量恢复的时候很重要，它可以提示我们应该从哪个binlog文件开始做增量恢复。当我们执行flush logs命令的时候也会切分新的binlog日志文件。
- binlog日志文件大于1GB：当我们执行一个比较大的事务操作的时候，而记录当前事务的binlog日志刚好1GB大小了，那么此时binlog是不会切分一个新的日志文件的，因为一个事务的binlog日志，必须要求在同一个binlog日志文件中，所以它不会被拆分到另外一个新的binlog日志文件中，所以此时的binlog日志文件会出现大于1GB的情况。
- binlog日志文件约等于1GB：这是MySQL正常的情况下产生的binlog日志文件的大小。当MySQL实例一直运行，并且没有大事务在数据库实例中，MySQL会在一个binlog日志文件到达1GB大小的时候，去切换为一个新的binlog日志文件继续记录。
如何查看binlog日志的内容
binlog日志文件中记录的内容是使我们的赠、删、改和各种DDL操作。我们该如何看里面的内容呢？它是一个二进制日志文件不是普通的平面文本文件，不能直接通过cat、more、vi等命令来查看。我们需要使用特殊的命令去查看它里面的内容，在MySQL的命令行终端中，可以使用show binlog events in命令进行查看具体的日志内容，如下所示：
mysql> show binlog events in 'mysql-bin.000013';
除了在命令行终端查看binlog日志文件的内容之外，MySQL还给我们提供了mysqlbinlog命令来解析binlog日志文件中的内容。让其可以转换为我们可以看的普通文本文件。关于mysqlbinlog的命令解析binlog日志的常用参数解释如下，你可以使用mysqlbinlog --help命令查看所有它所有支持的参数和使用帮助文档。这里只是我们只列出几个平时经常使用的参数。
- --base64-output：该参数用来控制binlog日志解析的时候，是否输出base64编码的内容。它的取值范围有：auto、decode-rows、never。
-
- auto是默认值，表示由mysqlbinlog自己觉得该如何显示解析的日志内容；
- never表示禁用base64编码输出，但是该参数仅适用于binlog日志不是row格式时才适用，如果binlog的日志格式为row格式，在解析这样的binlog的时候，不能时候用--base64-output=never这样的组合。
- decode-rows,把日志中的base64编码的内容过滤掉，我们经常会使用这个参数去查看binlog日志中的内容。不使用--base64-output(也就是说--base64-output=auto)和使用--base64-output=decode-rows 的区别如下：
- -d, --database=name：该参数用于指定数据库名称，指定该参数之后，会把待解析的binlog日志文件中所有关于指定的这个数据库的操作日志解析出来。如果不指定该参数，则会把待解析的binlog日志文件中对所有的数据库的操作日志都列出来。当我们需要分析指定的数据库中的操作日志的时候，这个参数很有用。
- -h, --host=name：如果我们需要去解析远程服务器上面的binlog，需要使用这个参数来指定远程服务器的IP地址。
- -u, --user=name：如果我们需要去解析远程服务器上面的binlog，需要使用这个参数来指定远程服务器数据库的登录用户名。
- -p, --password[=name]：如果我们需要去解析远程服务器上面的binlog，需要使用这个参数来指定远程服务器数据库的登录密码。
- -P, --port=#：如果我们需要去解析远程服务器上面的binlog，需要使用这个参数来指定远程服务器数据库的端口号。
- -R, --read-from-remote-server：如果我们需要去解析远程服务器上面的binlog，需要使用这个参数来表明要解析远程服务器的binlog。
- -v, --verbose：把对数据库的各种操作解析为可以读的SQL语句。
-
- 如下就是不使用-v和使用-v参数的效果
-
- 如下是使用-vv参数和-v参数的对比效果，
- -vv比-v增加了列的注释信息。
- -j, --start-position=#：从指定日志偏移的位置点开始解析binlog日志。
- --stop-position=#：一般和--start-position=#配合使用，解析到指定的位置点结束。
- --start-datetime=name：从指定的时间点开始解析binlog日志。
- --stop-datetime=name：一般和--start-datetime=name配合使用，解析到指定的时间点结束。
结合上面的参数解释，我们下面给出几个经常使用的解析binlog日志的示例供大家参考：
- 因为binlog日志文件在记录的时候，不会按照schema来分别记录，多个schema的操作日志是记录是按照时间的先后顺序记录在一起的。而当我们需要从binlog日志文件中只获取某一个schema（也就是我们平时说的数据库）下面所有的日志时，我们可以使用-d参数。使用方式如下所示：
# 从binlog日志文件中获取指定数据库的SQL语句，其他数据库SQL语句不会解析出来。
mysqlbinlog --base64-output=decode-rows -vv \
-d my_db \
mysql-bin.000013 > mysql-bin.000013.my_db.log- 有时候我们需要按照binlog日志的日志点来解析binlog的内容，可以使用--start-position和--stop-position参数组合来指定日志点的起始和结束位置，来获取指定日志点中间的日志内容，使用方式如下所示：
# 获取指定位置开始到指定位置结束的binlog日志内容。
mysqlbinlog --base64-output=decode-rows -vv \
--start-position 222 \
--stop-position 666 \
mysql-bin.000013 > mysql-bin.000013.222-666.log- 如果我们明确的知道我们想要的日志内容的起始时间和结束时间，那么我们可以使用--start-datetime和--stop-datetime参数组合来获取指定时间段内的binlog日志内容。使用方式如下所示：
# 获取指定时间段内的binlog日志。
mysqlbinlog --base64-output=decode-rows -vv \
--start-datetime='2020-12-12 10:10:20' \
--stop-datetime='2020-12-12 11:30:30' \
mysql-bin.000013 > mysql-bin.000013.log- 当然我们还可以根据上面的几种方式继续自由的组合使用，例如把-d参数和--start-datetime、--stop-datetime结合使用，可以获取指定的数据库下面指定的时间段内的binlog日志内容，使用方式如下所示：
# 获取指定数据库的指定时间段内的binlog日志。
mysqlbinlog --base64-output=decode-rows -vv \
-d my_db \
--stat-datetime='2020-12-12 10:10:20' \
--stop-datetime='2020-12-12 11:30:30' \
mysql-bin.000013 > mysql-bin.000013.my_db.log如何使用binlog日志做增量恢复
如果我们找到了指定的binlog日志，需要使用这些binlog日志进行数据库的增量恢复，我们可以使用下面的这种方式：
mysqlbinlog -d my_db mysql-bin.000001 | mysql -uroot -proot -h127.0.0.1
mysqlbinlog -d my_db mysql-bin.000002 | mysql -uroot -proot -h127.0.0.1
....
mysqlbinlog -d my_db mysql-bin.00000n | mysql -uroot -proot -h127.0.0.1需要注意的是，在恢复的时候，需要严格按照binlog日志的生成顺序来依次执行。先生成的binlog日志文件，需要先被执行，顺序不可乱，乱了的话，恢复是失败，即便不失败，那恢复后的数据库中的数据也会存在逻辑上的不一致性。
清理binlog日志文件
当我们开启了binlog日志文件之后，随着时间的增长，我们的binlog日志文件会越来越多，在我们的日志目录下会积累很多的binlog日志。在外面平时做好了日常有规律的备份之后，我们只保留近几天的binlog就可以了，较为久远的binlog可以被清理掉，从而节省我们的磁盘空间，避免我们的磁盘容量不足的报警邮件。那么我该如何清理binlog日志文件呢？
第一种方式：直接在日志目录下面执行rm命令删除久远的binlog日志，然后用vi命令修改我们的日志索引文件。这样可以释放我们的磁盘空间。但是我从来没有这样做过，因为我担心会导致异常的问题出现，避免组从同步任务的中断等等。所以我们还是采用MySQL自带的一些管理命令来删除binlog日志文件吧。
第二种方式，我们可以登录MySQL命令行界面，使用如下的SQL命令来删除binlog日志：
-- 删除所有binlog日志，新日志编号从头开始
reset master;
-- 删除mysql-bin.000005之前所有日志
purge master logs to 'mysql-bin.000005';
-- 删除2020-11-11 11:11:11之前产生的所有日志
purge master logs before '2020-11-11 11:11:11';
-- 清除5天之前所有的binlog，只保留最近5天的binlog日志文件
purge master logs before date_sub(now(), interval 5 day);第三种方式，我们可以在开启binlog日志文件的时候，就通过参数expire_logs_days参数来配置好binlog日志文件的过期时间，等待对应的日志文件过期之后，它就会被自动被清理，而不用我们手动去清理。
在没有配置binlog日志文件过期参数的时候是这样的：
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)配置的具体方式是在my.cnf配置文件中配置参数expire_logs_days，具体可以参考如下配置：
[mysqld]
# 保留近10天的binlog日志。
expire_logs_days=10该参数的默认值为0，最大值为99。当没有配置这个参数的时候，它就是默认值0，表示不会自动清理MySQL的binlog日志。上面我们配置为10，表示10天之前的binlog日志会被自动清理，只保留最近10天的binlog日志。
配置之后的结果是这样的：
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 10 |
+------------------+-------+
1 row in set (0.02 sec)注意：虽然配置好了binlog日志过期的时间，对于过期的日志并不是马上就会自己被删除，需要以下几种情况才会触发自动删除过期日志的操作：
- 重启MySQL数据库实例。
- binlog日志文件的大小达到参数max_binlog_size的限制之后；
- 手动执行flush logs命令，这个命令不要在业务高峰期执行，避免和业务应用争抢磁盘I/O。
下面我们来看一个示例：
实验的执行步骤是这样的：
- 查看当前有效的binlog日志文件有哪些。目前看图中是有2天的有效日志文件。
- 设置日志有效期为1天，仅保留近1天的日志文件。
- 查看设置后的结果。
- 查看当前的系统时间，为2021-01-01，那么在这之前的所有binlog日志都应该被删除。
- 执行flush logs命令，清除过期的binlog日志。
- 查看清除后的结果，发现只有2021-01-01的日志文件存在。在此之前的日志文件都别删除了。
最后
MySQL的binlog日志就介绍到这里了，后续我会把其他几种log依次介绍，敬请关注。
扫一扫
8442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
