Index  •  Intro  •  Basics of software monitoring  •  Standard Monitors  •  FAQ  •  Help  •  JavaDoc  •  Download

Tutorial 1: Detailed monitoring guide  •  Tutorial 2: HttpSessionListener  •  Tutorial 3: MySQL replication  •  Tutorial 4: Java Garbage Collection

Introduction
Many projects have successfully used MySQL master-slave replication feature. This type of replication is quite easy to setup and it works very well. Initially, I was skeptical of the replication and did not expect it to work reliably. I became more optimistic after reading some of Jeremy Zawodny’s presentations and his book "High performance MySQL" both showing that Yahoo! Finance team made replication work. I finally tried replication myself and it indeed works.

One big issue remained. In order to get a truly mission-critical database system one has to continuously monitor the state of both the master and the slave servers. In his writing Jeremy has hinted at the simple algorithm for monitoring replication status. Several simple scripts were written to explore his ideas. However, it was quite difficult for me to adapt these various scripts to my Java projects.

Finally I wrote MySQL replication monitor in Java. What is really cool is that I connected it with my Linguine Watch open-source monitoring project to allow monitoring of replication status with SNMP. This article describes how I have done it. Now, you can setup your MySQL master-slave replication monitoring with SNMP in five minutes!

General algorithm for monitoring
The basic algorithm for monitoring replication consists of the following steps:

• write the timestamp into some dedicated table on the master
• wait a bit for the replication to take place
• read the timestamp value off the same dedicated table on the slave
• subtract two timestamp values and see how much the slave is behind the master.
• repeat the whole sequence

I created simple table _MASTERSTATUS on the master to store the timestamp and some other things. Here is the DDL for it:

 
CREATE TABLE `_masterstatus` (
  `ID` int(11) NOT NULL default '0',
  `SEQUENCE` int(11) default NULL,
  `UPDATED_ON` datetime default NULL,
  `MASTER_UID` int(11) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `ID_idx` (`ID`)
);

There are several fields present in this table in addition to the timestamp. The absolute time can be very tricky to synchronize in the clustered environment and it should not be trusted. That is why I rely on the SEQUENCE number as an alternative. Instead of writing new timestamp to the master we first read the SEQUENCE number from the master. Then we increment its value by one and then write new value back into the same row on the master. Now we can wait for the replication and later read the SEQUENCE number off the slave and compare both values. We still put the timestamp into the UPDATED_ON field on the master, but for the convenience and not for the actual monitoring.

To figure our how much the slave is behind the master we multiply the difference in the SEQUENCE numbers by the frequency of the updates to the master. If the master has SEQUENCE number 10, the slave has 3 and master is updated once every 5 seconds, the slave is behind master by 7 * 5 = 35 seconds.

When the master-slave replication is configured a master is given a unique id. However, it is not possible to figure out the master's id while talking to the slave. To overcome this problem we need the MASTER_UID field. This field will help us to defend against the situation when one tries to accidentally monitor the master and the slave that actually do not work with each other. Now, every time the monitoring application starts it chooses a random number. This random number is written to the MASTER_UID field on all consecutive updates to the master. When reading the SEQUENCE number from the slave, we also check that the value of the MASTER_UID field matches with the master.

Purging binary logs
There is one annoying detail about MySQL master-slave replication that does not get mentioned frequently. For the replication to work, the binary logging must be enabled on the master. The binary logs take up large amount of disk space and must be cleaned up manually. MySQL replication monitor described in here takes care of purging binary logs as well.

After the slave status has been confirmed by checking SEQUENCE number and MASTER_UID, we determine the name of the current binary log file that is being processed by the slave. It can be obtained by issuing SHOW SLAVE STATUS statement on the slave (Appendix A). All prior binary logs have been already processed by the slave and can be purged from the master using PURGE MASTER LOGS TO statement.

Deployment diagram
The typical deployment diagram for the complete monitoring solution is presented below. It shows two MySQL servers: the master and the slave. Both servers are queried from the MySQL monitor I am describing here. The MySQL monitor contains a worker thread that issues periodic updates to the master. The monitor also checks how data is replicated from the master to the slave and purges the binary logs if everything works fine.

The MySQL monitor has built-in Java SNMP agent for reporting data to an SNMP monitoring station. A very good product OpManager from Adventnet can be used as a monitoring station. It has a free version that handles all aspects of SNMP monitoring (with alarms) for a limited number of hosts. Any other SNMP capable monitoring application will work as well.

