oreilly.comSafari Books Online.Conferences.


Advanced MySQL Replication Techniques
Pages: 1, 2, 3, 4, 5, 6

Automatic Fail-Over

Now I can tackle the second nuisance of current replication, namely the lack of fail-over mechanisms.

For an experienced administrator, changing the master to a group of slaves is a task that takes just a few minutes. However, while changing a master, you will of course realize that you need to replace the master. Such a realization may come to you at an unfortunate hour, and those few minutes of skilled work may become hours of lost sleep. Add to that the possibility of shaking social relations after leaving a party abruptly with your cell phone ringing, driving home dangerously, and eventually achieving a permanently anxious state of mind.

More experienced administrators have put in place monitoring systems that--on demand--can fire a script for changing the master without human intervention. Those scripts, though, depend on the operating system hosting the server and on the availability of the language and tools used for such scripts.

The news that MySQL 5.1 has introduced an event schedule feature embedded in the database server has surely prompted many people into researching a self-contained solution to the fail-over problem. This article is one such effort.

Start by considering a fail-over in normal (noncircular) replication, because it's easier to follow, and in many cases it may be just the missing piece to perfect happiness.

A simple replication scheme is very often a master, a couple of slaves, and a master candidate, which could simply be one of the normal slaves as well. For simplicity sake, assume the situation in Figure 4.

A simple replication framework
Figure 4. A simple replication framework

To achieve this purpose, you need two recently added features: federated tables, introduced in MySQL 5.0, and the MySQL event scheduler, available from MySQL 5.1.6. Describing these two features extensively would exceed the scope of this article. Fortunately, someone else has already written articles on these subjects, and thus the willing reader can find a gentle introduction to MySQL's federated engine and another about MySQL's event scheduler.

For the purposes of this article, it's enough to say that federated tables are just a link to an existing table in a remote server. The table in the local server has the same structure as the remote one. You must add a special connection string to the creation statement, so that the local database server knows where to look for the data.

The event scheduler is an engine that executes SQL statements in response to the occurrence of temporal events. An event is actually a database schema object that combines a schedule and an action. The schedule defines an optionally recurring point in time, and the action is a SQL statement that must execute according to schedule. Events can be created, altered, enabled, disabled, or dropped through DDL. The database server process maintains event scheduling and execution; it does not depend upon external utilities such as Linux cron jobs or Windows scheduled tasks.

Please note that MySQL 5.1 is, at the writing of this article, still in beta. While the techniques for circular replication are fit for production systems, the features upon which I based the ones I'm now going to show are still likely to have bugs. Before using this technique in production, wait until MySQL 5.1 is released as "generally available." Until then, feel free to experiment.

With these two tools at your disposal, you can build a mechanism to recover the replication system from a master failure. The paradigm is conceptually simple. In the master, in a database replica, is a table called who with just one column. Any other table would do, though. The only purpose of this table is to exist, so that the slaves can link to it through a federated table called master_who (Figure 5).

A heartbeat check for the master
Figure 5. A heartbeat check for the master

# in the master
  server_id int 
) ENGINE=MyIsam 

# in each slave
CREATE TABLE master_who (
  server_id int 
) ENGINE=Federated 

The leading character in this scenario, as shown in Figure 5, is a recurring event, set to occur every 30 seconds, called check master_conn.

create event check_master_conn 
    on schedule every 30 second 
    do call check_master();

It's as simple as setting a rule in crontab (even easier, I daresay). Every 30 seconds, this event calls a stored procedure that tests the status of the master.

create procedure check_master()
    declare master_dead boolean default false;
    declare curx cursor for
        select server_id from replica.master_who;
    declare continue handler for SQLSTATE 'HY000'
        set master_dead = true;

    open curx;
    # a failure to open the cursor occurs here
    # setting the master_dead variable to true

    if (master_dead) then
        stop slave;
        change master to
        start slave;
        alter event check_master_conn disable;
    end if;

To see if the master is alive, this procedure opens a cursor on master_who (a federated table pointing to a remote one). If the cursor fails to open, a handler enabled on the specific state related to such failure will set a variable master_dead to true. When this happens, the procedure will stop the replication, change the master to the waiting candidate, restart the replication, and finally disable the event, which has fulfilled its role (Figure 6).

Detecting a master failure through federated tables
Figure 6. Detecting a master failure through federated tables

Within 30 seconds of the master failure (or less, if you decide to set a lower interval), the old master is forgotten and the new one has happily taken its place (Figure 7).

The slaves have attached to a new master
Figure 7. Following a master failure, the slaves have attached to a new master

The administrator is thus spared from social embarrassment, high-speed driving, and a nervous breakdown.

I can see some hands rising from the audience. What about a secondary fail-over mechanism? What if the candidate master collapses as well? Take a breath and accept the reality. This is a proof of concept that such a mechanism can work and could spare you pain and fatigue. The cleanup after the failure still needs some manual work. You won't need to rush any longer, that's all.

As for secondary (or chain) failures, while they are highly unlikely, you should not dismiss them. My personal take is to put the best effort into reacting to the primary failure and leave secondary failure to human intervention--but there are cases where it is advisable or even necessary to take such failures into account and to put a plan of automatic recovery in place as well. It's just a matter of complexity and, ultimately, of money, because the more time you invest in dealing with redundant fail-over plans, the more it is going to cost.

You, or your company (or your customer, if you are a consultant), will decide how paranoid you can afford to be. The path is set, and you can take the simple solution explained in this article and double (or triple) its level of accuracy according to your policy. Everything is feasible. I know by experience that systems with redundant recovery solutions become complex and expensive. They are also beyond the scope of this article.

Pages: 1, 2, 3, 4, 5, 6

Next Pagearrow

Sponsored by: