如何同步具有相同结构和不同服务器的两个MySQL表 [英] How can I synchronize two MySQL tables that have same structures and different server

问题描述

我有两个ubuntu服务器，并安装了Mysql 4.0，ServerA和ServerB

i has two ubuntu server,and installed Mysql 4.0, ServerA and ServerB

我想将ServerA的mysql表同步到ServerB(将ServerATable复制到ServerBTable，保持ServerATable和ServerBTable相等)

i want to synchronize ServerA's mysql table to ServerB (copy ServerATable to ServerBTable, keep ServerATable and ServerBTable equals)

该怎么做?

(shell脚本，java或linux mysql工具)

(shell script,or java, or linux mysql tools)

谢谢:)

推荐答案

有一些选择.一种是在MySQL中设置复制，该复制会自动复制数据在服务器之间来回移动，并自动保持同步，并且随着数据的飞速移动，粒度为几秒钟.缺点是您必须至少将主服务器暴露在网上才能允许TCP连接.

There's a few options. One is to set up replication within MySQL, which will automatically copy data back and forth between the servers and keep them synchronized automatically, with a granularity of a few seconds as the data flies around. Downside is you have to expose at least the master server to the net to allow TCP connections.

在外部，您可以在服务器A上进行常规的mysqldump，复制到服务器B，加载到mysql中，然后就可以使用了.这将具有您运行转储/复制/加载序列的任何时间间隔的粒度.不利的是，mysqldump在工作时会锁定表.如果您的数据库很大，则在转储进行时将被锁定在serverA的表之外，而在数据加载时将被锁定在serverB之外.而且，与转储相比，加载转储要慢得多，因为mysql在加载期间会进行各种内部整理(密钥更新，表元数据更新等).

Externally you can do regular mysqldumps on server A, copy to server B, load into mysql, and off you go. This will have a granularity of whatever time interval you run the dump/copy/load sequence in. Downside is that mysqldump locks tables as it's working. If you have a large database, you'll be locked out of serverA's tables while the dump progresses, and the locked out of serverB as the data's loaded. And loading a dump is much slower than doing the dump in the first place, as mysql does all kinds of internal housekeeping (key updates, table metadata updates, etc...) during the load.

对于第三个选项，您可以编写一个同步器，该同步器比较两个数据库之间的行并根据需要进行更新.但是，然后将一台服务器的内容放到另一台服务器上，并进行逐行操作.另外，还必须处理任何相关的外键/子关系更新，这将使处理变得很麻烦.到那时，最好改用mysqldump选项.

For the third option, you could write a synchronizer that compares rows between the two databases and does updates as necessary. However, then you're slurping the contents of one server over to the other and doing row-by-row operations. Plus having to handle any dependent foreign key/child relation updates as well, and this will get complicated in a hurry. At that point you might be better off using the mysqldump option instead.

当然，您应该将MySQL升级到更现代的版本. v4.x非常过时.您应该至少运行5.0版本，最好是5.1版本.

Of course, you should upgrade your MySQL to something more modern. v4.x is very outdated. You should be running at least a 5.0, preferably a 5.1 version instead.

这篇关于如何同步具有相同结构和不同服务器的两个MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！