24

What is a good way to sync two MySQL databases?

I would like to sync the data in my production website's database to my local development database. Right now, I am just using the PHPMyAdmin interface to export it from one, and then I import it into the other.

These would be user-initiated sync's, not necessarily periodic or automated (something like a one-click sync).

18

Here are a couple of ideas:

  1. Use Replication: http://www.howtoforge.com/mysql_database_replication
  2. Use mysqldump in an import/export script to semi-automate it
  • 1
    Use mysqldump in an import/export script might not be feasible for large databases, e.g if a database is over 1gb, it would not be feasible. – Shoaib Iqbal Dec 10 '15 at 5:55
12

SQLyog Enterprise and Ultimate can do this.

alt text

alt text

SQLyog has a free version although it cannot do the syncing operations unfortunately. The paid versions start at $69 but a 30-day trial is available. For a free solution, replication is your best bet as quickcel outlined, although it requires a bit of configuration.

It now also has feature called 'Visual Data Compare' where data can be compared and synched visually.

enter image description here

  • not a bad answer for WINDOWS users.. – Adam F Feb 21 '15 at 17:04
3

For a free and reliable one-off MySQL synchronization tool, check out Percona Toolkit (formerly Maatkit):

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

2

SQLYog's SJA tool (free for Linux) can do this brilliantly and I have used it for years and years already. All it takes is an XML file generated with the correct login info, your database and tables desired. I could not have done without it.

protected by Community Mar 5 '13 at 19:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.