哪个是使用mysql实时双向同步动态数据的最佳方法 [英] Which is the best way to bi-directionally synchronize dynamic data in real time using mysql

问题描述

这里是场景.两个不同位置的 2 个 Web 服务器，具有两个具有相同表的 mysql 数据库.表中的数据也应该是实时相同的.

Here is the scenario. 2 web servers in two separate locations having two mysql databases with identical tables. The data within the tables is also expected to be identical in real time.

这就是问题所在.如果任一位置的用户同时将新记录输入到相同的表中，如下面的前两个表所示，其中每个表中的第三个记录已由不同的人同时输入.表中的数据不再相同.如下表第三张所示，无论更新发生在何处，保持数据实时保持一致的最佳方法是什么?这样在下面的插图中，新记录不是在每个表中以 3 行结束，而是双向复制，并将它们插入到两个表中以再次创建 2 个相同的表，这次有 4 列?

Here is the problem. if a user in either location simultaneously enters a new record into identical tables, as illustrated in the two first tables below, where the third record in each table has been entered simultaneously by the different people. The data in the tables is no longer identical. Which is the best way to maintain that the data remains identical in real time as illustrated in the third table below regardless of where the updates take place? That way in the illustrations below instead of ending up with 3 rows in each table, the new records are replicated bi-directionally and they are inserted in both tables to create 2 identical tables again with 4 columns this time?

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  |
|-----------|

推荐答案

在两个 master 上复制您的数据库不会获得太多性能.但是，如果您的应用程序编码正确，则会有相当多的故障转移.

There isn't much performance to be gained from replicating your database on two masters. However, there is a nifty bit of failover if you code your application correct.

Master-Master 设置本质上与 Slave-Master 设置相同，但都启动了 Slaves，并对每个盒子上的配置文件进行了重要更改.

Master-Master setup is essentially the same as the Slave-Master setup but has both Slaves started and an important change to your config files on each box.

掌握 MySQL 1:

Master MySQL 1:

auto_increment_increment = 2
auto_increment_offset = 1 

掌握 MySQL 2:

Master 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 等运行.从当前测试来看，自动增量似乎采用下一个空闲数字，而不是之前留下的数字.
例如.如果服务器 2 插入第 4 条记录时，服务器 1 插入前 3 条记录(1 3 和 5)，则其将被赋予 6 的键(不是 2，未使用).

These two parameters ensure that when two servers are fighting over a primary key for some reason, they do not duplicate and kill the replication. Instead of incrementing by 1, any auto-increment field will by default increment by 2. On one box it will start offset from 1 and run the sequence 1 3 5 7 9 11 13 etc. On the second box it will start offset at 2 and run along 2 4 6 8 10 12 etc. From current testing, the auto-increment appears to take the next free number, not one that has left before.
E.g. If server 1 inserts the first 3 records (1 3 and 5) when Server 2 inserts the 4th, it will be given the key of 6 (not 2, which is left unused).

设置完成后，将它们都作为奴隶启动.
然后检查两者是否正常工作，连接到两台机器并执行命令 SHOW SLAVE STATUS 你应该注意 Slave_IO_Running 和 Slave_SQL_Running 应该在每个盒子上都说是".

Once you've set that up, start both of them up as Slaves.
Then to check both are working ok, connect to both machines and perform the command SHOW SLAVE STATUS and you should note that both Slave_IO_Running and Slave_SQL_Running should both say "YES" on each box.

然后，当然，在表中创建几条记录，并确保一个框只插入奇数主键，另一个只增加偶数主键.

Then, of course, create a few records in a table and ensure one box is only inserting odd numbered primary keys and the other is only incrementing even numbered ones.

然后进行所有测试，以确保您可以在每个盒子上执行所有标准应用程序，并将其复制到另一个盒子.

Then do all the tests to ensure that you can perform all the standard applications on each box with it replicating to the other.

一旦开始就相对简单了.
但如前所述，MySQL 确实不鼓励这样做，并建议您确保在编写应用程序代码时注意此功能.

It's relatively simple once it's going.
But as has been mentioned, MySQL does discourage it and advise that you ensure you are mindful of this functionality when writing your application code.

我想如果您确保偏移量正确等等，理论上可以添加更多母版.不过，您可能会更现实地添加一些额外的从属设备.

I suppose it's theoretically possible to add more masters if you ensure that the offsets are correct and so on. You might more realistically though, add some additional slaves.

这篇关于哪个是使用mysql实时双向同步动态数据的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！