Monitoring Multi-Master MySQL Deployments GTIDs using gtidmonitor

MySQL GTID is relatively new and the popular tools for monitoring MySQL servers are still playing catch-up. Here is a rudimentary web-based tool for monitoring MySQL GTIDs in multi-master multi-slave deployments: gtidmonitor

Out of Sync!

Our MySQL deployment has four master servers is circular (aka ring) replication. We have several slave servers that spoke out from the master servers. Users using an application found that one server was not synchronized with the other masters. We couldn't figure out why. There were no replication errors. So we began the long and tedious task of trying to figure out what happened. What we found was that on one master the GTIDs being generated were lower then what the other masters and slaves had executed. In other words MASTER A was generating GTIDS in the 1000's (1-9999) and the SLAVES had their EXECUTED values for MASTER A in 100,000's (1 - 498334). Therefore, the SLAVES were ignoring all these transactions thinking they were duplicates. How could this be? In the end we found that someone had issued a RESET MASTER to MASTER A. That was a catastrophic mistake and required us to re-synchronize the master servers.

Tracking down GTIDs

Trying to track GTID issues is a bear. There are no friendly tools to really help watch and track GTIDs. We use MONyog and it's still not quite 5.6 friendly, yet. So, I wrote a simple PHP program to help display GTID's, by host, to see what is being applied and how far behind the master they are. Here is a screen shot:

Quick Overview

If we look at the first table Host: mysql02 : 708bb615-d393-11e3-a682-003048c3ab22 : 1-1663024, you will see the other hosts (defined in the hosts.txt file). The title tells us the hostname (as entered in the hosts.txt file), the Server_UUID, and it's current executed GTIDs.

Still referring to this table, view the column mysql01 and the Slave Executed row: This is informing us that this host (mysql02) has executed GTIDs 1-31128 belonging to mysql01, which represents 86.64% of mysql01 GTIDs (transactions).  In other words, mysql02 has currently executed just 86.64% of mysql01's transactions.

What isn't shown in this screen shot is when you hover your mouse cursor over a red text you will get a small popup to show the master servers executed GTIDs.

Download

gtidmonitor.zip Last updated 2014-06-10

Installation

You will need to install this on a host that has PHP5 with the PHP5 mysql extensions installed. For example, in Ubuntu do:  sudo apt-get install php5 php5-mysql

This application should work in windows too but I have not tested it.

Configuration

Configuration is simple. just edit the hosts.txt file and add the hosts to monitor. You can customize the some of the output by editing index.php.