通过 SQL 数据库复制实现容错
假设中间层服务器采用 3 路复制，后端数据库（MySQL、PostgreSQL 等）采用 3 路复制。用户请求最终会在中间层服务器中创建一些用户数据，我想以一种能够适应故障的方式将其提交到后端数据库。
一种候选尝试解决方案，例如，如果我将数据发送到一个 SQL 数据库并让它将数据复制到其他数据库，那么如果该 SQL 数据库在复制数据之前发生硬盘崩溃，数据就会丢失。
现实世界中使用的容错最佳实践解决方案是什么？
Suppose the middle tier servers are replicated 3 way and the backend database (MySQL, PostgreSQL, etc...) is replicated 3 way. A user request ends up creating some user data in the middle tier servers and I would like to commit this to the backend database in a way resilient to failures.
A candidate attempt solution, for example, if I send the data to one SQL database and have it replicate the data to the other databases then if the one SQL database has the harddrive crash before it can replicate the data, the data is lost.
What is the best practice solution for fault tolerance that is used in the actual real world.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
许多数据库都可以选择集群，作为满足您所描绘的需求的开箱即用的解决方案。我强烈建议使用开箱即用的解决方案，而不是自行解决 - 这种解决方案存在一些令人讨厌的问题，您不想自己解决。
一个典型的例子是主键 - 如果您有 3 个后端服务器，它们可以从中间件服务器接收“在表 x 中创建新记录”指令，并且您想要复制数据，则必须确保不存在冲突主键。如果您使用整数作为主键数据类型，则必须确保数据库服务器 1 不会创建 ID 为 1 的客户记录（如果该 ID 已用于服务器 2 上的记录）。这不是一个不可能的挑战 - 但您构建和测试解决方案很容易花费几周的时间。
您需要考虑的另一件事是，在数据库中断的情况下您的应用程序可以离线多长时间 - 完全不中断，几分钟、几小时或几天。中断窗口越短，集群解决方案就越昂贵和复杂。
Many databases have the option for clustering, as an out-of-the-box solution to the requirement you sketch. I'd strongly recommend using an out-of-the-box solution, rather than rolling your own - there are some nasty problems with this kind of solution which you don't want to solve on your own.
A classic example is primary keys - if you have 3 back-end servers which could receive a "create new record in table x" instruction from the middleware servers, and you want to replicate the data, you have to ensure there are no clashes in the primary key. If you use integers as the primary key data type, you have to make sure db server 1 doesn't create a customer record with ID 1 if that ID is already used for a record on server 2. It's not an impossible challenge - but you could easily burn a few weeks building and testing a solution.
The other thing you need to consider is how long your application can be off-line in the case of a database outage - no outage at all, minutes, hours, or days. The shorter the outage window, the more expensive and complex the clustering solution needs to be.