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

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.

Answer 2

I've been having this exact same issue, still not entirely sure what to make of it.

Here's my SO Question with a couple other suggestions given.

/mp

Answer 3

@mauriciopastrana: Acually it's not exactly the same issue, because I need the thing for pure fallback (if - as Adam calls it - the worst-case scenario occurs). Both servers are in the same rack, linked with a 1GBit connection and I don't read from both servers at the same time. One server is doing all the work, the other one will just kick in, when the first one dies.

(Actually it will have work, too, but it will hold other databases and primarily handle these, using the other server as fallback if it fails in exchange.)

Answer 4

@BlaM kindasorta, but still the same use; I want to have a mirror server I can tell people to go to if the regular server fails, obviously their data has to be there. Eventually the point is to have people working on both servers side by side and always keep information in sync across the two databases. This brings to light two rough spots:

• one server is down and now you have split brains, how do you resync?
• how can you tell if the mirroring got ALL the data? (I had an issue where a tick threw the slave off and some RANDOM 10mb of data was lost!)

the difference however is, you want to have the two servers on the same rack, right?

Currently, i'm trying this out: http://www.howtoforge.com/mysql_master_master_replication

/mp

Answer 5

@mauriciopastrana: The main difference is that I don't work on both servers simultaneously. I just work on one of them. If that one fails, all data (or at least as much as possible) has to be on the second one so that the applications can "fail over" to the backup machine.

There doesn't need to be a replication from the fallback to the main server in my scenario. I'll just take the full backup of "failover" to set up a new main server.

Also both servers will be in the same rack, but that's just a minor difference ;)

Answer 6

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.