# MySQL > Monitor MySQL database performance, slow queries, and system metrics with OpenTelemetry and Last9 Source: https://last9.io/docs/integrations/databases/mysql/ Use OpenTelemetry to monitor MySQL database performance and send telemetry data to Last9. This integration provides comprehensive monitoring of database performance, slow queries, connection metrics, and system resource utilization. ## Prerequisites Before setting up MySQL monitoring, ensure you have: - MySQL server installed and running (5.7+ or 8.0+) - Administrative access to create MySQL users and configure settings - OpenTelemetry Collector installation access - Last9 account with integration credentials 1. **Verify MySQL Installation** Ensure MySQL server is installed and running on your system: ```bash sudo apt update sudo apt install mysql-server sudo systemctl status mysql ``` For other operating systems, refer to the [official MySQL documentation](https://dev.mysql.com/doc/). 2. **Create MySQL Monitoring User** Create a dedicated user for OpenTelemetry monitoring with the necessary permissions: ```sql CREATE USER 'otel'@'localhost' IDENTIFIED BY 'otel'; GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'otel'@'localhost'; FLUSH PRIVILEGES; ``` **Permissions Explained:** - `SELECT`: Read access to performance schema and information schema - `PROCESS`: View running processes and connections - `REPLICATION CLIENT`: Access replication status information 3. **Configure MySQL for Monitoring** Edit the MySQL configuration file to enable performance monitoring: ```bash sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` Add or modify the following settings under the `[mysqld]` section: ```ini [mysqld] # Enable Performance Schema for detailed metrics performance_schema = ON # Enable InnoDB monitoring for storage engine metrics innodb_monitor_enable = all # Configure slow query logging slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2 # Optional: Enable general query log for debugging # general_log = 1 # general_log_file = /var/log/mysql/mysql-general.log # Optional: Configure error log location log_error = /var/log/mysql/mysql-error.log ``` **Configuration Explained:** - `performance_schema`: Enables detailed performance metrics collection - `innodb_monitor_enable`: Provides comprehensive InnoDB storage engine metrics - `slow_query_log`: Logs queries that take longer than `long_query_time` seconds - `long_query_time`: Threshold for slow query logging (in seconds) 4. **Restart MySQL Service** Apply the configuration changes by restarting MySQL: ```bash sudo systemctl restart mysql ``` Verify MySQL is running correctly: ```bash sudo systemctl status mysql mysql -u otel -p -e "SELECT 1;" ``` 5. **Install OpenTelemetry Collector** Choose the appropriate package for your operating system: **DEB Package** For Debian/Ubuntu systems: ```bash wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.118.0/otelcol-contrib_0.118.0_linux_amd64.deb sudo dpkg -i otelcol-contrib_0.118.0_linux_amd64.deb ``` **RPM Package** For Red Hat/CentOS systems: ```bash wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.118.0/otelcol-contrib_0.118.0_linux_amd64.rpm sudo rpm -ivh otelcol-contrib_0.118.0_linux_amd64.rpm ``` More installation options are available in the [OpenTelemetry documentation](https://opentelemetry.io/docs/collector/installation/#linux). 6. **Configure OpenTelemetry Collector** Create the collector configuration file: ```bash sudo nano /etc/otelcol-contrib/config.yaml ``` Add the following configuration to monitor MySQL metrics, logs, and system resources: ```yaml receivers: filelog: # File path pattern to read MySQL logs from include: [/var/log/mysql/*.log] include_file_path: true retry_on_failure: enabled: true hostmetrics: collection_interval: 60s scrapers: cpu: metrics: system.cpu.logical.count: enabled: true memory: metrics: system.memory.utilization: enabled: true system.memory.limit: enabled: true load: disk: filesystem: metrics: system.filesystem.utilization: enabled: true network: paging: mysql: endpoint: localhost:3306 # Change this to match your MySQL server address username: otel password: otel # Use ${env:MYSQL_PASSWORD} for environment variable collection_interval: 60s processors: batch: timeout: 20s send_batch_size: 10000 send_batch_max_size: 10000 resourcedetection/cloud: detectors: ["aws", "gcp", "azure"] resourcedetection/system: detectors: ["system"] system: hostname_sources: ["os"] transform/logs: flatten_data: true log_statements: - context: log statements: - set(observed_time, Now()) - set(time_unix_nano, observed_time_unix_nano) where time_unix_nano == 0 - set(resource.attributes["service.name"], "mysql") - set(resource.attributes["deployment.environment"], "production") exporters: otlp/last9: endpoint: "$last9_otlp_endpoint" headers: "Authorization": "$last9_otlp_auth_header" debug: verbosity: detailed service: pipelines: logs: receivers: [filelog] processors: [ batch, resourcedetection/system, resourcedetection/cloud, transform/logs, ] exporters: [otlp/last9] metrics: receivers: [mysql, hostmetrics] processors: [batch, resourcedetection/system, resourcedetection/cloud] exporters: [otlp/last9] ``` Replace the placeholder values with your actual Last9 credentials and MySQL connection details. 7. **Create Systemd Service Configuration** Create a systemd service file for the OpenTelemetry Collector: ```bash sudo nano /etc/systemd/system/otelcol-contrib.service ``` Add the following service configuration: ```ini [Unit] Description=OpenTelemetry Collector Contrib with custom flags After=network.target [Service] ExecStart=/usr/bin/otelcol-contrib --config /etc/otelcol-contrib/config.yaml --feature-gates transform.flatten.logs Restart=always User=root Group=root [Install] WantedBy=multi-user.target ``` 8. **Start and Enable the Service** Start the OpenTelemetry Collector service and enable it to start automatically: ```bash sudo systemctl daemon-reload sudo systemctl enable otelcol-contrib sudo systemctl start otelcol-contrib ``` ## Understanding MySQL Metrics The MySQL receiver collects comprehensive metrics including: ### Connection Metrics - **Active Connections**: Current number of active client connections - **Connection Errors**: Failed connection attempts and reasons - **Max Connections**: Maximum allowed concurrent connections - **Thread Cache**: Thread pool utilization for connection handling ### Query Performance - **Query Execution Time**: Average and percentile query duration - **Slow Queries**: Queries exceeding the configured slow query threshold - **Query Cache**: Hit ratio and cache utilization - **Queries Per Second**: Throughput metrics for different query types ### InnoDB Storage Engine - **Buffer Pool**: Memory utilization and hit ratios - **Transactions**: Active, committed, and rolled back transactions - **Locks**: Lock waits and deadlocks - **I/O Operations**: Read/write operations and latency ### Replication (if applicable) - **Replication Lag**: Delay between master and slave - **Binary Log**: Position and size information - **Slave Status**: Replication health and errors ## Advanced Configuration ### Secure Password Management Use environment variables for sensitive information: ```yaml mysql: endpoint: localhost:3306 username: otel password: ${env:MYSQL_PASSWORD} # Set via environment variable ``` Set the environment variable: ```bash export MYSQL_PASSWORD=your_secure_password ``` ### Custom Query Monitoring Monitor specific queries or tables: ```yaml mysql: endpoint: localhost:3306 username: otel password: otel statements: - "SELECT COUNT(*) as user_count FROM users" - "SELECT AVG(response_time) as avg_response FROM api_logs WHERE timestamp > NOW() - INTERVAL 5 MINUTE" ``` ### TLS/SSL Configuration For secure connections: ```yaml mysql: endpoint: localhost:3306 username: otel password: otel tls: insecure: false ca_file: /path/to/ca.pem cert_file: /path/to/client-cert.pem key_file: /path/to/client-key.pem ``` ## Verification 1. **Check Service Status** Verify the OpenTelemetry Collector service is running: ```bash sudo systemctl status otelcol-contrib ``` 2. **Monitor Service Logs** Check for any configuration errors or connection issues: ```bash sudo journalctl -u otelcol-contrib -f ``` 3. **Test MySQL Connection** Verify the collector can connect to MySQL: ```bash # Test with the monitoring user mysql -u otel -p -e "SHOW STATUS LIKE 'Connections';" mysql -u otel -p -e "SHOW PROCESSLIST;" ``` 4. **Generate Database Activity** Create some database activity to generate metrics: ```sql -- Connect to MySQL and run some queries USE information_schema; SELECT COUNT(*) FROM tables; SELECT * FROM processlist; ``` 5. **Verify Data in Last9** Log into your Last9 account and check that MySQL metrics are being received in [Grafana](https://app.last9.io/grafana). Look for metrics like: - `mysql.connections` - `mysql.queries` - `mysql.buffer_pool.usage` - `mysql.innodb.transactions` ## Troubleshooting ### Connection Issues **MySQL Connection Refused:** ```bash # Check if MySQL is running sudo systemctl status mysql # Check MySQL is listening on the expected port sudo netstat -tlnp | grep 3306 # Test connection manually mysql -u otel -p -h localhost ``` **Authentication Failed:** ```sql -- Verify user exists and has correct permissions SELECT User, Host FROM mysql.user WHERE User = 'otel'; SHOW GRANTS FOR 'otel'@'localhost'; ``` ### Permission Issues **Missing Metrics:** ```sql -- Check if Performance Schema is enabled SHOW VARIABLES LIKE 'performance_schema'; -- Verify InnoDB monitoring is enabled SHOW ENGINE INNODB STATUS; ``` ### Log File Access **Log File Not Found:** ```bash # Check MySQL log file locations sudo mysql -e "SHOW VARIABLES LIKE 'log%';" # Verify log file permissions sudo ls -la /var/log/mysql/ sudo chmod 644 /var/log/mysql/*.log # If needed ``` ### High Resource Usage Monitor and adjust collector resource usage: ```bash # Check collector memory usage sudo systemctl status otelcol-contrib ps aux | grep otelcol-contrib # Monitor MySQL performance impact mysql -u otel -p -e "SHOW PROCESSLIST;" ``` ## Best Practices ### Security - **Use Dedicated Users**: Create monitoring-specific users with minimal required permissions - **Secure Credentials**: Store passwords in environment variables or secure credential stores - **Network Security**: Restrict database access to monitoring systems only - **TLS Encryption**: Enable TLS for database connections in production ### Performance - **Collection Intervals**: Adjust based on your monitoring needs and system resources - **Batch Processing**: Configure appropriate batch sizes for efficient data transmission - **Index Monitoring**: Monitor slow queries and optimize database indexes based on findings - **Connection Pooling**: Use connection pooling in applications to reduce connection overhead ### Monitoring - **Set Alerts**: Configure alerts for critical metrics like connection limits and slow queries - **Baseline Metrics**: Establish performance baselines for comparison and capacity planning - **Regular Reviews**: Regularly review slow query logs and optimize database performance ## Need Help? If you encounter any issues or have questions: - Join our [Discord community](https://discord.com/channels/652153247672729619/652153247672729621) for real-time support - Contact our support team at [support@last9.io](mailto:support@last9.io)