基于statement或mixed格式的主从复制真的安全吗?
2022-05-28 11:42 abce 阅读(69) 评论(0) 编辑 收藏 举报
作为支持工程师,我仍然可以看到不少客户使用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格式。