keep 2 mysql dbs identical on two machines

Question

I have two mysql databases on two machines. Let's say first is production db and the second one is identical clone. My php app uses production db for default. But I need to have both dbs identical at the same time. It means I need solution for cases when production db is unavailable (for example connection error) so I just manually set second one and my app runs as usual. I would like to make it "failure resistant".

How should I do this? I think of making dump every minute but it is not a good solution when db is complex with many data...

Answer 1

These answers assume that standard replication is not an option for you, for whatever reason:

The following options are existing methods for manual data-sync that are well known, and would be good when combined when wrapped into a scripting language like bash etc. for a cronjob as needed with some logic to specify specific tables as needed, guarantee it is safe to run them in light of load, etc. on a production box.

Option 1: pt-table-sync

The pt-table-sync tool from the Percona MySQL toolkit allows for master-master, master-slave sync on demand in an existing replication scheme. Or you can use it to sync two servers that do not have any relationship.

Docs here from Percona

Following the example, for one way sync'ing.

pt-table-sync --execute h=sourcehost1,u=msandbox,p=msandbox h=desthost d=yourdb t=yourtables

Additionally the following features exist:

1. Dry Run Mode (--dry-run) - Program will connect, plan the sync, analyze conflicts and tell you how it would resolve the sync. This is key to making sure you use this powerful tool the right way.
2. Conflict analysis - see how the data compare - feed this back into your script to catch potential issues, or don't perform the sync to save time if there isn't a difference.

As I understand, a master-slave relationship need not exist necessarily - but the sync is more efficient if it does exist since more efficient checksum algorithms can be used for comparing the data.

Option 2: Hot/Streaming Backups with XtraDb Alternatively, you could use something like the free Percona XtraBackup in it's host streaming mode to keep a backup file in sync, and restore to your dev box as needed.

XtraBackup makes MySQL hot backups for all versions of Percona Server, MySQL, MariaDB, and Drizzle. It performs streaming, compressed, and incremental MySQL backups.

Option C: LVM Snapshots LVM snapshots are probably not the best option for a production box if you need to take them at any sort of frequency due to the brief locking/IO issues, but nonetheless here you go: MySQL Backups Using LVM Snapshots

All of these tools move data either one way, or bi-directionally - as such a thorough understanding of how to employ them is critical to avoid data loss.