Database monitoring can be quite complex, and depending on your deployment or experience, monitoring may already be set up. MySQL Server is the MySQL is the world’s most popular open source database.
For those that don’t have an existing monitor for a MySQL service, Hyperic from SpringSource is an excellent tool to set up monitoring and alerting for MySQL. Hyperic is a powerful monitoring and management platform with a bunch of functionality available out-of-the-box. Hyperic Agents report metrics to a Hyperic Server, which then exposes a web portal to the Hyperic view of your environment.
The software comes in two editions: an Open Source edition suitable for smaller installations and an Enterprise edition with paid support. The steps in the following section are for the Open Source edition.
Hyperic can monitor many aspects of our OpenStack environment, including system load, network statistics, Memcached, and RabbitMQ status. For more information on Hyperic and the versions, visit www.hyperic.com
We will be configuring Hyperic on an Ubuntu 12.04 server that has access to the MySQL server in our OpenStack environment. Ensure that this server has enough RAM (at least 2 GB), disk (at least 10 GB), and CPU (at least 1 CPU; 2 CPU is better) capacity for the environment you are running. Log in as a normal user to download and install the software.
To install Hyperic, carry out the following steps:
tar zxvf hyperic-hq-installer-4.5-x86-64-linux.tar.gz
Once unpacked, change to the directory:
sudo mkdir -p /home/hyperic sudo chown openstack /home/hyperic
Once this area is ready, we can run the setup script to install Hyperic:
Each node that we want to monitor in Hyperic needs an agent installed, which then gets configured to talk back to the Hyperic server.
tar zxvf hyperic-hq-agent-4.5-x86-64.tar.gz
The output from running the previous command is as follows:
MySQL gives a comprehensive list of variables to check your server’s health and performance.
To monitor MySQL, carry out the following steps:
Name: openstack1 MySQL
Server Type: MySQL 5.x
Install Path: /usr
JDBC User: root
JDBC Password: openstack
These are the credentials for a user in MySQL that can see all databases. Check the Auto-Discover Tables option and leave the rest of the options MySQL at their default values, unless you need to change the address that the agent will connect to for MySQL.
By clicking on OK and then browsing back to the host, we will now have a monitoring option named openstack1 MySQL, as specified in step 3. The agent will then collect statistics about our MySQL instance.
Hyperic uses agents to collect information and sends this back to the Hyperic server, where we can view statistics about the environment and configure alerting based on thresholds. The agent is very flexible and can be configured to monitor many more services than just MySQL.
Configuration for MySQL of the agent is done through the Hyperic server’s interface, where a running node’s service is known as a server. Here, we can configure usernames, ports, and passwords, to allow the agent to successfully communicate with that service. For MySQL, this is providing the agent with the correct username, password, and address for the familiar jdbc (Java Database Connector) connect string.
In your data center, you may have a MySQL cluster rather than a single server, where a view of the cluster as a whole is of equal (if not more) importance to that of the individual nodes. An example cluster monitoring suite that has both free and enterprise options is named CMON and is available at SeveralNines (https://www.severalnines.com/resources/cmon-cluster-monitor-mysql-cluster).
Get Updates on Tech posts, Interview & Certification questions and training schedules