模拟主从update事务，从库跳过部分update事务后，再次开始同步的现象
主库
mysql> select * from dbamngdb.isNodeOK; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 1 | +----+---------------------+-------+ 1 row in set (0.00 sec) mysql> update dbamngdb.isNodeOK set count = count + 100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
从库
将从库的数据重置，相当于丢失了一部分事务
mysql> update dbamngdb.isNodeOK set count = 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from isnodeok; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 1 | +----+---------------------+-------+ 1 row in set (0.03 sec)
主库
mysql> update dbamngdb.isNodeOK set count = count + 100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dbamngdb.isNodeOK; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 201 | +----+---------------------+-------+ 1 row in set (0.00 sec)
从库
mysql> select * from isnodeok; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 201 | +----+---------------------+-------+ 1 row in set (0.03 sec)
当主库再次update时，从库的数据就跟主库一致了，这是因为ROW格式下，从库的update是全行更新，不管主库更新的有多少个字段。
关于所有列皆更新，实验如下：
从库修改字段
mysql> update dbamngdb.isNodeOK set update_time=now(); Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from isnodeok; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 19:05:18 | 201 | +----+---------------------+-------+ 1 row in set (0.02 sec)
主库修改另外的字段
mysql> select * from dbamngdb.isNodeOK; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 201 | +----+---------------------+-------+ 1 row in set (0.00 sec) mysql> update dbamngdb.isNodeOK set count = count + 100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dbamngdb.isNodeOK; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 301 | +----+---------------------+-------+ 1 row in set (0.00 sec)
再次查看从库，我们预期从库的update_time字段值变化为主库的字段值
mysql> select * from isnodeok; +----+---------------------+-------+ | id | update_time | count | +----+---------------------+-------+ | 1 | 2019-11-08 18:51:48 | 301 | +----+---------------------+-------+ 1 row in set (0.03 sec)
结果与预期一致，这就是ROW格式更新，不管主库更新了几个字段，从库都是全行更新。并且主键号与主库保持一致。