Sync 2 MySQL Databases

Question

I have 2 MySql databases and I want them to sync. The problem is that I do not have access to the machine on which the "main" database is running, I only have read permissions.

So, I created a secondary database, and I want to create a cron job to synchronize with the master every day at 12:00 (for example).

What is the best way to do this?

Answer 1

Use mk-table-sync from the Maatkit toolkit for MySQL.

From the documentation:

Make slave1 have the same data as its replication master: mk-table-sync --execute --sync-to-master slave1 

In addition to being able to automatically add tables to sync, it has the -dry-run argument, which simply displays the SQL queries necessary to execute it manually. This is great for convincing yourself that you have chosen the correct command line arguments for mk-table-sync and that it will work correctly when you really synchronize tables.

Answer 2

The simplest, but perhaps the one with the most overhead, will just do a mysql dump and re-import the data into a second database.

Answer 3

My solution was to create a python script to reimport all the data. I needed to change the table names anyway for the django application.