Monitoring MySQL

Supported operating systems

The supported operating systems of the MySQL sensor are consistent with host agent requirements, which can be checked in the Supported operating systems section of each host agent, such as Supported operating systems for Unix.

Supported versions

Instana supports the following versions of MySQL:

• MySQL 5.6.x
• MySQL 5.7.x
• MySQL 8.0.x

Supported client-side tracing

For this technology, Instana supports client-side tracing for the following languages and runtimes:

• Java
• Go
• Node.js
• PHP
• Python
• .Net Framework
• .Net Core

Required DB Permissions

For the sensor to be able to collect performance information about the DBMS, read-only access to the schema PERFORMANCE_SCHEMA is required.

• For MySQL versions earlier than 8.0.0, see the following example for how to create an account with the required rights:

  GRANT REPLICATION CLIENT ON *.* TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  GRANT SELECT ON 'performance_schema'.'events_waits_summary_global_by_event_name' TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  GRANT SELECT ON 'performance_schema'.'events_statements_summary_by_digest' TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  GRANT SELECT ON 'performance_schema'.'events_statements_summary_global_by_event_name' TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  GRANT SELECT ON 'performance_schema'.'replication_connection_status' TO '<instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  

• For MySQL version 8.0.0 or later, see the following example for how to create an account with the required rights:

  GRANT REPLICATION CLIENT ON *.* TO '<instana_mon_user>'@'localhost';
  GRANT SELECT ON performance_schema.events_waits_summary_global_by_event_name TO '<instana_mon_user>'@'localhost';
  GRANT SELECT ON performance_schema.events_statements_summary_by_digest TO '<instana_mon_user>'@'localhost';
  GRANT SELECT ON performance_schema.events_statements_summary_global_by_event_name TO '<instana_mon_user>'@'localhost';
  GRANT SELECT ON performance_schema.replication_connection_status TO '<instana_mon_user>'@'localhost';
  

Note: To collect total transactions metrics, you need to grant the PROCESS permission to the user and enable the counters 'trx_nl_ro_commits','trx_ro_commits','trx_rollbacks', and 'trx_rw_commits' in the schema information_schema.innodb_metrics:

• For MySQL versions earlier than 8.0.0, to grant the PROCESS permission to all database schemas of the SQL user, run the following command:

  GRANT PROCESS ON *.* TO <instana_mon_user>'@'localhost' IDENTIFIED BY 'instana_mon_pwd';
  

• For MySQL version 8.0.0 or later, to grant the PROCESS permission to all database schemas of the SQL user, run the following command:

  GRANT PROCESS ON *.* TO '<instana_mon_user>'@'localhost';
  

  The hostname part of the account name depends on your MySQL configuration.

• To enable these counters, run the following commands:

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

MySQL version 8.0.0 and above support

From build 266, Instana automatically monitors MySQL 8.0.0 and later. You do not need to manually add the MySQL driver JAR file to the Instana agent deploy folder.

For earlier builds, Instana does not provide automatic MySQL monitoring for MySQL 8.0.0 and later due to licensing reasons. Therefore, you need to manually obtain the MySQL driver JAR file from the Maven repository, and add it to the Instana agent deploy folder by using the <agent_install_dir>/deploy/ command. The MySQL driver jar file obtained from Maven repository should have version 8.0.16 or newer.

spec:
  initContainers:
    - name: init-myservice
      image: 'registry.access.redhat.com/ubi8/ubi:latest'
      command:
        - sh
        - '-c'
        - >-
          curl
          https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar
          --output /instana/mysql-connector-java-8.0.28.jar
      resources: {}
      volumeMounts:
        - name: instanadeploy
          mountPath: /instana
    volumes:
    - name: instanadeploy
      emptyDir: {}

    containers:
      name: instana-agent
      volumeMounts:
        - name: instanadeploy
          mountPath: /opt/instana/agent/deploy

Configuration data

• Process ID
• Port
• Version
• Start time
• Role
• Max connections

Performance metrics

Replication metrics

Health Signatures

For each sensor, there is a curated knowledgebase of health signatures that are evaluated continuously against the incoming metrics and are used to raise issues or incidents depending on user impact.

Built-in events trigger issues or incidents based on failing health signatures on entities, and custom events trigger issues or incidents based on the thresholds of an individual metric of any given entity.

For information about built-events for the MySQL sensor, see the Built-in events reference.

Performance Schema disabled by default

If an error message, similar to:

java.sql.SQLSyntaxErrorException: (conn=2) Table 'performance_schema.global_status' doesn't exist

appears in Instana agent log, it is most likely, that your MySQL server copy has been compiled with performance schema disabled by default. This is a known bug.

To fix this, you could try to enable it from MySQL CLI:

mysql> set @@global.show_compatibility_56=ON;
Query OK, 0 rows affected (0.00 sec)

and to check it's enabled:

mysql>select @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Once enabled, mysql>show status; should now work. Please restart Instana agent.

Timezone setup

If an error message, similar to:

The server time zone value 'CEST' is unrecognized or represents more than one time zone.
You must configure either the server or JDBC driver (via the serverTimezone configuration property) 
to use a more specifc time zone value if you want to utilize time zone support.

appears in Instana agent log, the server timezone needs to be configured.

This is a known issue since MySQL is not reading the host time-zone appropriately.

The error can be fixed as explained in the MySQL Server Time Zone Support.

Authentication protocol not supported

If an error message, similar to:

Client does not support authentication protocol requested by server

appears in Instana agent log, you need to use mysql_native_password instead of caching_sha2_password mechanism:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
mysql> flush privileges;

Connection error

Sometimes you might see an error message in the Instana host agent log as follows:

Agent could not connect to MySQL.  Could not connect: Access denied for user 'user'@'ipaddress/hostname' (using password: YES)

In this case, you need to alter the MySQL user to use another hostname in the MySQL side. For example, if you created a MySQL user as user@localhost, you need to alter the user's name in the MySQL side to 'user'@'ipaddress or hostname' as stated in the error message, and then update the com.instana.plugin.mysql > users field in the agent configuration file with the 'username' without hostname, since agent automatically discovers hostname. This error can happen when MySQL is run within a container.