compare/sync databases from different computers

Question

I have a server online and I have installed MySQL and database A server on it.
I also have mysql installed on my local computer and have a copy of database A on it.
I want to compare the contents of the two databases, and possibly sync them using federated tables.

But after trying out federated table, it seems that I can’t even connect to the server.
The federated table that's supposed to be connected to (an online, not-empty) table from the server is empty.

Am I doing this right? Is this even possible?

If not, can you please suggest any tools/applications that can do this.

Thanks!

Answer 1

You can use Percona's pt-table-sync tool, even if the two MySQL instances are not setup for replication, with this caveat:

pt-table-sync does one-way and bidirectional synchronization of table data. It does not synchronize table structures, indexes, or any other schema objects

So something like this command (if they're not set up in replication..there's other options for the tool if they are):

pt-table-sync --dry-run host1 host2

After you're comfortable with the report, run it:

pt-table-sync --dry-run host1 host2

When using this tool, I would first make a backup of both instances. Always backup in case an issue arises!