如何同步两个 MySQL 表?

2022-01-22 00:00:00 sql synchronization mysql

如果我在我的一台服务器上有一个表(我们称之为 orders),例如,命名为 local.我的另一台服务器也有同一张表,例如,名为 remote.

If I have a table (lets call it orders) on one server of mine, named, for example, local. And I have this same table one another server of mine, named, for example, remote.

我的问题是,同步这两个表的最佳方式是什么?

My problem is, what is the best way to sync these two tables?

如果本地与远程不同,我想要一个替换注册表的解决方案.如果本地表上不存在注册表,则插入注册表.

I would like a solution that replaces a registry if the local is different of the remote one. And insert the registry if it doesn't exist on the local table.

我曾尝试使用 dump 一个类似于此的转储命令,但没有按预期工作:

I had tried using dump a dump command similar to this one, but didn't worked as expected:

/usr/bin/mysqldump --defaults-file=~/my/conf.cnf --skip-opt --skip-add-locks --default-character-set=latin1 --disable-keys --no-create-db --no-create-info --dump-date --compress --quick --replace --where='date > DATE_SUB(NOW(), INTERVAL 1 DAY)' mydb orders >> /backup/myDump

我该怎么做?我怎么能做一个脚本来做到这一点?

How can I do this? How could I do a script to do this?

推荐答案

pt-table-sync 可以这样做:http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

pt-table-sync can do this: http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

另请参阅其他 SO 问题和答案提及 pt-table-sync.

See also other SO questions and answers mentioning pt-table-sync.

相关文章