illustration by Stephane Parpand

Introduction:

Moodss fully supports the MySQL database and great efforts are made in collaboration with the MySQL AB developer Sinisa Milivojevic, in order to achieve a practical and thorough monitoring of MySQL servers.

At this time, no less than 8 modules (such as the new myreplication and myerrorlog modules) allow a very flexible way to monitor one or several MySQL servers on the same user made dashboard. As with any other moodss modules, monitoring can be done graphically with moodss or in the background with the moomps daemon. Most useful are thresholds with email alerts on very important indicators, as can be found in the myhealth module.


Main features:


Modules:

myerrorlog:

This module monitors the error log of a database server, local or remote. The messages are displayed in a table, refreshed at a settable interval. Messages are regrouped, sorted according to their importance level and time of arrival. Thresholds can be set so that the administrators may be warned when new errors occur.
This module is important and extremely useful, and required quite a large amount of work: importance levels have been determined for more than 350 messages that can be generated by the MySQL server in its error log.
More examples and documentation can be found in the module help file.

screenshot: myerrorlog module

myhealth:

This module monitors the health of a database server. Carefully chosen relevant indicators are displayed, allowing the administrator to set meaningful thresholds. For example, it is possible to be alerted with an email message when a server becomes unreachable, or the number of remaining connections becomes low. More examples and documentation can be found in the module help file.

screenshots:

myhealth module
main window
myhealth thresholds dialog box
thresholds dialog box

myreplication:

This module monitors a pool of replicated servers, which can include any number of masters and slaves, or even servers assuming both roles, for complete forward compatibility. Also very useful for debugging your replicated servers, this module displays replication delays, identifiers, errors, ... allowing the administrator great flexibility in setting thresholds used to warn of any replication problems.

For example, it is possible to be alerted with an email message when a slave is late in the replication process. More examples and documentation can be found in the module help file.

screenshots:

myreplication module
main window
myreplication threshold
a threshold triggered and its corresponding email

myshow:

This module monitors the status or the configuration variables of a database server. Data is drawn from the SHOW STATUS or SHOW VARIABLES queries results and displayed in 1 table. Examples and documentation can be found in the module help file.

screenshot: myshow module

mystatus:

(obsolete) This module monitors the status of a database server. Data is drawn from the SHOW STATUS query results and displayed in 2 tables (the second being specific to queries counters). Examples and documentation can be found in the module help file.

screenshot: mystatus module

myvars:

(obsolete) This module monitors the database server configuration variables. Data is drawn from the SHOW VARIABLES query results and displayed in one table. Examples and documentation can be found in the module help file.

screenshots:

myvars module
main window
myvars help
complete contextual help

myprocs:

This module monitors the database server processes. Data is drawn from the SHOW PROCESSLIST query results and displayed in one table. Examples and documentation can be found in the module help file.

screenshot: myprocs module

myquery:

This module monitors any data from a database server using the native MySQL connection protocol. The contents of a database table or the results of a user-defined query are displayed in one table. Examples and documentation can be found in the module help file.

screenshot: myquery module

odbcquery:

This module, as the myquery module, monitors any data from a database server, using ODBC. The contents of a database table or the results of a user-defined query are displayed in one table. Examples and documentation can be found in the module help file.


Howto:

Sending SNMP traps:

For example, using the myhealth module, you would like to send a state change to unavailable SNMP trap to your big network/system management workstation.
Using the snmptrap tool from the net-snmp package (also known as ucd-snmp), insert the following line in the script field entry of a threshold set on the remaining connections indicator:

snmptrap -v 2c -c public manager '' RDBMS-MIB::rdbmsStateChange RDBMS-MIB::rdbmsRelState i 5
where manager is the address of the management workstation.

Here is a screenshot of the corresponding thresholds dialog box:

SNMP trap on threshold
SNMP trap on threshold

Sending SMS messages (to a telephone):

Use the script functionality of a threshold entry to launch a command that will send the message.
In the message text, use the automatic substitutions (for example: %t is replaced by the threshold value) to make it more meaningful (more on thresholds script substitutions).

There are many ways to send SMS messages depending on your country and the available operators at the time. Please try http://freshmeat.net/search/?q=send+sms to find out about all the available ways to do it.

For example, you could enter the following script:

smssend totalise.sms 33 6123456 'WARNING: %s value (%v) is now more than %t'

Monitoring replicated servers:

Note: old method, use the myreplication module now

Build a dashboard using the myquery module on the different servers:

Then, using a free text viewer, display all the different replication positions so that a mismatch becomes instantaneously obvious.
Add thresholds with email alerts when Last_error is not empty on any of the slaves. Such condition would indicate that a slave is no longer synchronized with the master.

Here are screenshots of a real dashboard running 24 hours a day:

replication example
main window
replication thresholds
thresholds

You can optionally load the myhealth, mystatus, ... module on the master and some of the slaves in order to monitor health indicators, statistics, ...


Contact:

write me at: jfontain@free.fr, visit my homepage.

This page was last updated on March 4, 2006.