mysql 主从复制 row_my42_Mysql基于ROW格式的主从同步

模拟主从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格式更新，不管主库更新了几个字段，从库都是全行更新。并且主键号与主库保持一致。