MySQL双主
对于我当前的项目，我们正在考虑为地理上分离的设置设置双主复制拓扑； 一个数据库位于美国东海岸，另一个数据库位于日本。 我很好奇是否有人尝试过这个以及有什么经验。
另外，我很好奇解决这个问题的其他选择是什么； 我们正在考虑消息队列。
谢谢！
For my current project we are thinking of setting up a dual master replication topology for a geographically separated setup; one db on the us east coast and the other db in japan.
I am curious if anyone has tried this and what there experience has been.
Also, I am curious what my other options are for solving this problem; we are considering message queues.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
请注意您的计划的技术方面：您必须知道 MySQL 官方并不支持多主复制（只有MySQL Cluster提供同步复制支持）。
但至少有一种“技巧”可以使多主复制成为可能，即使使用正常的 MySQL 复制设置也是如此。 请参阅 Patrick Galbraith 的“MySQL 多主复制”，了解可能的解决方案。 我对这种设置没有任何经验，所以我不敢判断这种方法的可行性。
Just a note on the technical aspects of your plan: You have to know that MySQL does not officially support multi-master replication (only MySQL Cluster provides support for synchronous replication).
But there is at least one "hack" that makes multi-master-replication possible even with a normal MySQL replication setup. Please see Patrick Galbraith's "MySQL Multi-Master Replication" for a possible solution. I don't have any experience with this setup, so I don't dare to judge on how feasible this approach would be.
在地理上复制数据库时需要考虑几件事。 如果您这样做是出于性能原因，请确保您的复制模型支持数据“最终一致”，因为可能需要一些时间才能使两个或多个位置的复制保持最新状态。 如果位置之间的吞吐量或响应时间不好，主动复制可能不是最佳选择。
There are several things to consider when replicating databases geographically. If you are doing this for performance reasons, be sure your replication model supports your data being "eventually consistent" as it can take time to bring the replication current in both, or many, locations. If your throughput or response times between locations is not good, active replication may not be the best option.
将 mysql 设置为双主实际上在正确完成的正确场景中确实可以正常工作。 但我不确定它是否非常适合您的场景。
首先，mysql中的双主设置实际上是一个环设置。 服务器 A 被定义为 B 的主服务器，而 B 同时被定义为 A 的主服务器，因此两台服务器既充当主服务器又充当从服务器。 复制的工作原理是传送一个包含 sql 语句的二进制日志，从属服务器在认为合适时插入这些语句，通常是立即插入。 但如果你用本地插入来锤击它，则需要一段时间才能赶上。 顺便说一句，从属插入是顺序的，因此您不会获得多核等的任何好处。
双主mysql的主要用途是在服务器级别上具有自动故障转移的冗余（通常在Linux上使用hearbeat）。 排除 mysql-cluster（由于各种原因），这是 mysql 唯一可用的自动故障转移。 基本双主设置可以在 google 上轻松找到。 心跳的事情需要做更多的工作。 但这并不是您真正要问的，因为它实际上表现为单个数据库服务器。
如果您需要双主设置，因为您总是想写入本地数据库（同时写入两个数据库），则需要在编写应用程序时牢记这一点。 数据库中永远不能有自动递增值，并且当您有唯一值时，必须确保两个位置永远不会写入相同的值。 例如，位置 A 可以写入奇数唯一数字，位置 B 可以写入偶数唯一数字。 原因是您无法保证服务器在任何给定时间都同步，因此如果您在 A 中插入了唯一行，然后在第二个服务器赶上之前在 B 中插入了重叠的唯一行，您将系统损坏。 如果有什么东西首先发生故障，整个系统就会停止。
总而言之：这是可能的，但如果您在此基础上构建商业软件，则需要非常小心。
Setting up mysql as dual master does actually work fine in the right scenario done correctly. But I am not sure it fits very well in your scenario.
First of all, dual master setup in mysql is really a ring-setup. Server A is defined as master of B, while B is at the same time defined as the master of A, so both servers act as both master and slave. The replication works by shipping a binary log containing the sql statements which the slave inserts when it sees fit, which is usually right away. But if you're hammering it with local insertions, it will take a while to catch up. The slave insertions are sequential by the way, so you won't get any benefit of multiple cores etc.
The primary use of dual master mysql is to have redundancy on the server level with automatic fail-over (often using hearbeat on linux). Excluding mysql-cluster (for various reasons), this is the only usable automatic failover for mysql. The setup for basic dual master is easily found on google. The heartbeat stuff is a bit more work. But this is not really what you were asking about, since this really behaves as a single database server.
If you want the dual master setup because you always want to write to a local database (write to both of them at the same time), you'll need to write your application with this in mind. You can never have auto-incrementing values in the database, and when you have unique values, you must make sure that the two locations never write the same value. For example location A could write odd unique numbers and location B could write even unique numbers. The reason is that you're not guaranteed that the servers are in sync at any given time, so if you've inserted a unique row in A, and then an overlapping unique row in B before the second server catches up, you'll have a broken system. And if something first breaks, the entire system stops.
To sum it up: it's possible, but you'll need to tip-toe very carefully if you're building business software on top of this.
由于 MySQL 复制的一对多架构，您必须拥有一个具有多个主服务器的复制环：也就是说，每个主服务器都在循环中从下一个复制。 对于两个人来说，它们互相复制。 早在 v3.23 就支持这一点。
在我以前工作过的地方，我们使用 v3.23 与相当多的客户合作，作为准确提供您所要求的内容的一种方式。 我们使用 Internet 上的 SSH 隧道来进行复制。 我们花了一些时间才让它变得可靠，并且有几次我们不得不将一个数据库复制到另一个数据库（幸运的是，它们都没有超过 2Gb，也不需要 24 小时访问）。 此外，v3 中的复制并不像 v4 中那样稳定，但即使在 v5 中，如果检测到任何类型的错误，它也会停止。
为了适应不可避免的复制延迟，我们重新构建了应用程序，使其不依赖于
AUTOINCRMENT字段（并从表中删除了该属性）。 由于我们开发了数据访问层，这相当简单； 它不是使用 mysql_insert_id() 来创建新对象，而是首先创建新 ID 并将其与行的其余部分一起插入。 我们还实现了存储在 ID 上半部分的站点 ID，因为它们是 BIGINT。 这也意味着当我们的客户想要将数据库分布在三个位置时，我们不必更改应用程序。 :-)它并不是 100% 稳健。 InnoDB 刚刚获得一些知名度，因此我们无法轻松使用事务，尽管我们考虑过它。 因此，当两个对象试图使用相同的 ID 创建时，偶尔会出现竞争条件。 这意味着失败，我们尝试在应用程序中报告这一情况。 但监视复制并在发生故障时修复问题仍然是某人工作的重要组成部分。 重要的是，要在我们变得太不同步之前修复它，因为在某些情况下，数据库在两个站点中使用，如果我们必须重建一个站点，那么很快就很难重新集成。
这是一个很好的练习，但我不会再这样做了。 MySQL 中没有。
Because of the one-to-many architecture of MySQL replication, you have to have a replication ring with multiple masters: that is, each replicates from the next in a loop. For two, they replicate off each other. This has been supported from as far back as v3.23.
In a previous place I worked, we did it with v3.23 with quite a number of customers as a way of providing exactly what you're asking. We used SSH tunnels over the Internet to do the replication. It took us some time to get it reliable and several times we had to do a binary copy of one database to another (fortunately, none of them were over 2Gb nor needed 24-hour access). Also the replication in v3 was not nearly as stable as in v4 but even in v5, it will just stop if it detects any sort of error.
To accomodate the inevitable replication lag, we re-structured the application so that it didn't rely on
AUTOINCREMENTfields (and removed that attribute from the tables). This was reasonably straightforward due to the data-access layer we had developed; instead of it usingmysql_insert_id()for new objects, it created the new ID first and inserted it along with the rest of the row. We also implemented site IDs that we stored in the top half of the ID, because they wereBIGINTs. This also meant we didn't have to change the application when we had a client who wanted the database in three locations. :-)It wasn't 100% robust. InnoDB was just gaining some visibility so we couldn't easily use transactions, although we considered it. So there were race conditions occasionally when two objects tried to be created with the same ID. This meant one failed and we tried to report that in the app. But it was still a significant part of someone's job to watch over the replication and fix things when it broke. Importantly, to fix it before we got too far out of sync, because in a few cases the databases were being used in both sites and would quickly become difficult to re-integrate if we had to rebuild one.
It was a good exercise to be a part of, but I wouldn't do it again. Not in MySQL.