With Maatkit's mysql-table-sync / mk-table-sync, I can easily synchronize between 2 tables whether it's on same database, different database or even different hosts. Below command will do the trick:
mk-table-sync \The command above will compare table rodin.data on server1 (source) with rodin.dataarchives on server2 (destination). If you want to test comparison (say you are not trust the command) you can remove the --execute option. And if you are sure it's ok then you might want to remove options --verbose --debug --print.
u=user1,p=pwd1,h=server1,D=rodin,t=data \
u=user2,p=pwd2,h=server2,D=rodin,t=dataarchives \
--verbose --debug --print --lock --onlydo ui --execute
The --onlydo ui option mean to tell mysql-table only do update and insert on destination table without delete any record(s). If you want completely synchronize table you might dont use it.
Now, if you try to sync table with different name, it will always prompt you like this.
"Source and destination table have different names. Continue? y/n:"Annoying and you can run it in shell script. So what I do is remark line 782 until 788 of /usr/bin/mk-table-sync on Maatkit version 1316.
One thing still I dont know how is when I tried to uninstall maatkit, it displayed error:
----I leave that open
Uninstall is unsafe and deprecated, the uninstallation was not performed.
We will show what would have been done.
no packlist file found: at /usr/lib/perl5/5.8.5/ExtUtils/Install.pm line
318.
make: *** [uninstall_from_sitedirs] Error 2
----