Almost no programming skills are needed
If you are a Java programmer you can integrate this package into any of your existing applications. At the same time I keep forgetting that not all people in the world are programmers. If you are not a Java programmer, but still want to use MySQL replication monitor you can now do it without writing any custom code or compiling anything.

The MySQL monitor application can be started from the command line using the following batch file. Please properly set OY_LW_HOME and MYSQL_REPL_PROPS variables before use. Most of all possible configuration parameters can be set in a MYSQL_REPL_PROPS property file. So you don’t have to write any Java code to recompile anything. Here the sample properties file. The description of the parameters is in the Appendix B. The standard Java JDK 1.4+ is required.

See MySqlReplStatusMonitor in action
Start the master and the slave servers and MySqlReplTest monitoring application. Connect the OpManager to MySqlReplTest application via default SNMP port 161. Start some unit tests that generate moderate MySQL work. To test the replication progress I have set a maximum bin log file size to just 50K (max_binlog_size=50000), so I can force logs being purged frequently.

The real-time chart for the sample run is presented below. The same data can be seen on a periodic HTML report.

The red line shows how master SEQUENCE number is incremented every 5 seconds. The green line shows SEQUENCE number on the slave. Both follow each other closely. The blue line shows how much the slave is behind the master. It always stays at 0, confirming that the replication is always done within 5 seconds (in my test at least). The yellow line shows number of errors.

At around 120 seconds into the test I issue STOP SLAVE command on the slave server. The green line stops growing; new values for SEQUENCE number do not arrive to the slave. The blue line, the lag, increases correspondingly. As soon as I issue START SLAVE command at around 140 seconds slave immediately recovers and everything goes back to normal! There are no errors while replication is stopped; we are able to connect to both the slave and the master to check their statuses.

At around 180 seconds, I terminate the slave process and the number of errors and the lag start growing. The errors increase because we are not able to connect to slave anymore. Finally, at around 210 seconds, I terminate the master. The red line, that represents the master SEQUENCE number, stops growing. The number of errors goes up twice as fast.

Now we are ready to eliminate the last bit of manual work in the replication monitoring. Set up an email alarm inside the OpManager to trigger if:

• the lag is over 12 (slave 1 minute behind), or
• the number of errors is above 0.

Implementation of the MySQL monitor
The implementation consists of several Java classes. MySqlReplThread class is a thread that queries the master and the slave and purges the binary logs. MySqlReplCmd class handles details of SQL statements and manages database connections. In addition to the common SQL queries and updates, the following MySQL -specific statements are used (Appendix A):

• SHOW MASTER STATUS;
• SHOW SLAVE STATUS;
• SHOW MASTER LOGS;
• PURGE MASTER LOGS TO 'bin-log.XYZ';

MySqlReplContext class contains various options that control behavior of the package. MySqlReplManager class holds all of the other classes together. MySqlReplTest contains main() function and all the configuration code.

Following the ideas of the Linguine Watch framework for adding performance counters to large software applications, all the MySQL monitoring information is concentrated in one class MySqlReplStatusMonitor.

This class contains five individual performance counters including:

• master_seq (master SEQUENCE number)
• slave_seq (slave SEQUENCE number)
• lag (the difference between two numbers above)
• purge_to (last bin log number that was purged)
• errors (number of SQL or other errors since the start time)

All performance counters are continuously updated by the MySqlReplThread as it communicates with the master and the slave. At the same time the performance counter values can be accessed directly in real-time by registering MySqlReplStatusMonitor with Linguine Watch built-in SNMP agent.

Implementation of SNMP agent
Linguine Watch is an open-source Java library for real-time monitoring of Java applications. It has a simple mechanism for publishing performance monitoring objects (like the MySqlReplStatusMonitor) and a built-in SNMP agent. Linguine Watch is free, simple, thread-safe, high-performance library that adds very little overhead. It is designed to be deployed with the application into production environment.

The SNMP defines a special MIB file format for listing counter definitions. The Linguine Watch generates MIB files automatically thus saving you from the need to learn MIB file format and writing the file by hand. The generated MIB file for the MySqlReplStatusMonitor is here.

