使用mysql实现动态数据的双向实时同步，哪种方法最好？

Question

以下是情景。两个位于不同位置的web服务器，各自拥有两个具有相同表结构的mysql数据库。表内数据实时也被期望保持一致。

以下是问题。如果在任一位置的用户同时向相同的表中添加新记录（如下面的前两个表所示），其中每个表的第三条记录均被不同的人同时输入。那么表格中的数据将不再是相同的。如何最好地维护数据始终实时保持一致，就像下面的第三个表格中所示，无论更新在哪里进行？这样，以下图为例，我们不会得到每个表中都有3行的结果，而是双向复制这些新记录，并将它们插入到两个表中，以再次创建具有4列的2个相同的表格。

Server A in Location A
==============

Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
|-----------|
| 3 | John  |
|-----------|

Server B in Location B
==============
Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
|-----------|
| 3 | Peter |
|-----------|


Expected Scenario
===========
Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
| 3 | Peter |
| 4 | John  |
|-----------|

- sobbayi

Answer 1

复制数据库到两个主服务器上并不能获得太多性能提升。然而，如果你正确编写应用程序，则可以实现巧妙的故障转移。

主-主设置基本上与从-主设置相同，但是两个从服务器都已启动，并且每个服务器的配置文件有一个重要的更改。

MySQL 1 主服务器：

auto_increment_increment = 2
auto_increment_offset = 1 

掌握MySQL 2:

auto_increment_increment = 2
auto_increment_offset = 2

这两个参数确保了当两个服务器因某种原因争夺主键时，它们不会重复并破坏复制。任何自增字段默认情况下不是递增1，而是递增2。在一个框上，它将从1开始偏移并运行序列1 3 5 7 9 11 13等。在第二个框上，它将以2为偏移开始沿着2 4 6 8 10 12等运行。从当前的测试来看，自增似乎会取下一个可用的编号，而不是已使用过的编号中最小的那个。
例如，如果服务器1插入前3条记录（1、3和5），当服务器2插入第4条记录时，它将获得键值为6（而不是留下未使用的2）。

设置好后，将它们都作为从属服务器启动。
然后，连接到两台机器并执行命令SHOW SLAVE STATUS以检查两台机器是否正常工作，应该注意到每个框都应该显示“YES”的Slave_IO_Running和Slave_SQL_Running。

然后，当然，在表中创建几条记录，并确保一个框仅插入奇数号的主键，另一个框仅递增偶数号的主键。

然后进行所有测试，以确保您可以在每个框上执行所有标准应用程序，并将其复制到其他框。

一旦开始就相对简单。
但是，正如所提到的，MySQL不建议这样做，并建议您在编写应用程序代码时确保您意识到这种功能。

编辑：我想理论上可能会添加更多的主服务器，如果您确保偏移量正确等等。不过，您更实际地可能会添加一些额外的从属服务器。

- Amadiere

---

为了冗余，两个主节点应该足够。为了负载均衡，可以在两个位置上使用单独的主从设置。但是，使用一个带有远程从节点的主节点，在发生故障时可以手动切换到该节点，这是另一种选择。 - jishi

另外，请确保服务器的server-id具有不同的值，并且replicate-same-server-id设置为默认值0。这可能已经存在，但如果没有，它将循环并遇到错误。 - benlumley

我喜欢这个想法，即让服务器自动增加2，然后编写一个自定义插件来跟踪插入/编辑操作，插件将处理其余部分。因此，如果服务器1插入（1,3,5），插件可以捕获并使用相同的ID将其导出到服务器2，反之亦然，因此不会发生冲突。 - Steve Obbayi

我认为您不需要编写插件来完成此操作，复制应该可以做到。您的连接代码只需尝试负载平衡/连接到工作箱，复制将完成其余操作（并且因为没有关键冲突，它不应该遇到问题）。 - Amadiere

Answer 2

MySQL不支持同步复制，即使它支持，你也可能不想使用它（因为每次事务提交时等待其他服务器同步会影响性能）。

你需要考虑更合适的架构解决方案 - 有第三方产品可以以预定方式进行合并和解决冲突 - 这是唯一的方法。

期望你的架构能够以这种方式运行是幼稚的 - 对于任何数据库，都没有“简单的修复”。

- MarkR

---

它不像你描述的那样等待 - 如上所述，它实际上是两个主从实例。 - benlumley

我是指在假设情况下，MySQL支持同步复制的情况下。 - MarkR

Answer 3

UID是否相同很重要吗？或者您是否考虑过创建一个表或列来映射远程UID到本地UID，并编写自定义同步代码以复制需要进行UID映射的对象，如外键列等？

- JeeBee

---

是的，在两个服务器上UID相同非常重要。它们不必遵循顺序，但在两个服务器上需要是相似且唯一的。无论如何，还是谢谢你。我已经有一个很好的想法了。 - Steve Obbayi

Answer 4

确保您的表格同步的唯一方法是在数据库之间设置双向复制。

但是，MySQL只允许单向复制，因此您无法在此配置中简单地解决问题。

明确一点，您可以“设置”双向复制，但MySQL AB 不鼓励这样做。

- Pierre-Yves Gillier