Basics of MySQL failover (using replication)

[Sarah Sproehnle]

For easy MySQL failover, replication is a great solution. This post will cover the basics of performing a failover in case your master dies.

First, setup one-way replication (or two-way, but don't plan to use both servers for writes at the same time). Next, you'll want to direct all activity, or at least the writes, to the master. If the master dies, there are two major concerns:

1. Redirecting the clients to the slave. There are several ways to handle this, such as Heartbeat or MySQL's JDBC driver.
2. Checking if the slave is caught up. This is trickier. If the master's binary logs are still available, then you can do SHOW SLAVE STATUS on the slave and compare the READ_MASTER_LOG_POS to the master's binary log. For example, if SHOW SLAVE STATUS shows:
   Master_Log_File: localhost-bin.000051
Read_Master_Log_Pos: 605
   Then look at the master's binary log files. A long listing will do the trick:
   
ls -l *bin*
-rw-rw---- 1 mysql mysql 698 2009-10-08 18:24 localhost-bin.000051

   
   

   Note that the binary log has a size of 698 bytes. Yet the slave only read up to byte position 605. So you can manually execute the activity that the slave missed:
   mysqlbinlog --start-position=605 localhost-bin.000051 | mysql -h slave-host-name

   

You may want to manually get the slave up-to-date before failing over. Or you can keep the slave read-only until you catch the slave up, then turn off read-only by doing SET GLOBAL read_only=0;

You may want to setup replication in both directions (sometimes called "master-master" or two-way replication). This is a tricky setup if both servers are actively doing updates. But if you know that only one of the servers is updating at any one time, then it works quite well. Once you've failed-over to the slave, it is now acting as a master. Any updates done on this server can be replicated back to the other server when it comes back up.

This solution is not perfect and may not be suitable for all situations. There is potential data loss (possibly unknown data loss) if the binary logs are no longer available on the master. For some scenarios it is better to have additional means of failover such as MySQL Cluster or DRBD for localized failures.

[Chris]

Not sure I agree with manual intervention here. Heartbeat, keepalive or any other automated mechanism would be optimal for a few reasons:

1. Lets say you don't want to be woken up at some awful time of night or morning.
2. Lets say you are the only DBA and you are in a place with no internet access
3. Lets say you have 50, 100, 1000, 5000 servers... multiply that by the number of probable system failures and you have an administrative nightmare.

Automated fail over is key to limit customer facing downtime plus heartbeat and keepalive are great tools with the right implementation logic.

[Sheeri Cabral]

we've found that fully automated solutions end up having their own problems, and that the best solution for most customers ends up involving manual intervention. Basically, have a master/master setup, where there are service names (separate A records that can be moved). Writes go to only one server (the "primary" server) and there's a standby (the "secondary" server).

If the primary dies, or needs to be taken offline for maintenance, the secondary server can be promoted easily by moving the A records. No application issues, no heartbeat issues (it's finicky software!), and it only takes about a minute to change networking on both servers.