MySQL 两个数据库之间的Mysql数据库同步
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7707859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql database sync between two databases
提问by peedee
We are running a Java PoS (Point of Sale) application at various shops, with a MySql backend. I want to keep the databases in the shops synchronised with a database on a host server.
我们正在各个商店运行 Java PoS（销售点）应用程序，并带有 MySql 后端。我想让商店中的数据库与主机服务器上的数据库保持同步。
When some changes happen in a shop, they should get updated on the host server. How do I achieve this?
当商店发生一些变化时，它们应该在主机服务器上得到更新。我如何实现这一目标？
采纳答案by Book Of Zeus
Replication is not very hard to create.
创建复制并不难。
Here's some good tutorials:
这里有一些很好的教程：
http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/
http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/
http://www.ghacks.net/2009/04/09/set-up-mysql-database-replication/
http://www.ghacks.net/2009/04/09/set-up-mysql-database-replication/
http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html
http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html
http://www.lassosoft.com/Beginners-Guide-to-MySQL-Replication
http://www.lassosoft.com/Beginners-Guide-to-MySQL-Replication
Here some simple rules you will have to keep in mind (there's more of course but that is the main concept):
这里有一些您必须记住的简单规则（当然还有更多规则，但这是主要概念）：
- Setup 1 server (master) for writing data.
- Setup 1 or more servers (slaves) for reading data.
- 设置 1 个服务器（主）用于写入数据。
- 设置 1 个或多个服务器（从站）用于读取数据。
This way, you will avoid errors.
这样，您将避免错误。
For example:If your script insert into the same tables on both master and slave, you will have duplicate primary key conflict.
例如：如果您的脚本插入到主从上的相同表中，您将有重复的主键冲突。
You can view the "slave" as a "backup" server which hold the same information as the master but cannot add data directly, only follow what the master server instructions.
您可以将“从”视为“备份”服务器，它与主服务器保持相同的信息，但不能直接添加数据，只能按照主服务器的说明进行操作。
NOTE: Of course you can read from the master and you can write to the slave but make sure you don't write to the same tables (master to slave and slave to master).
注意：当然，您可以从 master 读取，也可以写入 slave，但请确保不要写入相同的表（master 到 slave 和 slave 到 master）。
I would recommend to monitor your servers to make sure everything is fine.
我建议监控您的服务器以确保一切正常。
Let me know if you need additional help
如果您需要其他帮助，请告诉我
回答by Javier
three different approaches:
三种不同的方法：
Classic client/server approach: don't put any database in the shops; simply have the applications access your server. Of course it's better if you set a VPN, but simply wrapping the connection in SSL or ssh is reasonable. Pro: it's the way databases were originally thought. Con: if you have high latency, complex operations could get slow, you might have to use stored procedures to reduce the number of round trips.
replicated master/master: as @Book Of Zeus suggested. Cons: somewhat more complex to setup (especially if you have several shops), breaking in any shop machine could potentially compromise the whole system. Pros: better responsivity as read operations are totally local and write operations are propagated asynchronously.
offline operations + sync step: do all work locally and from time to time (might be once an hour, daily, weekly, whatever) write a summary with all new/modified records from the last sync operation and send to the server. Pros: can work without network, fast, easy to check (if the summary is readable). Cons: you don't have real-time information.
经典的客户端/服务器方法：不要在商店中放置任何数据库；只需让应用程序访问您的服务器即可。当然，如果设置了VPN 更好，但简单地将连接包装在SSL 或ssh 中是合理的。优点：这是数据库最初的想法。缺点：如果您有高延迟，复杂的操作可能会变慢，您可能必须使用存储过程来减少往返次数。
复制的主/主：正如@Book Of Zeus 所建议的那样。缺点：设置有点复杂（特别是如果您有几家商店），闯入任何商店机器可能会危及整个系统。优点：更好的响应性，因为读操作完全是本地的，写操作是异步传播的。
离线操作 + 同步步骤：在本地和不时（可能是每小时一次，每天一次，每周一次，等等）完成所有工作，用上次同步操作的所有新/修改记录编写摘要，然后发送到服务器。优点：可以在没有网络的情况下工作，速度快，易于检查（如果摘要可读）。缺点：您没有实时信息。
回答by Komoo
SymmetricDS is the answer. It supports multiple subscribers with one direction or bi-directional asynchronous data replication. It uses web and database technologies to replicate tables between relational databases, in near real time if desired.
SymmetricDS 就是答案。它支持单向或双向异步数据复制的多个订阅者。如果需要，它使用 Web 和数据库技术在关系数据库之间复制表，近乎实时。
Comprehensive and robust Java API to suit your needs.
全面而强大的 Java API 以满足您的需求。
回答by Devart
Have a look at Schema and Data Comparison toolsin dbForge Studio for MySQL. These tool will help you to compare, to see the differences, generate a synchronization script and synchronize two databases.
查看dbForge Studio for MySQL中的架构和数据比较工具。这些工具将帮助您比较、查看差异、生成同步脚本并同步两个数据库。
