System Center Open Source Software Management Packs Walkthrough Part 2: MySQL Server

[System-Center-Team]

First published on TECHNET on Jul 08, 2015

Two weeks ago we released a walkthrough of managing Apache HTTP Server with System Center 2016 Technical Preview 2 Operations Manager . This week we continue with a walkthrough for managing MySQL Server with System Center 2016 TP2 Operations Manager. This walkthrough details a step by step guide for setting up and managing MySQL Server.

Here’s a quick look at the feature set for the MySQL Server management pack. A full list of available metrics can be found in the MySQL Server management pack guide in the Appendix section:

-          Performance metrics for key cache, query cache, table cache, etc.

-          Alerting on connections and query performance

-          InnoDB buffer pool hit and usage statistics

-          Information on disk space usage for the whole server and individual databases

Requirements for Open Source Software management pack monitoring can be found in the respective management pack guide at the linked download page above.

Basic Setup

For this walkthrough my pre-existing environment consists of the following

1. System Center 2016 Technical Preview 2 Operations Manager


2. System Center 2016 Technical Preview 2 Management Packs for Open Source Software


3. Ubuntu Server 14.04 LTS with MySQL Server 5.5 installed
   
   
   1. Important: This Linux server is not already managed by System Center 2016 Technical Preview 2 Operations Manager
   
   

MySQL Server Monitoring Overview

MySQL Server monitoring consists of a MySQL OMI CIM Provider that uses the MySQL client libraries to retrieve statistics from the running MySQL Server. In order for the MySQL OMI CIM Provider to utilize the MySQL client libraries user credentials must be specified to authorize and facilitate the connection. A short architecture overview can be seen below.

Creating the MySQL Server user

To begin configuration of the MySQL Server monitoring user let’s login to the MySQL Server using the MySQL client. There are a variety of tools to access the MySQL Server. In this tutorial I use the mysql client included with the mysql-server distribution

1. On the Ubuntu 14.04 LTS Server run the following command to login to the MySQL client as root. Note: You may login as any user as long as the specified user has permissions to CREATE users and GRANT permissions.
   
   
   1. mysql -u<INSERT USER> -p<INSERT PASSWORD>
   
   
   
   
   
   
   • 
   
   
   
   


2. if successful you are greeted with a terminal for the mysql client
   
   
   
   • 
   
   
   
   


3. In the MySQL command terminal enter the following to create a MySQL Server monitoring user with a specified password. In our case the user is "scomuser" and the password is "scompass"
   
   
   1. CREATE USER ‘scomuser’@’localhost’ IDENTIFIED by ‘scompass’
   
   
   
   
   
   
   • 
   
   
   
   


4. Once the MySQL Server monitoring user is created we need to grant permissions so that the monitoring user can view the performance metrics MySQL Server collects.
   
   
   1. The MySQL monitoring user requires SELECT privileges to the default 'mysql' tables for base performance monitoring. Run the following command to grant SELECT privileges for the 'mysql' database to the scomuser
   
   
   
   
   
   
   • 
     
     
     
     
     
     
     
     
     

     GRANT SELECT ON mysql.* TO ‘scomuser’@’localhost’;

     
     
     
     
   
   
   
   


5. The MySQL monitoring user also retrieves information about MySQL Databases the MySQL monitoring user has permissions to access. In order to grant privileges to the MySQL monitoring user use the following command as a template.
   
   
   1. 
      
      
      
      
      
      
      
      
      

      GRANT SELECT ON <INSERT DATABASE NAME>.* TO ‘scomuser’@’localhost’;

      
      
      
      
   
   

Managing the Ubuntu Server with System Center 2016 Technical Preview 2 Operations Manager

As mentioned in the Apache blog last week, the process for bringing a Linux server under management with this new version of Operations Manager remains largely unchanged. For a more detailed look on how to discover Linux servers with Operations Manager refer to the following blow post http://blogs.technet.com/b/kevinholman/archive/2012/03/18/deploying-unix-linux-agents-using-ops...

Like the Apache provider the MySQL provider is included in the new Linux agents for System Center 2016 TP2 Operations Manager. During the Linux agent installation if a supported version of MySQL Server is installed the Linux agent installs the MySQL Server agent.

If you would like to add MySQL management to an already managed Linux server refer to the instructions inside the MySQL management pack guide on the download page.

1. Import the MySQL Server Library Management Pack and the OSS Library Management Pack. The version for both management packs is 7.6.1042.0
   
   
   
   • 
   
   
   • 
   
   
   
   


2. After the MySQL management packs are imported when the next discovery runs a MySQL Server instance appears in the console. MySQL Server discovery runs every four hours by default.
   
   
   
   • 
   
   
   
   

Enabling MySQL Monitoring

Now that we have the MySQL Server discovery completed in Operations Manager we have to configure the MySQL provider to use the MySQL Server monitoring user we created earlier. Thankfully, this can be done by distributing an authentication text file or running a management pack task. This guide uses the management pack tasks. Instructions for creating and distributing the authentication text file can be found in the management pack guide.

1. Click the “Not monitored” instance of the MySQL Server and open the “Task Pane” on the right hand side


2. Under MySQL Server (Linux) Tasks you can see two tasks “Set Default MySQL Credentials” and “Update Discovered Properties”
   
   
   
   • 
   
   
   
   


3. First run the “Set Default MySQL Credentials”
   
   
   
   • 
   
   
   
   


4. The task contains parameters including username, port, password, and bind address. Let’s click “Override” and change the username and password to the user we created in the earlier step as well as change the bind address to 127.0.0.1 for localhost.
   
   
   1. The port option can be changed to a specific port if needed, otherwise the MySQL client communicates through the default 3306. If multiple instances of MySQL Server are running on the same Linux server refer to MySQL Management Pack Guide for authentication file configuration.
   
   
   
   
   
   
   • 
   
   
   
   


5. After clicking override and then run task we should receive a “True” for task output
   
   
   
   • 
   
   
   
   


6. Once the MySQL Monitoring user is configured we can run the “Update Discovered Properties” management pack task to initiate a discovery and immediately update properties.
   
   
   
   • 
   
   
   
   

Congratulations!! You have successfully set up MySQL Server monitoring with System Center 2016 TP2 Operations Manager. After a couple minutes MySQL Server Databases and MySQL Server performance metrics begin to populate.

We appreciate any comments you may have about MySQL Server or Apache HTTP Server monitoring, and look forward to your feedback.