使用 Rails 3 实现 MySQL 复制的最佳方案？
我们正在考虑为我们的主 MySQL 数据库设置复制，虽然设置复制看起来非常简单，但应用程序的实现似乎有点模糊。
我的第一个想法是设置主从配置和 RW 分割，以及所有写入查询（CREATE、INSERT、UPDATE）转到主站，所有读取查询 (SELECT) 转到从站。读完之后，我们发现，如何使用我们的应用程序实现这一点基本上有两种选择：
- 对所有 MySQL 连接使用独立的中间件层，例如 MySQL 代理 或 DBSlayer。然而，前者处于 Alpha 阶段，后者的文档有限。
- 使用基于 Ruby 的 gem/plugin，例如 Octopus 在框架中实现 RW 分割。
如果我们想采用主从设置，您建议继续做什么？
我的另一个想法是使用主-主配置，但我不确定这种设置的实现。
想法？
We're looking at potentially setting up replication for our primary MySQL database, and while setting up the replication seems pretty straight-forward, the application implementation seems a bit murkier.
My first idea would be to set up a master-slave configuration and RW-splitting, with all write queries (CREATE, INSERT, UPDATE) going to master, and all read queries (SELECT) going to slave. Having read up on it, it seems that there are essentially two options for how to implement this with our app:
- Using an independent middleware layer for all MySQL connections, such as MySQL proxy or DBSlayer. However, the former is in Alpha and the latter has limited documentation.
- Using a Ruby-based gem/plugin, such as Octopus to achieve RW-splitting in the framework.
If we wanted to go with a master-slave setup, what you recommend moving forward?
The other thought I've had was to use a master-master configuration, but am unsure about the implementation of such a setup.
Thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一般来说，您应该在框架中进行读/写拆分，因为只有它才能理解上下文。在 PHP 中，我通过维护两个连接来实现这一点 - 一个用于写入，一个用于读取，并决定您想要在代码中明确使用哪个连接。这样做的原因是它不像按查询类型拆分那么简单。例如，如果您在写入连接上启动一个事务，您希望其中的所有读取也都经过该事务，否则它们将在事务之外，并且可能会获取旧数据或被锁挂起。
除非您的工作负载确实需要大量读取，否则复制不是扩展解决方案，因为复制滞后会导致您得到过时的结果。主-主并没有那么特别——它只是主-从的两个实例，但是当你要求裂脑噩梦时，你不应该错误地尝试向两个主服务器写入数据。
我真正喜欢的配置是将 mmm 与主-主对一起使用。这使得故障转移和冗余对于应用程序来说变得非常简单和透明，而且效果非常好。
Generally you should do your R/W splitting in the framework because only it can understand the context. In PHP I do this by maintaining two connections - one for writes and one for reads and decide which you want explicitly in your code. The reason for this is that it's not as simple as splitting by query type. For example, if you start a transaction on the write connection, you want all the reads inside it to go through that as well, otherwise they will be outside the transaction and will probably get old data or get hung up with locks.
Unless your workload is really read-heavy, replication is not a scaling solution as replication lag will cause you to get out of date results. Master-master is not that special - it's just two instances of master-slave, but you should not make the mistake of trying to write to both masters as you're asking for split-brain nightmares.
The config I really like is using mmm with master-master pair. This makes failover and redundancy really easy and transparent to applications, and it works beautifully.