MySQL Community Replication Monitoring System
[edit] The community driven replication monitoring tools for MySQL
By the principle that you can spend time to save money, the community driven MySQL replication monitoring tools are a collection of scripts in SQL and other languages, to achieve the same result that others can get with expensive applications.
Everything in this project is made with community involvement, by open source principles.
The main goal of this project is to take advantage of MySQL 5.1 features to create a portable, SQL-based monitoring system.
Contents |
[edit] Purpose
This set of scripts and documentation aims at creating a platform independent monitoring systems for MySQL replication. No external applications will be necessary except the server (with plugins) and eventually MySQL Proxy.
[edit] System requirements
The monitoring tools are based on MySQL 5.1 features, integrated by a plugin written by Hartmut Holzgraefe.
- event scheduler;
- information_schema plugin for slave and master status.
The above are the minimal requirements for basic monitoring. For more advanced tasks, it may be necessary using Federated tables and MySQL Proxy.
[edit] Libraries
The monitoring tools are server-side. Thus, they must be implemented as database objects (tables, stored routines, events, triggers) that must be created by SQL scripts.
The sql directory contains such libraries.
Additional advanced tools are implemented as a combination of database objects and MySQL Proxy. These tools are stored in the lua directory.
[edit] How it works
The principles of server side tools are simple. Replication monitor uses mainly the output of SHOW MASTER STATUS and SHOW SLAVE STATUS to detect the health of a replication system. The main problem with this approach is that the output of SHOW commands can't be used inside stored routines or events. If you need to know if a slave is replicating, you must parse the output of SHOW SLAVE STATUS and detect if Slave_IO_Running and Slave_SQL_Running report 'Yes'. To detect if the slave is using the right binary log file, you should compare the output of SHOW MASTER STATUS and SHOW SLAVE STATUS. All of the above needed the help of an external application to produce results. Not anymore. Using Hartmut's plugin, you can now read the above statuses as INFORMATION_SCHEMA tables, and assign a single column to a value inside a stored routine. You can diagnose the replication health from the inside.
[edit] Simple applications
What can you do with the above apparently insignificant advance in technology? A great lot, actually. All the needed pieces existed already, and this was the missing tile to complete the mosaic.
[edit] Slave-side monitor
- a mstatus table on the master with the same fields as MASTER_STATUS;
- an event on the master that updates mstatus with the fields from MASTER_STATUS;
- a stored routine on the slave that compares the values of replicated mstatus with the values of SLAVE_STATUS;
[edit] Master-side monitor
- a slaves_status table in the master, excluded from replication;
- a federated table in each slave pointing to the slaves_status table in the master;
- each slave will update slaves_status with its own results.
[edit] Installation
The first order of business is to get the plugin, compile it and load the new features.
[edit] Download the source
Get the source code.
[edit] Expand it
Expand the tarball in any directory
$ mkdir build $ cd build $ tar -xzf MySQL-is_replication_status-0.1.tar.gz $ cd MySQL-is_replication_status-0.1
[edit] Configure and compile
You need the full path to the MySQL 5.1 source code, which you can get through the Bazaar source trees in Launchpad or you can download from the MySQL site. Either way, this source must be the same from which the binaries that you will use for this project were built.
Assuming that your 5.1 source code is in $HOME/builds/5.1, now you need to configure the plugin with the above information.
$ ./configure --with-mysql-src=$HOME/builds/5.1 && make
[edit] Copying the plugin library
If everything went according to plan, you can now copy the plugin library to the appropriate directory.
$ cp .libs/is_replication_status.so $MYSQL_BASEDIR/lib/mysql/plugin
(Notice that the built libraries are in the hidden .libs directory).
[edit] Starting the server with the new features
You can initialize the new features in two ways. At run time, you can use
mysql> INSTALL PLUGIN master_status SONAME 'is_replication_status.so'; mysql> INSTALL PLUGIN slave_status SONAME 'is_replication_status.so';
Or you can pre-install the plugins at start-up, by including the following directive in the options file.
plugin_load=master_status=is_replication_status.so:slave_status=is_replication_status.so
[edit] Checking that they work
mysql> use information_schema; mysql> show tables like '%_STATUS'; +-----------------------------------------+ | Tables_in_information_schema (%_STATUS) | +-----------------------------------------+ | GLOBAL_STATUS | | SESSION_STATUS | | SLAVE_STATUS | | MASTER_STATUS | +-----------------------------------------+
mysql> select plugin_name, plugin_type, plugin_status from plugins where plugin_name like '%STATUS'; +---------------+--------------------+---------------+ | plugin_name | plugin_type | plugin_status | +---------------+--------------------+---------------+ | MASTER_STATUS | INFORMATION SCHEMA | ACTIVE | | SLAVE_STATUS | INFORMATION SCHEMA | ACTIVE | +---------------+--------------------+---------------+
And finally, let's use it. First, the old way:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+
And then the new way:
mysql> select File,Position from MASTER_STATUS; +------------------+----------+ | File | Position | +------------------+----------+ | mysql-bin.000001 | 106 | +------------------+----------+
[edit] Simple monitoring in action
Currently (2008-08-29), there is a proof-of-concept monitoring system based on a few scripts that give visibility of the replication status in the master and in each slave. The status is written to a table, which is replicated. (These scripts are available from the source code repository).
master> select * from slaves_status; +-----------+------------------+------------+--------------+ | server_id | binlog_file | binlog_pos | slave_status | +-----------+------------------+------------+--------------+ | 101 | mysql-bin.000002 | 2747779 | OK | | 102 | mysql-bin.000002 | 2747381 | OK | | 103 | mysql-bin.000002 | 2747182 | OK | | 104 | mysql-bin.000002 | 2747580 | OK | +-----------+------------------+------------+--------------+ slave3> stop slave SQL_THREAD; master> select * from slaves_status; +-----------+------------------+------------+--------------+ | server_id | binlog_file | binlog_pos | slave_status | +-----------+------------------+------------+--------------+ | 101 | mysql-bin.000002 | 2747779 | OK | | 102 | mysql-bin.000002 | 2747381 | OK | | 103 | mysql-bin.000002 | 2747978 | not ok | | 104 | mysql-bin.000002 | 2747580 | OK | +-----------+------------------+------------+--------------+ slave3> start slave SQL_THREAD; master> select * from slaves_status; +-----------+------------------+------------+--------------+ | server_id | binlog_file | binlog_pos | slave_status | +-----------+------------------+------------+--------------+ | 101 | mysql-bin.000002 | 2753375 | OK | | 102 | mysql-bin.000002 | 2752977 | OK | | 103 | mysql-bin.000002 | 2753574 | OK | | 104 | mysql-bin.000002 | 2753176 | OK | +-----------+------------------+------------+--------------+