26

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).

vonhogen
  • 2,359
  • 4
  • 29
  • 39

4 Answers4

19

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
quickcel
  • 4,779
  • 2
  • 24
  • 25
  • 2
    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 05:55
13

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

Mathew
  • 103
  • 2
John T
  • 163,373
  • 27
  • 341
  • 348
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

crishoj
  • 341
  • 2
  • 3
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.

Cristian Cotovan
  • 161
  • 5