Pivotal Knowledge Base

Follow

MySQL troubleshooting problems in Pivotal Cloud Foundry tiles

Environment

 Product  Version
 Pivotal Cloud Foundry (PCF)  1.10
 Pivotal MySQL  1.9.0+

Purpose

This article introduces a tool that can be used to gather information and help troubleshoot problems with the tiles that have an internal MySQL - Elastic Runtime, the Pivotal MySQL tile or the Pivotal Metrics tile. The mysql-diag tool will gather information, inform you of the state of your MySQL cluster and suggest remediations when the problems are discovered. Used in conjunction with Pivotal Support, this tool can help expedite the diagnosis and resolution of problems with MySQL. 

Instructions

Where to find the MySQL-Diag tool

The mysql-diag binary is distributed with the releases of Pivotal MySQL 1.9.0+ and Pivotal Cloud Foundry 1.10. It can be found on the mysql-monitor node. If you are not on at least Pivotal MySQL 1.9.0, you may obtain the utility from the attachments below. Please only use the attachment if you are on a previous release.

If any of the deployments i.e cf, p-mysql or apm has a mysql-monitoring-release, release v7.2 (or higher), the respective deployment will have the mysql-diag binary and the conf files.

What does MySQL-Diag tool do?

mysql-diag is a one-time use (ie, interactive, not an on-going check) diagnostic tool for helping you to understand the state of the cluster. Instead of just reporting raw metrics, this tool helps interpret the data in a way that's useful to the Operator.

We recommend running this tool against any cluster before any deployment.

Here's a quick summary of what mysql-diag does when checking the status of a cluster:
- Checks membership status of all nodes
- Checks cluster size (as it appears to all nodes)
- Checks to see if a cluster needs to be bootstrapped
- Checks to see if replication is working via a synthetic test
- Checks used disk space and inodes per server

For now an agent on each server is required to perform the disk space checks; if it's not available, mysql-diag simply omits this information and proceeds as normal with the other tests.

How to run the MySQL-Diag on Pivotal MySQL 1.9.0+ or versions of PCF or PCF Metrics which have the tool available

The tool is included on these versions so you can execute it using the following steps:

  1. Follow the instructions in the documentation here to prepare to use the Bosh CLI. You only need to do this on the first attempt.
  2. Follow the instructions in the documentation here to select either the Elastic Runtime or Pivotal MySQL or the Metrics (apm.yml) deployment.
  3. Run bosh ssh from the Ops Manager VM and select the mysql-monitor node from the list. If you need additional assistance with this, please refer to the documentation here.  
  4. Once on the mysql-monitor, VM type mysql-diag at the terminal prompt.

Using the tool with Earlier Releases, with Elastic Runtime or MySQL or Metrics

If you are running a version of Pivotal MySQL prior to 1.9.0, Elastic Runtime or Pivotal Metrics which has the mysql-monitoring-release release version less than 7.2, then you will need to download the binary attached in this article, copy it to the mysql-monitor VM and execute it. You can copy the file to the VM with the command bosh scp <job_name> <job_instance_number> --upload <local_file_path> <remote_file_path>. If you do not have a monitor node, as is the case with some older versions of the software, we strongly recommend you to upgrade. If for some reason you are unable to upgrade, you could use one of the MySQL cluster nodes instead.

Versions of Pivotal MySQL that ship with the mysql-diag utility come with an automatically generated configuration file (see attached mysql-diag-full.conf for an example). If your version does not ship with the mysql-diag utility, you will need to create a configuration file. To manually create a configuration file, please start with the template (shown below). Here are the steps to find the Credentials and the Host IP's that will be needed to manually generate the configuration file:

  1. For the Elastic Runtime MySQL, navigate to the Ops Manager Installation Dashboard, then select the tile Pivotal Elastic Runtime and then Credentials > MySQL Server > Monitordb Credentials. Click on the Link to Credential link and this will give you the Credentials for the conf file. The Host IP(s) can be found in the ERT tile, Status section (IPs corresponding to MySQL Server).
  2. For the MySQL tile, navigate to the Ops Manager Installation Dashboard, then select the tile MySQL and then Credentials > Monitoring > Canary Db Credentials. Click on the Link to Credential link and this will give you the Credentials for the conf file. The Host IP(s) can be found in the MySQL tile, Status section (IPs corresponding to MySQL Server).
  3. For the Metrics tile, navigate to the Ops Manager Installation Dashboard, then select the tile MySQL and then Credentials > MySQL Monitor > Canary Database Credentials. Click on the Link to Credential link and this will give you the Credentials for the conf file. The Host IP(s) can be found in the Metrics tile, Status section (IPs corresponding to MySQL Server).

