Monitoring MySQL with Hyperic – OpenStack
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.
How to achieve it…
To install Hyperic, carry out the following steps:
- We can find the Hyperic server to be downloaded at http://www.springsource.com/landing/hyperic-open-source-download.
- Fill in the details, and you will be presented with two links. One is for the server, and the other for the agent. Download both.
- On the server that will be running the Hyperic server, unpack the hyperic-hq-installer archive, thus:
tar zxvf hyperic-hq-installer-4.5-x86-64-linux.tar.gz
Once unpacked, change to the directory:
- The default install area for Hyperic is /home/hyperic, so we create this and ensure our unprivileged user can write to it:
sudo mkdir -p /home/hyperic
sudo chown openstack /home/hyperic
- Once this area is ready, we can run the setup script to install Hyperic:
- During the installation, a message will pop up asking us to open up another terminal on our server as the root user to execute a small script, as shown in the following screenshot:
- In a new terminal shell, log in as root and execute this command as described in the previous step:
- Return to the original shell and continue the installation. Eventually, the installation will complete. We can now start the Hyperic HQ service with the following command:
- For the first-time start up can be quite slow, but eventually you will be able to point your web browser at the address the installation has presented to you, which will be
- Log in with username hqadmin and password hqadmin.
Each node that we want to monitor in Hyperic needs an agent installed, which then gets configured to talk back to the Hyperic server.
- Copy the agent tarball to the server that we’ll be monitoring in Hyperic.
- Unpack the agent as follows:
tar zxvf hyperic-hq-agent-4.5-x86-64.tar.gz
- Change to the unpacked directory:
- Start the agent, which will ask for information about the Hyperic server installation. Specify the server address, port, username (hqadmin), and password (hqadmin). When asked for the IP to use, specify the address that Hyperic can use to communicate with the server.
The output from running the previous command is as follows:
- This completes the installation of the agent. Once done, the new node will appear in Hyperic, with auto-discovered services listed.
- Click on the Add to Inventory button to accept these to be added to Hyperic, and you will see our new node listed with the services that have been discovered.
MySQL gives a comprehensive list of variables to check your server’s health and performance.
To monitor MySQL, carry out the following steps:
- Monitoring MySQL involves the agent’s understanding how to authenticate with MySQL. We first add in the MySQL service to our host by selecting the host that has recently been added. This takes us to the main screen for that host, where we can click through services that are being monitored.
- We then click on the Tools Menu option and select New Server.
- This takes us to a screen where we can add in a label for the new service and the service type.
Name: openstack1 MySQL
Server Type: MySQL 5.x
Install Path: /usr
- Clicking on OK takes us to the configuration screen for this new service. At the bottom of the page, there is a section named Configuration Properties. Click on the EDIT… button for this section.
- We can now specify the username, password, and connect string, to use to connect to the running MySQL instance.
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.
How it works…
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 (http://www.severalnines.com/resources/cmon-cluster-monitor-mysql-cluster).