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.
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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
pt-table-sync can do this: http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html
See also other SO questions and answers mentioning pt-table-sync.
Method 2
I am solving similar problem of syncing two tables constantly.
Among all the scripts (of which most are old) I’ve found this actively developed app (looks promising)
https://github.com/mrjgreen/db-sync
I’ll try it and maybe later I’ll write an example.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0