We've also provided a copy of this template as an attachment in this article (see mysql-diag.json), if you'd prefer to download it instead. Please replace the password and the IPs as explained above.

Configuration File Template

{
    "mysql": {
      "username": "repcanary",
      "password": "ebuKSKkhL6WUFo9xxfFT0l3fijIhni_7",
      "port": 3306,
      "nodes": [                             
        {
          "host": "10.193.68.65",
        },
        {
          "host": "10.193.68.66",
        },
        {
          "host": "10.193.68.67",
        }
      ]
    }
  }

After you have created your configuration file, copy that onto the same VM as the mysql-diag tool, again using the bosh scp command. Put the configuration file in the same directory as the mysql-diag tool, make the binary executable via:

$ chmod +x mysql-diag

Then run the mysql-diag as shown below (attempting to run the binary from /tmp or /var/tmp may give you a Permission Denied error because later BOSH releases have "noexec" flag set to those partitions. You may use $HOME directory of the respective VM to run the binary).

$ ./mysql-diag -c ./mysql-diag.json

MySQL-Diag Agent

Certain older versions of Pivotal MySQL do not have a mysql-diag-agent running on the MySQL nodes, nor does the replication canary provide a diagnostic API. If that is the case for your installation, then you will only get limited output from the mysql-diag tool.

You can confirm if your environment has the mysql-diag-agent available by running sudo -i and then running monit summary on the MySQL node or replication canary VMs. If the agent is available, you'll see it's status reported by monit. If it's not listed then it's not available.

Example with agent installed:

# monit summary
 The Monit daemon 5.2.5 uptime: 57m

 Process 'mariadb_ctrl'              running
 Process 'galera-healthcheck'        running
 Process 'gra-log-purger-executable' running
 Process 'cluster_health_logger'     running
 Process 'mysql-diag-agent'          running
 System 'system_localhost'           running

Sample Output from MySQL-Diag

Here is some sample output from the mysql-diag utility. This shows how the tool behaves when the mysql-diag-agent isn't available:

  Checking canary status...
  Checking canary status... Get http://127.0.0.1:8111/api/v1/status: dial tcp 127.0.0.1:8111: getsockopt: connection refused
  Checking cluster status of mysql/c3 at 10.0.32.10...
  Checking cluster status of mysql/a1 at 10.0.16.44...
  Checking cluster status of mysql/b2 at 10.0.16.45...
  Checking cluster status of mysql/a1 at 10.0.16.44... done
  Checking cluster status of mysql/c3 at 10.0.32.10... done
  Checking cluster status of mysql/b2 at 10.0.16.45... done
  +------------+-----------+-------------------+----------------------+--------------------+
  |    HOST    | NAME/UUID | WSREP LOCAL STATE | WSREP CLUSTER STATUS | WSREP CLUSTER SIZE |
  +------------+-----------+-------------------+----------------------+--------------------+
  | 10.0.16.44 | mysql/a1  | Synced            | Primary              |                  3 |
  | 10.0.32.10 | mysql/c3  | Synced            | Primary              |                  3 |
  | 10.0.16.45 | mysql/b2  | Synced            | Primary              |                  3 |
  +------------+-----------+-------------------+----------------------+--------------------+
  I don't think bootstrap is necessary
  Checking disk status of mysql/c3 at 10.0.32.10...
  Checking disk status of mysql/a1 at 10.0.16.44...
  Checking disk status of mysql/b2 at 10.0.16.45...
  Checking disk status of mysql/c3 at 10.0.32.10... Get http://10.0.32.10:8112/api/v1/info: dial tcp 10.0.32.10:8112: getsockopt: connection refused
  Checking disk status of mysql/a1 at 10.0.16.44... Get http://10.0.16.44:8112/api/v1/info: dial tcp 10.0.16.44:8112: getsockopt: connection refused
  Checking disk status of mysql/b2 at 10.0.16.45... Get http://10.0.16.45:8112/api/v1/info: dial tcp 10.0.16.45:8112: getsockopt: connection refused
  Unable to gather disk usage information, moving on. Run bosh vms --vitals for this information.

Attachments

Comments

Powered by Zendesk