MySQL中binlog的三种格式

AsiaYe

发布于 2019-11-06 17:02:23

4.6K0

发布于 2019-11-06 17:02:23

文章被收录于专栏：DBA随笔DBA随笔

MySQL中binlog的三种格式

概念介绍

在MySQL中，我们经常需要打开binlog来观察用户对某一个数据库的操作，binlog中记载着对用户数据库所做的所有修改类操作，例如delete，update，insert等等。binlog一般情况下分为三种格式，分别是row格式、statement格式、mixed格式，下面就这三种格式给出一些解释:

1.Row格式

此格式不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以Row格式的日志内容会非常清楚的记录下每一行数据修改的细节。

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句或者一条alter语句，修改多条记录，则binlog中每一条修改都会有记录，每条记录都发生改变，那么该表每一条记录都会记录到日志中，这样造成binlog日志量会很大。

2.Statement格式

该格式下每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。它相比row模式能节约很多性能与日志量，具体节约的多少取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，考虑到整表删除等一些大量数据操作，ROW格式会产生大量日志，所以总体来讲statement模式会稍微好一些。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

3.Mixed格式

该格式是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，当statement无法完成主从复制的操作时(设计一些函数时)，则采用Row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队Row模式也被做了优化，并不是所有的修改都会以Row模式来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

实践过程

Row模式测试：

为了让整个过程中日志的内容更加清理，每次操作之前我们都使用flush logs来刷一下二进制日志,下面来看例子:

首先我们查看当前服务器的binlog模式，命令如下：

mysql--dba_admin@127.0.0.1:(none) 21:40:06>>show variables like '%format%';
+---------------------------+-------------------+
| Variable_name             | Value             |
+---------------------------+-------------------+
| binlog_format             | ROW               |
| date_format               | %Y-%m-%d          |
| datetime_format           | %Y-%m-%d %H:%i:%s |
| default_week_format       | 0                 |
| innodb_default_row_format | dynamic           |
| innodb_file_format        | Barracuda         |
| innodb_file_format_check  | ON                |
| innodb_file_format_max    | Barracuda         |
| time_format               | %H:%i:%s          |
+---------------------------+-------------------+
9 rows in set (0.00 sec)

我们可以看到binlog_format的格式是Row模式的，此时我们刷新日志文件，进行如下操作：

mysql 21:52:45>>flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql 21:52:48>>select * from yeyz.yyz_test;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql 21:52:59>>update yeyz.yyz_test set age=5;
Query OK, 3 rows affected (0.00 sec)

mysql 21:53:15>>exit

刷新完当前的日志，然后我们看到在yeyz数据库中刚创建了一张表，表名称为yyz_test，我们可以看到表的内容，现在我们把表里面的age字段改为5，然后退出。

类似这种update的操作，都会记录在binlog文件中，我们打开binlog文件的路径：

[dba_mysql /data/mysql_4359/innodblog]$ll
total 2113952
-rw-r----- 1 mysql mysql 1073741824 Mar 28 21:53 ib_logfile0
-rw-r----- 1 mysql mysql 1073741824 May 17  2018 ib_logfile1
-rw-r----- 1 mysql mysql   13632861 Mar 27 18:50 mysqlbin.000056
-rw-r----- 1 mysql mysql      59520 Mar 28 17:32 mysqlbin.000057
-rw-r----- 1 mysql mysql     447192 Mar 28 21:52 mysqlbin.000058
-rw-r----- 1 mysql mysql        554 Mar 28 21:53 mysqlbin.000059
-rw-r----- 1 mysql mysql       2150 Mar 28 21:52 mysqlbin.index

可以看到，最新的一个日志mysqlbin.000059就是我们刚刚flush logs刷新出来的新日志，下面我们通过mysqlbinlog -v的命令，来对binlog文件进行解析，如下：

[root /data/mysql_4359/innodblog]#/usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/bin/mysqlbinlog -vv mysqlbin.000059 > aa.sql
[root /data/mysql_4359/innodblog]#vim aa.sql

需要注意的是，binlog文件是二进制文件，不能直接使用vim打开，里面是一对乱码，看看里里面的内容：

# at 327
#190328 21:53:15 server id 213  end_log_pos 381 CRC32 0xd0afcde6        Rows_query
# update yeyz.yyz_test set age=5
# at 381
#190328 21:53:15 server id 213  end_log_pos 433 CRC32 0x8b6bb088        Table_map: `yeyz`.`yyz_test` mapped to number 190
# at 433
#190328 21:53:15 server id 213  end_log_pos 523 CRC32 0x0a9c1405        Update_rows: table id 190 flags: STMT_END_F

