MySQL
StackState Self-hosted v4.5.x
This page describes StackState v4.5.x. The StackState 4.5 version range is End of Life (EOL) and no longer supported. We encourage customers still running the 4.5 version range to upgrade to a more recent release.
Overview
Get realtime metrics from MySQL databases, including:
Query throughput
Query performance (average query run time, slow queries, etc)
Connections (currently open connections, aborted connections, errors, etc)
InnoDB (buffer pool metrics, etc)
You can also invent your own metrics using custom SQL queries.
MySQL is a StackState curated integration.
Setup
Installation
The MySQL check is included in the Agent V2 StackPack StackPack. No additional installation is needed on your MySQL server.
Configuration
Edit the mysql.d/conf.yaml
file, in the conf.d/
folder at the root of your Agent's configuration directory to start collecting your MySQL metrics and logs.
Prepare MySQL
On each MySQL server, create a database user for the Agent:
For mySQL 8.0+ create the stackstate
user with the native password hashing method:
Note: @'localhost'
is only for local connections - use the hostname/IP of your Agent for remote connections. For more information, see the MySQL documentation.
Verify the user was created successfully using the following commands - replace <UNIQUEPASSWORD>
with the password you created above:
The Agent needs a few privileges to collect metrics. Grant the user the following limited privileges ONLY:
For mySQL 8.0+ set max_user_connections
with:
If enabled, metrics can be collected from the performance_schema
database by granting an additional privilege:
Metric Collection
Add this configuration block to your
mysql.d/conf.yaml
to collect your MySQL metrics:
Note: Wrap your password in single quotes in case a special character is present.
To collect extra_performance_metrics
, your MySQL server must have performance_schema
enabled - otherwise set extra_performance_metrics
to false
. For more information on performance_schema
, see the MySQL documentation.
Note that the stackstate
user should be set up in the MySQL integration configuration as host: 127.0.0.1
instead of localhost
. Alternatively, you may also use sock
.
Restart the Agent to start sending MySQL metrics to StackState.
Log Collection
By default MySQL logs everything in
/var/log/syslog
which requires root access to read. To make the logs more accessible, follow these steps:Edit
/etc/mysql/conf.d/mysqld_safe_syslog.cnf
and remove or comment the lines.Edit
/etc/mysql/my.cnf
and add following lines to enable general, error, and slow query logs:Save the file and restart MySQL using following commands:
service mysql restart
Make sure the Agent has read access on the
/var/log/mysql
directory and all of the files within. Double-check your logrotate configuration to make sure those files are taken into account and that the permissions are correctly set there as well.In
/etc/logrotate.d/mysql-server
there should be something similar to:
Collecting logs is disabled by default in the StackState Agent, so you need to enable it in
stackstate.yaml
:Add this configuration block to your
mysql.d/conf.yaml
file to start collecting your MySQL logs:See our sample
mysql.yaml
for all available configuration options, including those for custom metrics.Restart the Agent.
Validation
Run the Agent's status
subcommand and look for mysql
under the Checks section.
Data Collected
Metrics
See metadata.csv
for a list of metrics provided by this integration.
The check does not collect all metrics by default. Set the following boolean configuration options to true
to enable the respective metrics:
extra_status_metrics
adds the following metrics:
extra_innodb_metrics
adds the following metrics:
extra_performance_metrics
adds the following metrics:
schema_size_metrics
adds the following metric:
Events
The MySQL check does not include any events.
Service Checks
mysql.replication.slave_running
: Returns CRITICAL for a slave that's not running, otherwise OK.
mysql.can_connect
: Returns CRITICAL if the Agent cannot connect to MySQL to collect metrics, otherwise OK.
Last updated