Putting it all together
Finally we have to combine together the Linguine Watch SNMP agent and the MySQL replication monitor into MySqlReplTest application. The simple versions of this process are discussed in Tutorial 1 and Tutorial 2. But the MySqlReplTest example is more complex because both the SNMP agent and the MySqlReplManager have threads and require a coordination between their start() and stop() operations.

The issues here are similar to the issues of any Java or non-Java package where multiple dependent services must be started and stopped in a coordinated way. I do not want to pollute this article with the deep discussion of this and I will discuss this topic elsewhere.

To make long story short, the following adapted excerpt from MySqlReplTest shows the correct order for initialization and finalization of the Linguine Watch SNMP agent and MySqlReplManager. Notice how finalization sequence is in the reverse order to initialization.

 
public static void main(String  args){

	MySqlReplManager man;
	LinguineSNMPWatch watch;

	try {	
		
		// create, customize, start watch
		watch = new LinguineSNMPWatch();
		init(watch);		
		watch.start();		
		try {
			// create, customize MySQLReplManager
			man = new MySqlReplManager();
			init(man);
		
			// after watch has started we can now add counters
			watch.getPerfAgent().addMonitor(man.getMonitor());
			try {			
				// after counters have been added we can generate SNMP MIB file
				watch.getSNMPAgent().generateMIBFile("c:/test.mib");

				// start MySQLReplManager threads
				man.start();
				try {


					// enter main message loop for your application here
					// do not return until application needs to exit
					...


				} finally{
					// stop MySQLReplManager
					man.stop();
				}

			} finally {
				// remove monitor
				watch.getPerfAgent().removeMonitor(man.getMonitor());
			}
			
		} finally {
			// stop watch last
			watch.stop();
		}
		
	} catch(Exception e){
		e.printStackTrace();
	}
}

Conslusion
If you think that only big boys like Microsoft, Oracle, IBM, Sun, or Cisco can afford built-in software monitoring, you are wrong. It is quite simple; you just have to go for it!

Appendix A
MySQL statements used in managing master-slave replication.

 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| my-bin-log.1666  | 678      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



mysql> PURGE MASTER LOGS TO 'my-bin-log.1580';
Query OK, 0 rows affected (0.01 sec)



mysql> SHOW SLAVE STATUS;
+-------------+-------------+-------------+---------------+------------------+...
| Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File  |...
+-------------+-------------+-------------+---------------+------------------+...
| 127.0.0.1   | admin       | 3011        | 60            | my-bin-log.1666  |...
+-------------+-------------+-------------+---------------+------------------+...
1 row in set (0.00 sec)


mysql> SHOW MASTER LOGS;
+------------------+
| Log_name         |
+------------------+
| my-bin-log.1663  |
| my-bin-log.1664  |
| my-bin-log.1665  |
| my-bin-log.1666  |
+------------------+
4 rows in set (0.00 sec)

Appendix B
Configuration parameters can be changed programmatically or via property file properties file.

Following configuration parameters can be set for MySqlReplContext:

 
	// the name of the housekeeping database that will store timestamp and sequence
	// numbers used to track replication status
	private String dbName = "MASTER";
	
	// the name of the housekeeping database
	private String tableName = "_MASTERSTATUS";
	
	// the table type for the CREATE TABLE command 
	private String tableCreatePostfix = "type = InnoDB";
		
	// change master timestamp and sequence number and check slave status
	// every this number of milliseconds
	private int checkDelayMillis = 5000;
	
	// purge master logs after confirming that slave is up to date
	// every this number of milliseconds
	private int purgeDelayMillis = 5 * 60 + 1000;
	
	// if set to true will purge binary logs once confirmed with slave
	// that they have been processed; is set to false will only report replication
	// status, increment master status, but will not purge bin logs
	private boolean canPurgeBinLogs = true;

Following configuration parameters can be set for MySqlReplContext.MySqlReplConnectionPool:

 
	// connection string to the master database
	private String masterConnStr;
		
	// connection string to the slave database
	private String slaveConnStr;

References

1. Managing MySQL Replication by Jeremy Zawodny.
2. Monitoring MySQL Replication Slaves by Christian G. Warden.

Index  •  Intro  •  Basics of software monitoring  •  Standard Monitors  •  FAQ  •  Help  •  JavaDoc  •  Download

Tutorial 1: Detailed monitoring guide  •  Tutorial 2: HttpSessionListener  •  Tutorial 3: MySQL replication  •  Tutorial 4: Java Garbage Collection