MySQL replication for fallback scenario

Question

When I have two mysql servers that have different jobs (holding different databases) but want to be able to use one of them to slip in when the other one fails, what would you suggest how I keep the data on both of them equal "close to realtime"?

Obviously it's not possible to make a full database dump every x minutes.

I've read about the Binary Log, is that the way that I need to go? Will that not slow down the fallback server a lot? Is there a way to not include some tables in the binary log - where it doesn't matter that the data has changed?

Answer 1

You may want to consider the master-master replication scenario, but with a slight twist. You can specify which databases to replicate and limit the replication for each server.

For server1 I would add --replicate-do-db=server_2_db and on server2 --replicate-do-db=server_1_db to your my.cnf (or my.ini on Windows). This would mean that only statements for the server_1_db would be replicated to server2 and vice verse.

Please also make sure that you perform full backups on a regular basis and not just rely on replication as it does not provide safety from accidental DROP DATABASE statements or their like.

Answer 2

Binary log is definitely the way to go. However, you should be aware that with MySQL you can't just flip back and forth between servers like that.

One server will be the master and the other will be the slave. You write/read to the master, but can only read from the slave server. If you ever write to the slave, they'll be out of sync and there's no easy way to get them to sync up again (basically, you have to swap them so the master is the new slave, but this is a tedious manual process).

If you need true hot-swappable backup databases you might have to go to a system other than MySQL. If all you want is a read-only live backup that you can use instantly in the worst-case scenario (master is permanently destroyed), Binary Log will suit you just fine.