• MySQL binlog都有哪些模式？

    写在前面

    binlog主要用于主从复制和数据恢复，本文就一起来看下其都有哪些模式，以及这些模式都有哪些优点和缺点。

    1：准备

    1.1：打开binlog

    my.ini
[mysqld]
...
log_bin=D:\\program_files\\phpstudy\\PHPTutorial\\MySQL\\binlog\\mysql-bin
server-id=1
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format=ROW
...

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    查看：

    mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | ROW                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
9 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    1.2：环境

    mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.53-log |
+------------+
1 row in set (0.05 sec)

win10

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.3：测试数据

    CREATE TABLE `binlog_demo` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into binlog_demo values(1,1,'2020-07-01');
insert into binlog_demo values(2,2,'2020-07-02');
insert into binlog_demo values(3,3,'2020-07-02');
insert into binlog_demo values(4,4,'2020-07-04');
insert into binlog_demo values(5,5,'2020-07-05');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2：正戏

    2.1：statement

    注意数据恢复为初始状态！！！

    • 修改为statement
    mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set session binlog_format='statement';
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 查看当前binlog的状态
    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     5083 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                                                                               |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.53-log, Binlog ver: 4                                                                                                                                                                                                              |
...                                                                                                                                                         |
| mysql-bin.000002 | 5056 | Xid         |         1 |        5083 | COMMIT /* xid=58 */                                                                                                                                                                                                                                |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    这里需要记住5083这个日志位置，后续我们需要用到。

    • 执行一个delete操作
    mysql> delete from binlog_demo where a>=4 and t_modified<='2020-07-10' limit 1;
Query OK, 1 row affected, 1 warning (0.07 sec)

    • 1
    • 2
    • 查看生成的binlog内容
    C:\Users\Administrator>mysqlbinlog -vv D:\program_files\phpstudy\PHPTutorial\MySQL\binlog\mysql-bin.000002 --start-position=5083
...
delete from binlog_demo where a>=4 and t_modified<='2020-07-10' limit 1
/*!*/;
# at 5301
...

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    这里只保留了最关键的内容，可以看到记录的正式我们执行的sql语句，但是这样的记录在进行主从同步或者是数据恢复时可能会导致数据不一致的情况，为什么？这里就涉及到优化器的索引选择的问题，不同的环境下，优化器选择的索引可能是不同的，而不同的索引选择，则会导致limit 1的结果不同，即最终的结果就是删除了不同的数据，如下可以证明这种现象。

    • 准备环境和数据
    CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` varchar(36) DEFAULT NULL,
  `d` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure t_1003()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,substring(md5(rand()), 10, 30),substring(md5(rand()), 10, 30));
    set i=i+1;
  end while;
end;;
delimiter ;

begin;
call t_1003();
commit;

mysql> alter table t add index idx_c(`c`);
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index idx_d(`d`);
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t\G
*************************** 1. row ***************************
        Table: t
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 100282
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 200
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_d
 Seq_in_index: 1
  Column_name: d
    Collation: A
  Cardinality: 200
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 测试使用不同索引时获取不同的结果
    mysql> select id from t force index(idx_c) where c>'a' and d>'5' limit 1;
+------+
| id   |
+------+
| 4972 |
+------+
1 row in set (0.00 sec)

mysql> select id from t force index(idx_d) where c>'a' and d>'5' limit 1;
+-------+
| id    |
+-------+
| 60716 |
+-------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    binlog statement模式不仅仅有上述的问题，还有使用诸如now()等和当时环境有关的函数时，也会造成数据的不一致。

    2.2：row

    注意数据恢复为初始状态！！！

    • 修改为row
    mysql> set session binlog_format='row';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 查看当前binlog的状态
    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |     1779 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    这里需要记住1779这个日志位置，后续我们需要用到。

    • 执行一个delete操作
    mysql> delete from binlog_demo where a>=4 and t_modified<='2020-07-10' limit 1;
Query OK, 1 row affected, 1 warning (0.07 sec)

    • 1
    • 2
    • 查看生成的binlog内容
    C:\Users\Administrator>mysqlbinlog -vv D:\program_files\phpstudy\PHPTutorial\MySQL\binlog\mysql-bin.000005 --start-position=1779
...
### DELETE FROM `test`.`binlog_demo`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593792000 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
# at 1949
...
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    只保留了最关键的内容，可以看到此时记录的是可以具体定位到某行的sql信息，相当于如下的sql语句:

    DELETE FROM `test`.`binlog_demo`
WHERE
  id=4
  a=4
  t_modified=1593792000

    • 1
    • 2
    • 3
    • 4
    • 5

    这样就避免了因为选错索引导致删除不同的数据的问题，也就是说row会将对应的语句翻译为针对所有影响行的操作，如下测试影响多行的情况：

    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |     1976 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> select id from binlog_demo where id<900;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
4 rows in set (0.00 sec)

mysql> update binlog_demo set a='aa' where id<900;
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 4

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    此时生成的binlog如下可以看到是针对多个行的update：

    C:\Users\Administrator>mysqlbinlog -vv D:\program_files\phpstudy\PHPTutorial\MySQL\binlog\mysql-bin.000005 --start-position=1976
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
...
'/*!*/;
### UPDATE `test`.`binlog_demo`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593532800 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593532800 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`binlog_demo`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593619200 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593619200 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`binlog_demo`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593619200 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593619200 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`binlog_demo`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593878400 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593878400 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
...
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42

    那么针对使用了比如now()函数的更新会怎么记录呢？如下测试：

    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      318 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> update binlog_demo set a=9876 where id=5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    查看binlog如下：

    C:\Users\Administrator>mysqlbinlog -vv D:\program_files\phpstudy\PHPTutorial\MySQL\binlog\mysql-bin.000007 --start-position=318
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
...
'/*!*/;
### UPDATE `test`.`binlog_demo`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1659417981 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=9876 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1659417981 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
# at 494
...
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    可以看到将now()转成了秒:

    在这里插入图片描述

    不仅如此，还set了其他列的值。

    2.3：mixed

    注意数据恢复为初始状态！！！

    MySQL server层智能选择到底是使用statement，还是row，但是这种智能并不能保证百分百的正确，毕竟，在优化器智能的优化sql，选择合适的索引时，也会出现错选索引的情况，而选错索引，最多就是数据查询的慢一点，但是如果是智能选错了binlog的模式，可能就会出现数据一致性的问题，这个结果就比较严重了。所以，在生产环境，还是建议使用row。

    写在后面

    参考文章列表：

    MySql Binlog statement row mixed 三种模式初探 。

  • 相关阅读:
    如何把arguments转换为数组
    聊聊接口性能优化的11个小技巧
    软考 系统架构设计师系列知识点之软件构件（1）
    开放本人早年的Windows核心编程的代码库——供引擎设计参考
    算法学习 day27
    python爬虫requests.get乱码问题
    Helm的安装和使用
    Zongmu AVM车载环视 Android SDK 简介
    1829. 每个查询的最大异或值
    【JavaEE】 spring boot的配置文件详解
  • 原文地址：https://blog.csdn.net/wang0907/article/details/126120638