Abstract
This document describes how you can monitor an MySQL database server such as:
- Connection time
- The number of connections
- Cache hit
- etc
There are some steps you’ll need to follow in order to monitor a new database machine:
- Understand what is already available (in the Shinken Entreprise installation)
- Setup the MySQL user account
- Test the connection to the database
- Setup your server host definition
What is already available in the Shinken Installation
To make your life a bit easier, a few configuration tasks have already been done for you:
- Installation of check_mysql_health plugin : /var/lib/shinken/libexec/check_mysql_health
- Several host templates are ready to be used
Note
Setup the MySQL user account
Connect with a root account on your MySQL database. change ‘password’ with your mysql root password:
lin-srv-1:# mysql -u root -ppassword
And create a shinken user:
GRANT usage ON *.* TO 'shinken'@'%' IDENTIFIED BY 'shinkenpassword';
It’s a good thing to change the shinkenpassword to another password. Then you need to update the /etc/shinken/resource.d/mysql.cfg
$MYSQLUSER$=shinken $MYSQLPASSWORD$=shinkenpassword
Test the connection
To see if the connection to the MySQL server is ok, just launch :
 | /var/lib/shinken/libexec/check_mysql_health --hostname "srv-lin-1" --username "shinken" --password "shinkenpassword" --mode connection-time |
What is checked with the templates
Public templates
mysql
This is the very basic MySQL template allowing to know if your MySQL database server is running and usable.
Every MySQL templates (except Mysql-connection-method) use it as a parent template.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| connection | Time to connect to the server | 0 to n | 1 | 5 |
| restart | Time the server is running | 0 to n | 10: | 5: |
| slow_queries | slow_queries | 0 to n | 0.1 | 1 |
| tmp_disk_tables | Percent of temp tables created on disk | 0 to n | 25 | 50 |
mysql-full
Contains all MySQL host templates listed in the Shinken Adminstrators templates.
Warning
Using the mysql-full template will require a lot of resources on your poller daemon. We advise to use only the needed templates.
Shinken Administrator templates (can't be seen by other users)
mysql-connection-method
This template describes the method to connect to MySQL server. Every MySQL templates use it as a parent template.
mysql-cluster
This template gives informations about the MySQL cluster.
mysql-innoDB
This template gives informations about innoDB buffers and logwaits.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| bufferpool_hitrate | InnoDB buffer pool hitrate | 0 to n | 99: | 95: |
| bufferpool_wait_free | InnoDB buffer pool waits for clean page available | 0 to n | 1 | 10 |
| log_waits | InnoDB log waits because of a too small log buffer | 0 to n | 1 | 10 |
mysql-MyISAM
This template gives informations about MyISAM key cache hitrate.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| keycache-hitrate | MyISAM key cache hitrate | 0 to n | 99: | 95: |
mysql-performance
This template gives informations about the global server performance.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| index_usage | Usage of indexes | 0 to n | 90: | 80: |
| long_running_procs | long running processes | 0 to n | 10 | 20 |
| table_lock_contention | Table lock contention | 0 to n | 1 | 2 |
| tablecache_hitrate | Table cache hitrate | 0 to n | 99: | 95: |
| threadcache_hitrate | Hit rate of the thread-cache | 0 to n | 10 | 20 |
mysql-query-cache
This template gives informations about query cache.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| qcache_hitrate | Query cache hitrate | 0 to n | 90: | 80: |
| qcache_lowmem_prunes | Query cache entries pruned because of low memory | 0 to n | 1 | 10 |
mysql-usage
This template gives informations about MySQL server usage.
| Check | Detail | Check range | Default Warning | Default Critical |
|---|---|---|---|---|
| open_files | Percent of opened files | 0 to n | 80 | 95 |
| threads_connected | Number of currently open connections | 0 to n | 10 | 20 |
How to
Configuration of Mysql-connection-method host template
Click on Hosts then on Hosts Templates in the Elements menu |  |
In the Name field, type mysql-connection. Then clic on mysql-connection-method |  |
| Clic on Data tab | |
You can setup the following DATA :
|
|
You can also set the user and password directly in /etc/shinken/resource.d/myslq.cfg
$MYSQLUSER$=shinken $MYSQLPASSWORD$=shinkenpassword
Attach a MySQL template to you host
| Clic on Hosts in the Elements menu |  |
Add the chosen MySQL host template to the Host Templates to inherit field. For example the MySQL host template. |  |
Click on the Data tab |  |
| Check if the Following data are ok. |  |