BINLOG '
y9GcXB3VAAAANgAAAH0BAACAAB51cGRhdGUgeWV5ei55eXpfdGVzdCBzZXQgYWdlPTXmza/Q
y9GcXBPVAAAANAAAALEBAAAAAL4AAAAAAAEABHlleXoACHl5el90ZXN0AAIDAwADiLBriw==
y9GcXB/VAAAAWgAAAAsCAAAAAL4AAAAAAAEAAgAC///8AQAAAAQAAAD8AQAAAAUAAAD8AgAAAAQA
AAD8AgAAAAUAAAD8AwAAAAQAAAD8AwAAAAUAAAAFFJwK
'/*!*/;
### UPDATE `yeyz`.`yyz_test`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `yeyz`.`yyz_test`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `yeyz`.`yyz_test`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
# at 523
#190328 21:53:15 server id 213  end_log_pos 554 CRC32 0x423ce908        Xid = 632321
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

我们可以看到，从第3行开始，就进入了update的日志，日志内容中包含每一行的更改前和更改后的值，我们可以通过这些更改前后的值的信息，来写一些DML闪回的脚本。这样，即使我们不小心更新错了，也有补救的机会，最常见的一种误操作就是update某些数据，然后忘记写where条件，这时候，我们就可以把这段日志截取下来，从而恢复到原来的样子。

statement模式测试：

看完了Row模式，此时我们把日志的格式改为statement，在该的时候需要注意，如果出现了下面的错误：

mysql--dba_admin@127.0.0.1:(none) 22:36:51>>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 22:37:06>>select * from yeyz.yyz_test;
+------+------+
| id   | age  |
+------+------+
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
+------+------+
3 rows in set (0.01 sec)

mysql--dba_admin@127.0.0.1:(none) 22:37:17>>update yeyz.yyz_test set age=6;
ERROR 1665 (HY000): Unknown error 1665

这个错误是由于事务的隔离级别不合适导致的，我们只需要将默认的隔离级别改为RR即可，操作如下：

mysql--dba_admin@127.0.0.1:(none) 22:45:15>>show variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 22:48:42>>set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 22:49:07>>show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql--dba_admin@127.0.0.1:(none) 22:49:53>>select * from yeyz.yyz_test ;
+------+------+
| id   | age  |
+------+------+
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 22:50:53>>update yeyz.yyz_test set age=6;
Query OK, 3 rows affected (0.00 sec)

此时我们发现已经修改成功了，再次查看新的日志文件，可以发现，日志文件中的内容如下：

# at 334
#190328 22:51:03 server id 213  end_log_pos 434 CRC32 0x0569f619        Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1553784663/*!*/;
update yeyz.yyz_test set age=6
/*!*/;
# at 434
#190328 22:51:03 server id 213  end_log_pos 465 CRC32 0x688386c7        Xid = 35
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

也就是说，只记录了一些行信息。

Mixed模式测试：

首先更改binlog_format的值：

mysql--dba_admin@127.0.0.1:(none) 22:51:03>>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

mysql--dba_admin@127.0.0.1:(none) 23:00:27>>set global binlog_format='MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 23:00:48>>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 23:00:53>>flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 23:01:09>>select * from yeyz.yyz_test;
+------+------+
| id   | age  |
+------+------+
|    1 |    6 |
|    2 |    6 |
|    3 |    6 |
+------+------+
3 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:(none) 23:01:19>>update yeyz.yyz_test set age=7;
Query OK, 3 rows affected (0.00 sec)

进行更新后打开日志:

# at 334
#190328 22:51:03 server id 213  end_log_pos 434 CRC32 0x0569f619        Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1553784663/*!*/;
update yeyz.yyz_test set age=6
/*!*/;
# at 434
#190328 22:51:03 server id 213  end_log_pos 465 CRC32 0x688386c7        Xid = 35
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

我们发现，默认情况下是使用statement模式进行的日志保存，由于测试数据库暂时没有搭建从库，主从复制时候的Row模式切换并没有测试，有兴趣的同学可以下面测试一把，就可以看到Mixed格式是在statement模式和Row模式之间进行切换的。

到底应该选用哪一种模式？

关于这三中格式的binlog，我们在使用的时候到底应该使用哪一种？我的观点如下：

如果我们的磁盘空间和服务器性能比较OK的情况下，尽量使用Row模式，因为这种模式能够最大程度的保证安全性，虽然产生的日志量很多，但是当你误删数据的时候，你就会感受到binlog给你带来的温暖。
当我们对一些不太重要的业务库(例如一些log库)进行数据主从复制的时候，尽量使用statement来执行，因为它的速度快，日志量小，而且不牵扯使用函数，是简单的数据同步。
如果有一些场景需要尽量保证性能，但是又没有十分严格的要求时，我们可以设置为Mixed格式，它可以在statement和Row之间进行切换，保证了业务的写入性能。
最后一点，在RC和RU隔离界别下，不能使用statement格式的binlog日志。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-03-28，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server