我最好的 MySQL 同步方法是什么？
由于我们的其他托管提供商的整个数据中心出现故障，我们正在将社交媒体服务转移到单独的数据中心。两次。
这意味着两个网站都需要在某种意义上进行同步——我不太担心页面的代码，这很容易同步，但它们需要具有相同的数据库数据。
从我对 SO 的研究来看，MySQL 复制似乎是一个不错的选择，但是 MySQL 手册，对于横向扩展，说当读取次数远远多于写入/更新次数时最好： http://dev.mysql.com/doc/ refman/5.0/en/replication-solutions-scaleout.html
在我们的例子中，它大约相等。我们现在每天收到大约 200-30 万个请求，并且我们可以快速增长。每个请求都是读请求和写请求。
处理这个问题的最佳方法或工具是什么？
We're moving a social media service to be on separate data centers as our other hosting provider's entire data center went down. Twice.
This means that both websites need to be synchronized in some sense -- I'm less worried about the code of the pages, that's easy enough to sync, but they need to have the same database data.
From my research on SO, it seems MySQL Replication is a good option, but the MySQL manual, for scaling out, says that its best when there are far more reads then there are writes/updates:
http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-scaleout.html
In our case, it's about equal. We're getting around 200-300 thousand requests a day right now, and we can grow rapidly. Every request is both a read and write request.
What would be the best method or tool to handle this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
复制不是即时的，所有写入都必须通过线路发送到远程服务器，因此也需要带宽。只要这对您有用并且您了解后果，那么就不必担心读/写比率。
但是，您确定需要全局复制吗？我们处理数百万个请求，并拥有一个位置，多个 Web 服务器连接到两个数据库。一个数据库是实时数据库，另一个是复制的只读数据库。
我们确实有全球故障转移位置，有些人在任何一天都会连接到这些位置，即使我们的主节点因为互联网问题而启动。不过，数据只是慢慢流入。
如果主节点出现故障，那么每个主体都将按顺序使用全局故障转移位置。因此，如果我们的主节点失效，所有客户都将连接到丹佛。如果丹佛宕机，他们都会连接到哥伦布。
此外，我们的主节点位于两个不同的互联网提供商上，因此一个 ISP 宕机不会导致我们宕机。
Replication isn't instantaneous, and all writes have to be sent over the wire to the remote servers, so it takes bandwidth too. As long as this works for you and you understand the consequences, then don't worry about the read/write ratio.
However, are you sure that you need global replication? We handle millions of requests and have one location, with multiple web servers connected to two databases. One database is the live database, and the other is a replicated read only database.
We do have global fail over locations, and some people connect to these on any day, even if our main node is up because they have Internet issues. The data just trickles in though.
If the main node went down, then every body would be using the global fail over locations, in order. So, if our main node died, all customers would connect to Denver. If Denver went down, they'd all connect to Columbus.
Also, our main node is on two different Internet providers, so one ISP going down doesn't take us down.
两个数据中心之间的连接速度是否足够好？您可以将文件复制到新服务器并将数据库移至那里。然后设置旧服务器，以便它能够连接到另一个 DC 中新服务器的 MySQL 数据库？这当然会比较慢，但根据查询的性质，这是可以接受的。一旦 DNS 或其他任何内容移动/完成，您只需在不再有请求时关闭旧服务器即可。
Is the connection speed between two datacenters good enough? You can copy files to a new server and move database there. And then setup old server so that it will connect to new server's MySQL database in another DC? This will be slower of course, but depending on the nature of your queries it can be acceptable. As soon as DNS or whatever moves/finishes, you just power off the old server when there is no more requests for it.
为了帮助您评估您的选择，您需要考虑灾难恢复场景（即一个数据中心的系统完全丢失）中的需求。
特别是对于这种情况，您可以承受丢失多少数据（恢复点目标 - RPO），以及您需要多快才能启动并运行站点的备用数据中心版本（恢复时间目标 - RTO）。
例如，如果您的 RPO 是无事务丢失并在 5 分钟内恢复，那么解决方案将不同于您可以承受丢失 5 分钟事务和 1 小时恢复的情况。
我想问的另一个问题是您是否使用 SAN 存储？这为您提供了存储级别（SAN 阵列到SAN 阵列）的复制选项，而不是数据库级别的复制（例如MySQL 复制）。
还需要考虑的是数据中心之间的距离（例如，您是否可以在时间上对两个数据库执行同步写入，或者异步复制方法是否更合适）
To help you to assess your options you need to consider what your requirements are in a disaster recovery scenario (i.e. total loss of the system in one data-centre).
In particular for this scenario, how much data can you afford to lose (recovery point objective - RPO), and how quickly do you need to have the standby data-centre version of the site up and running (recovery time objective - RTO).
For example if your RPO is no transactions lost and recovery in 5 minutes, then the solution would be different than if you can afford to lose 5 mins of transactions and an hour to recover.
Another question I'd ask is if you're using SAN storage at all? This gives you options for replication at the storage level (SAN array to SAN array), rather than at the database level (e.g. MySQL replication).
Also to consider is the distance between the data-centres (e.g. timewise can you afford to perform a synchronous write to both databases, or would an asynchronous replication approach be more appropriate)