基于statement或mixed格式的主从复制真的安全吗?

MySQL中的二进制日志格式从5.7开始默认为ROW格式，但仍有许多用户出于各种原因坚持使用STATEMENT或MIXED格式。在某些情况下，修改老的应用程序上运行了多年的东西都有一种犹豫。但在其他情况下，可能存在严重的阻碍，最常见的是在设计不良的模式中缺少主键，这将导致副本出现严重的性能问题。

作为支持工程师，我仍然可以看到不少客户使用STATEMENT或MIXED格式，即使他们已经使用 MySQL 8.0。在许多情况下这没问题，但最近我不得不处理一个非常讨厌的情况，发现不使用ROW格式会导致副本默默地丢失数据更新，而不会引发任何复制错误！ 这是一些非常罕见的边缘用例吗？ 一点也不！ 让我在下面演示一个非常简单的测试用例，以说明最终陷入如此糟糕的情况是多么容易。

测试一

主库

?

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

mysql> select @@binlog_format,@@system_time_zone; +-----------------+--------------------+ | @@binlog_format | @@system_time_zone | +-----------------+--------------------+ | STATEMENT       | BST                | +-----------------+--------------------+ 1 row in set (0.00 sec) ​ mysql> CREATE TABLE `test1` (     ->   `id` int(11) NOT NULL AUTO_INCREMENT,     ->   `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     ->   `a` varchar(30) NOT NULL,     ->   `name` varchar(25) DEFAULT NULL,     ->   PRIMARY KEY (`a`),     ->   UNIQUE KEY `id` (`id`)     -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 1 warning (0.02 sec) ​ mysql> insert into test1 values (null,now(),"test1",0); Query OK, 1 row affected (0.00 sec) ​ mysql> insert into test1 values (null,now(),"test2",0); Query OK, 1 row affected (0.01 sec) ​ mysql> insert into test1 values (null,now(),"test3",0); Query OK, 1 row affected (0.01 sec) ​ mysql> select * from test1; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  1 | 2022-05-22 10:13:37 | test1 | 0    | |  2 | 2022-05-22 10:13:37 | test2 | 0    | |  3 | 2022-05-22 10:13:38 | test3 | 0    | +----+---------------------+-------+------+ 3 rows in set (0.00 sec)

从库

?

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

mysql> select @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | UTC                | +--------------------+ 1 row in set (0.00 sec) ​ mysql> select * from db1.test1; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  1 | 2022-05-22 09:13:37 | test1 | 0    | |  2 | 2022-05-22 09:13:37 | test2 | 0    | |  3 | 2022-05-22 09:13:38 | test3 | 0    | +----+---------------------+-------+------+ 3 rows in set (0.00 sec)

主库update

?

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

mysql> UPDATE test1 SET name = 'foobar', d = CURRENT_TIMESTAMP WHERE a = 'test1' AND d = '2022-05-22 10:13:37'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 ​ mysql> select * from test1; +----+---------------------+-------+--------+ | id | d                   | a     | name   | +----+---------------------+-------+--------+ |  1 | 2022-05-22 10:16:15 | test1 | foobar | |  2 | 2022-05-22 10:13:37 | test2 | 0      | |  3 | 2022-05-22 10:13:38 | test3 | 0      | +----+---------------------+-------+--------+ 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

mysql> select * from db1.test1; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  1 | 2022-05-22 09:13:37 | test1 | 0    | |  2 | 2022-05-22 09:13:37 | test2 | 0    | |  3 | 2022-05-22 09:13:38 | test3 | 0    | +----+---------------------+-------+------+ 3 rows in set (0.00 sec) ​ mysql> pager egrep "Running|SQL_Error" PAGER set to 'egrep "Running|SQL_Error"' ​ mysql > show replica status\G            Replica_IO_Running: Yes           Replica_SQL_Running: Yes                Last_SQL_Error:     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates      Last_SQL_Error_Timestamp: 1 row in set (0.00 sec)

 

测试二

另一个测试，使用UTC_TIME()函数

主库

?

1 2 3 4 5 6 7

mysql> select * from test1 WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  3 | 2022-05-22 10:13:38 | test3 | 0    | +----+---------------------+-------+------+ 1 row in set (0.00 sec)

从库

?

1 2	mysql> select * from test1 WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3; Empty set (0.00 sec)

测试三

主库

?

1 2 3 4 5 6 7 8 9 10 11

mysql> update test1 set name="bar" WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 ​ mysql> select * from test1 where id=3; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  3 | 2022-05-22 22:12:15 | test3 | bar  | +----+---------------------+-------+------+ 1 row in set (0.01 sec)

从库

?

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

mysql> select * from test1 where id=3; +----+---------------------+-------+------+ | id | d                   | a     | name | +----+---------------------+-------+------+ |  3 | 2022-05-22 09:13:38 | test3 | 0    | +----+---------------------+-------+------+ 1 row in set (0.01 sec) ​ mysql > show replica status\G            Replica_IO_Running: Yes           Replica_SQL_Running: Yes                Last_SQL_Error:     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates      Last_SQL_Error_Timestamp: 1 row in set (0.00 sec)

复制忽略了update，且没有报任何复制错误。预计这种特殊情况会在地理分布的数据库环境中经常发生。

因为这些函数对主从复制是不安全的，两个安全操作没有被执行：

·当使用statement格式的时候，在错误日志中没有打印警告信息

·使用mixed格式的时候，复制事件不是以RBR格式记录的，而是使用和查询一样的格式

这个问题很危险，具体可以看bug提交：https://bugs.mysql.com/bug.php?id=107293

 

总结

基于ROW的复制已经成为MySQL中的标准并且是最可靠的一种。它也是唯一一种允许虚拟同步复制解决方案（如 Percona XtraDB Cluster/Galera 和 MySQL Group Replication）的解决方案。

同时，STATEMENT甚至MIXED格式都可能导致数据一致性问题，可能会长时间未被检测到，从而导致最终发生复制错误时很难调查。

最好早点切换成row格式。