Monitoring MySQL & MariaDB

Configure the Server Density agent to monitor your MySQL or MariaDB and:

• Collect important metrics, such as performance & connections
• Identify overall server slowdowns caused by the database server
• Execute custom queries and monitor the returned metric

Monitored metrics

  • Max used connections
  • Slow queries
  • Threads connected
  • Temporary created disk tables
  • Open files
  • Seconds behind master
  • Table locks waited
  • Connections

Installing the MySQL / MariaDB plugin package

Install the MySQL / MariaDB plugin on Debian/Ubuntu:

sudo apt-get install sd-agent-mysql

Install the MySQL / MariaDB plugin on RHEL/CentOS:

sudo yum install sd-agent-mysql

Read more about agent plugins.

Configuring the agent to monitor MySQL / MariaDB

The steps for configuring MySQL and MariaDB are exactly the same - MariaDB is a drop-in replacement for MySQL. If you have a master-slave setup, ensure you give the database user the correct permissions (step 2). 

1. Connect to the database server and create a monitoring user:

sudo mysql -e "CREATE USER 'serverdensity'@'localhost' IDENTIFIED BY 'supersecretpassword';"

2. Grant the correct permissions to the user:

sudo mysql -e "GRANT PROCESS ON *.* TO 'serverdensity'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'serverdensity'@'localhost';"

3. (Optional) The above user does not require any extra privileges unless you wish to monitor replication, in which case it requires REPLICATION CLIENT privileges:

sudo mysql -e "GRANT REPLICATION CLIENT ON *.* to 'serverdensity'@'localhost';"

4. Configure /etc/sd-agent/conf.d/mysql.yaml

init_config:
instances:  - server: localhost    user: serverdensity    pass: supersecretpassword sock: /path/to/socket
      • If you want to connect via TCP on a non-standard port, change the server option to 127.0.0.1 and uncomment the port line and amend the port number. Note that you will need to grant access for your user to 127.0.0.1 instead of localhost
      • If you do not know your socket location you can find it with the following command mysqladmin variables | grep socket

4. Restart the agent

sudo /etc/init.d/sd-agent restart

Verifying the configuration
Execute info to verify the configuration with the following:

sudo /etc/init.d/sd-agent info 

If the agent has been configured correctly you’ll see an output such as:

mysql
-----
 - instance #0 [OK]
 - Collected 12 metrics, 0 events & 1 service check

You can also check the metrics that the check is returning with the following command

sudo -u sd-agent /usr/share/python/sd-agent/agent.py check mysql

Replication monitoring

To enable replication monitoring you need to add 

options: # Optional 
  replication: 1 

To your {agentdir}/conf.d/mysql.yaml configuration file. A full example is below:

 instances:
  - server: localhost
    user: my_username
    pass: my_password
    port: 3306             # Optional
    options:              # Optional
       replication: 1

If you have configured replication monitoring, you will see additional metrics:

      • Slave_running
      • Seconds_behind_master

An important metric is "seconds behind master" because as this shows how far behind the master the slave is. As this increases, the slave is getting increasingly behind.

You should create alerts for this metric so you can be notified when it starts getting too far behind. A good baseline value is 300 or 600 seconds i.e. 5 or 10 minutes. This allows for some variation e.g. networking issues but doesn't let it get too far behind.

Configuring graphs

Click the name of your server from the Devices list in your Server Density account then go to the Metrics tab. Click the + Graph button on the right then choose the MySQL metrics to display the graphs. The metrics will also be available to select when building dashboard graphs.



Further reading

We have a detailed blog post about how to monitor MySQL.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Monday  —  Friday.

10am  —  6pm UK.

Dedicated Support.