How to Setup MySQL Discovery on Your Turbonomic Instance

Document created by Anson McCook Expert on Aug 26, 2015Last modified by Anson McCook Expert on Jan 9, 2017
Version 14Show Document
  • View in full screen mode

The following setup is only applicable to users with the Application Control Module or Application Edition running on Operations Manager 5.3 or higher.

 

If you do not have the Application Control Module or Application Edition, please contact your Customer Experience representative or your Account Executive for licensing.

 

Ensure your Turbonomic appliance is running 5.3 or higher. Follow these instructions to update:

How To Perform an Operations Manager "Offline Update" - Latest Links Included

 

Table of Contents

 

 

Introduction

In this document we’ll review how to setup control for the MySQL database running within Turbonomic. To enable MySQL discovery on any MySQL database please reference this document: How to Target MySQL Servers in Turbonomic .

 

Open the Firewall for the MySQL Port

The firewall on the VM containing the MySQL instance must allow connections to the public MySQL port.

 

Linux

We will use Yast to install

1) Run yast. Open a Terminal session and enter the command:

yast

2) Open Software Management.

 

3) If you see the following error, Cannot access installation media simply select Skip. This prompt may appear multiple times. Select Skip each time.

Select OK to any Warnings that appear

 

 

4) Search for Firewall. Scroll down to yast2-firewall and press Enter. Then select Accept.

 

5) Exit yast for the new options to be available. Quit Yast.

 

6) Run yast again.

 

7) Under Security and Users select Firewall.

 

8) Under “Custom Rules”, set the Firewall Zone to the interface you would like to communicate to through to access MySQL. In this example, we will be setting the Firewall zone to the External Zone. Select Add and press Enter.

 

 

9) Fill out the fields below. In the example, we are using a configuration that will open up the port to anyone and everyone. Enter the values appropriate for your configuration. You should only open the port for known trusted source networks to ensure security.

 

Source Network: 0/0

Protocol: TCP

Destination Port: 3306

 

Select Add.

 

10) Select Next and Finish.

 

11) Quit Yast.

.

Windows

There are any number of good articles explaining how to open a firewall port for your version of Windows. Web search is your friend.

 

Configuration

MySQL must be configured to capture the metrics required for the MySQL mediation.

 

VMWare Tools

VMWare Tools must be installed on the VM hosting the MySQL instance.

 

Performance Schema

The performance schema must be enabled. Issue the following statement. The Value column value must be ON.

SHOW VARIABLES LIKE 'performance_schema';

 

If not, please see https://dev.mysql.com/doc/refman/5.6/en/performance-schema-quick-start.html for instructions on how to enable performance schema.

 

Transactions Metrics

The metrics used for transactions must be enabled. Issue the following statement. The status column value must be enabled for the rows with the names trx_rw_commits, trx_nl_ro_commits, trx_ro_commits, trx_rollbacks.

SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;

 

If not, issue the following statements.

SET GLOBAL innodb_monitor_enable = trx_rw_commits; SET GLOBAL innodb_monitor_enable = trx_nl_ro_commits; SET GLOBAL innodb_monitor_enable = trx_ro_commits; SET GLOBAL innodb_monitor_enable = trx_rollbacks;

 

Response Time Metrics

The metrics used for response time must be enabled. Issue the following statement. The values in the enabled and timed columns for all rows must be YES.

SELECT * performance_schema.setup_instruments WHERE NAME LIKE 'statement%';

 

If not, issue the following statements.

UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES' WHERE NAME LIKE ’statement/%'; UPDATE performance_schema.setup_instruments SET TIMED = ‘YES' WHERE NAME LIKE ’statement/%';

 

Create a User

A MySQL user with remote access to the MySQL instance and sufficient permission to access the require metrics must be defined.

A user with the least amount of privileges required can be defined by issuing the following statements. Replace %.%.%.% with the IP address of your Turbonomic instance. Replace the IDENTIFIED BY value with the password of your choice.

CREATE USER 'leastpriv'@'%.%.%.%' IDENTIFIED BY 'vmturbo'; GRANT SELECT ON performance_schema.* TO 'leastpriv'@'%.%.%.%'; GRANT PROCESS ON *.* TO 'leastpriv'@'%.%.%.%'; FLUSH PRIVILEGES;

 

Unbind MySQL from LocalHost (Turbonomic 5.6+)

 

In Turbonomic versions 5.6+ and up, you'll need to unbind MySQL from the localhost. Note that if you update your Turbonomic appliance, you will need to repeat these steps to unbind MySQL from LocalHost.

 

1) SSH into Turbonomic (with credentials: root/vmturbo)

 

2) Open the following file:

vi /etc/my.cnf

 

3) Navigate to the following line:

bind_address=127.0.0.1

 

4) Comment out the line:

#bind_address=127.0.0.1

 

5) Save the file and quit

 

6) Restart MySQL:

service mysql restart

 

Add the MySQL Target

 

MySQL Targets can be added by IP address targets or by scoped group targets. In this example, we will target an address MySQL target.

 

1) Go to Admin > Workflows > Target Configuration > Database > MySQL.

2) The Address is the IP address or DNS name of the MySQL target. The Port number is 3306, the port we opened on the firewall. The Username and Password are the user name we defined above.

3) Click the Add button. The target should appear on the Pending Targets list. Click Apply. The Target Configuration page will appear. Click Rediscover or Rediscover All.

 

 

4) After discovery and regrouping has completed you will see the MySQL DB in the Inventory tab. You will be able to see decisions based on the demand of the application from the virtual machine. Metrics collected include:

  • DBMem
  • Connection
  • Transaction
  • Response Time
  • Transaction Log

 

 

Attachments

    Outcomes