Introducing the MySQL community-driven Replication Monitoring Tools

If you are using MySQL replication, you know how hard is to monitor it properly. You have a wide choice of commercial and free tools, all of which check the health of your replication system from the outside. A few years ago, I wrote an article advocating a self-monitoring and self-healing replication system, using new features in MySQL 5.1. At the time, there were some missing technology pieces to make this project feasible. Now the pieces exist, and you can create your own self monitoring replication system.

Hartmut rules!

It started during FrOSCon, when I discussed with Hartmut Holzgraefe the practical application of a plugin that he wrote some time ago. One of the missing pieces for the internal replication monitoring is the ability of reading replication values into variables. Currently, you can see the replication status with SHOW MASTER STATUS and SHOW SLAVE STATUS. Unfortunately, you can't manipulate the values from these commands inside a stored routines. And thus there is no way of measuring the health of a replication system without the help of external programming languages.
This means that, even if you manage to detect a master failure, you can't create a CHANGE MASTER STATUS with the right parameters.
During our FrOSCon talks, Hartmut released a small Launchpad project, the MySQL replication status INFORMATION_SCHEMA plugin, which implements two INFORMATION_SCHEMA tables for MASTER and SLAVE STATUS. Using these extensions, you can get single values inside a stored routine.

The Replication Monitor is born

From this starting point, the Replication Monitoring Tools become possible.
The project is young, and it only contains a proof-of-concept monitoring tool (see below), but given enough time and help, it can be expanded into a full fledged system.
The roadmap has a long list of possible features, some of which are quite ambitious

The project is open. I need ideas, manpower, testers, to implement all the intended features. This is a project from the community to the community.
The principle that you have seen several times when downloading MySQL server fully applies. If you have time, you can save money and help yourself and the community at the same time.

Replication Monitor in action

The proof of concept implements a table on the master, where the slaves write their status, by means of federated tables.
Each slave uses a federated table to the master INFORMATION_SCHEMA.MASTER_STATUS to compare its status with the master,a nd a second federated table to write its status to the master table. The result is that you can read the global status of a replication system in the master, and in each slave (because the table is replicated).

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      82321 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      82530 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |      82739 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      82948 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The status reads as follows:
IO+ means that the slave IO_thread is working (the opposite is io-.
SQL+ means that the SQL thread is working.
P+ means that the slave is reading from the latest binlog and that the position is equal or greater to the one shown by the master.
E+ means that the slave has executed all the statements received from the master.
Let's try a small experiment. We stop slave 2 and see what happens.

slave2> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      91517 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      91517 | io-,sql-,p-    |
|       103 | mysql-bin.000001 |      91932 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      92141 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The monitor shows that slave 2 is not working. If we restart the slave, the situation is restored.

slave2> start slave;

select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     114894 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     115104 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |     115314 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |     115524 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

We can do the same experiment while loading some heavy data, like the employee sample database.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     134158 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     134975 | IO+,SQL+,p-    |
|       103 | mysql-bin.000001 |     133777 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |    1165155 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |    7343962 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |    8374099 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |    7343751 | IO+,SQL+,P+,e- |
|       104 | mysql-bin.000001 |    8374310 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Very rapidly (the experiment happens in a MySQL Sandbox, so all servers use the same disk and CPU), the slaves are left behind in execution.
If we stop a slave while loading, the situation is even more explicit.

slave3> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |   45764491 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |   45764703 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000001 |   42685103 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Let's introduce a new element of difference, and flush logs while still loading data.

master> flush logs;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |    2044673 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |    3066965 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000002 |    3067176 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Now the stopped slave is really far behind. Let's see what happens when we put it back online.

slave3> start slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |   27604369 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |   27603945 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000002 |   27604157 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |   25558385 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

The restored slave was the first one to catch up in execution. This would not probably happen in a situation with separate hosts for each slave, but it's interesting to test our system.
And finally, after a few seconds more, all slaves have caught up with the master, with both data fetching and execution.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |  104641288 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000002 |  104641501 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000002 |  104641714 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |  104641927 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

Getting started

There is a step-by-step set of instructions in MySQL Forge Wiki that explain how to build and install the necessary plugin.
After that, you should get the code from the bazaar repository and make the changes in the option files of master and slaves (check the sql/master_scripts and sql/slave_scripts directories). Then, you should load the initializing script in the master and in each slave, and you are in business.
Notice that this first version relies on a MySQL Sandbox running with MySQL 5.1.28, compiled from source. Further versions will lift this limitation, but for now, if you want to try it out, you need to follow my steps closely.

Next steps

What's left? Ah, yes. A totally internal monitoring system is not much helpful. If the DBA needs to run a query to know what's happening, then the system is almost worthless.
This is another challenge, because MySQL architecture forbids connection to operating system services such as mail applications. There is already a solution to this problem. We only need to integrate it with the rest of the replication monitoring system. More challenges are outlined in the roadmap.
If you think this is interesting, check the roadmap, and comment on it. Even better, if you want to implement parts of it, join the sandbox developers group and start coding!