High Availability across DCs

splinter · 13th June 2008

I'm looking at putting MySQL on two servers in two separate datacentres, using either Master-Slave Replication if the latency is low enough because it's more reliable, otherwise Master-Master Replication. I'd rather each server be from a different provider too with separate upstreams. The traffic between then will go through an OpenVPN tunnel.

Obviously with this, latency is an issue. What do I really need to look for when choosing the providers?

Should I look to have a VLAN setup between the two?
Will peering between the two be sufficient? Should I ask for traceroutes between the two providers? What sort of latency should I look for?

I've run MySQL Replication for a long time, just not between multiple datacentres.

Thank you for any advice you can give me,
splinter

othellotech · 14th June 2008

>Should I look to have a VLAN setup between the two?

No, you're vpn'ing between the machines

>Will peering between the two be sufficient?

Sufficient - no, but it will improve the performance.

>Should I ask for traceroutes between the two providers?

Of course

> What sort of latency should I look for?

What do you need ? For master/master ~100ms is fine

samb · 16th June 2008

Setting up the replication is the easy bit - much harder is how to handle automated failover and avoid a split-brain condition when the link between the two DCs fails.

Dhosting · 21st June 2008

We had two w2k3 boxes doing master to master MySQL 5 replication between two data centers never an issue, always helps if you set the auto increment offset properly etc.

Also did a MySQL 4.1 master to slave in the event of the master failing we could handle transactions on the slave and essentially switch the roles so the slave became the master etc.

It can generate some super large files though log wise so watch out for it, you can usually clean them out if you set MySQl up right.

Naturally your App needs to look for the backup mysql box if the primary times out etc.

shortword · 18th August 2008

We've done several solutions like that this and in several different configurations. Master - Master works best between DC's but it helps to be able to DO HA on the same subnet. if you can, then programatically set the app to use IP1 all the time and if IP1 isn't available to use IP2. Or round robin them in code and make sure to exclude 1 if it's down.

The other option is to use drbd for /var/lib/mysql/ - drbd works flawlessly over the internet or over the lan which means you can have a live mirrored copy of the DB on an offsite server. Again programatically you can connect to the box that isn't giving errors/is available.

Be carefull of both solutions. If you drop a table, it's gone no matter what solution you go for. So in addition to the above always keep regular backups.