With MySQL Replication, what level of resilience is possible?

Question

I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing my system, so I'm not here looking for "howto" administrative advice, rather I want to understand the achievable semantics.

So, to simplify my requirements, I have an application that cannot function unless it can read a database. There are two scenarios: first, the common usage, many requests a minute, read the database return an answer. Second, less freaquently, update the database with new data. It's acceptble for the updates to be a few minutes delayed.

So my first thought: Master->Slave

Now the reader can use either Master or Slave, if we lose the Master for a while the reader can work against the Slave.

Sounds simple enough. But ... what about more drastic problems, how manual is recovery? How long does it take? What data is lost?

Take this scenario: Master->Slave. We know that the slave is potantially a little out of synch with the Master. Now suppose we lose the master in a way that means it won't be back any time soon.

Now presumably we need the Slave to become the writable Master, and we'll need a new Slave.

Specific Questions:

1. How much time and effort to make the Slave the Master - I failed to find docs on what to do. I'm guessing that this is pretty easy. Can we make this take-over seamless to client apps? Adjust DNS routing or some such?
2. If we can't now get at the old Master's logs, then we have to accept that some updates to the master will never make it to the new Master, we do have data loss?
3. How much effort to create the new Slave? My guess here is that this is not difficult but potentially takes time. I was trying to imagine reducing this overhead by having two Slaves, and adjusting the replication so that when Slave 1 becomes the new Master, Slave 2 now becomes the slave of that new Master. However given the potential delays in replaction I don't see that ensuring complete consistency is very easy.

Answer 1

I use master slave a lot for speeding up reads at remote offices at the end of slow (512 kbit) connections. My implementation and experience is as follows:

1. Applications are written to read from the slave and write to the master. Reads inside a transaction say for a last_insert_id() need to be done from the master.

2. In the event of an outage (say the broadband link goes down) reads continue from the slave transparently, but writes are not possible for that remote location. Other writes continue from other locations. The slaves keep updating as normal.

3. When the link is restored, the slave reconnects and downloads any updates and synchronises itself, usually transparently.

This has been pretty successful for me where I have lots of people reading and only a few updating, most of whome are at head office.

You can have multiple slaves updating from the master and, if I remember correctly, a slave can also update from another slave so you can have "layers" of slaves.

The following is a useful read. http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html

Answer 2

A lot of the questions you are asking are predicated by use of master-slave replication. Life's a lot simpler if you use master-master replication; master-slave requires detection of a failure on the master and promotion of the slave. While this can be automated to some extent, you also need to think about how you implement reinstating the master.

Regarding data-loss: get over it. Although there are various approaches implemented in different products, even moving transaction control outside of the database cluster, there is still a possibility of losing data. The only practical solution is to design your system so that its possible to identify failed operations (which is good practice as such losses are more often caused by software bugs than system crashes).

Regarding the details of how you implement the system - a lot depends on the OS you are running on, the impact of downtime, the architecture of the application, the nauture of the network connecting the nodes in the application....lots of information which you've not provided.

There are some useful links here

Answer 3

Since the advent of MySQL 5.5, the resilience of MySQL Replication has just kicked up a notch. MySQL 5.5. now has Semisynchronous Replication.

Here is a more simplified explanation of how Semisynchronous Replication is step up from the current Asynchronous Replication:

Prior to MySQL 5.5, Replication would operate like this:

• Master Executes SQL
• Master Records SQL Event in its Binary Logs
• Slave Reads SQL Event from Master Binary Logs
• Slave Stores SQL Event in its Relay Logs via I/O Thread
• Slave Reads Next SQL Event From Relay Log via SQL Thread
• Slave Executes SQL
• Slave Acknowledges Master of the Complete Execution of the SQL Event

Now, with Semisynchronous Replication, this is how it will operate:

• Master Executes SQL
• Master Records SQL Event in its Binary Logs
• Slave Reads SQL Event from Master Binary Logs
• Slave Acknowledges Master of the Receipt of the SQL Event
• Slave Stores SQL Event in its Relay Logs via I/O Thread
• Slave Reads Next SQL Event From Relay Log via SQL Thread
• Slave Executes SQL
• Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

There are also new parameters that make resilience a function of the settings you make.

• rpl_semi_sync_master_enabled : Controls whether semisynchronous replication is enabled on the master.
• rpl_semi_sync_master_timeout : A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
• rpl_semi_sync_master_trace_level : The semisynchronous replication debug trace level on the master.
• rpl_semi_sync_master_wait_no_slave : With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.
• rpl_semi_sync_slave_enabled : Controls whether semisynchronous replication is enabled on the slave.
• rpl_semi_sync_slave_trace_level : The semisynchronous replication debug trace level on the slave.

These settings have a direct bearing on how sensitive replication is in the event of intermittency. When this happens, MySQL Replication will downgrade itself on the fly from Semisynchronous to standard Asynchronous until the intermittency disappears.

Click on this URL to see the steps to setup Semisynchronous Replication.

Now, for your specific questions:

Question 1 : How much time and effort to make the Slave the Master ?

If you have Master-Master Replication is in place, simply point your application away from one master and point it at the other. This you can do IF AND ONLY IF the other master

Question 2 : If we can't now get at the old Master's logs, then we have to accept that some updates to the master will never make it to the new Master, we do have data loss?

Theoretically, no. With MySQL Semisynchronous Replication, the intermittency is self-correcting. However, if communication is down for a long time, you must make sure the Master's binary logs are not rotated out. If the Master has expire_logs_dfays set to 0, binary logs wll not disappear until manually erase them using RESET MASTER (blows away all binary logs) or PURGE BINARY LOGS. That way, once replication is brought back up, the Master's binary logs will be read from where the Slave Last read them. If you are running replication and are really concerned about data loss between slaves, you should also create disk-level replication using DRBD.

Question 3 : How much effort to create the new Slave?

You can definitely use rsync only as a last resort. This would only become necessary if the slave left off at a position in the master's binary logs and that particular binary log either no longer exists or is corrupt.