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

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

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

测试一

主库

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)

从库

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

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)

从库查看

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()函数

主库

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)

从库

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

测试三

主库

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)

从库

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格式。