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:

   sudo apt update
   sudo apt install mysql-server
   sudo systemctl status mysql

   For other operating systems, refer to the official MySQL documentation.

2. Create MySQL Monitoring User

   Create a dedicated user for OpenTelemetry monitoring with the necessary permissions:

   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:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   Add or modify the following settings under the [mysqld] section:

   [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:

   sudo systemctl restart mysql

   Verify MySQL is running correctly:

   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
   • RPM Package

   For Debian/Ubuntu systems:

   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

   More installation options are available in the OpenTelemetry documentation.

6. Configure OpenTelemetry Collector

   Create the collector configuration file:

   sudo nano /etc/otelcol-contrib/config.yaml

   Add the following configuration to monitor MySQL metrics, logs, and system resources:

   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:

   sudo nano /etc/systemd/system/otelcol-contrib.service

   Add the following service configuration:

   [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:

   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:

mysql:
  endpoint: localhost:3306
  username: otel
  password: ${env:MYSQL_PASSWORD} # Set via environment variable

Set the environment variable:

export MYSQL_PASSWORD=your_secure_password

Custom Query Monitoring

Monitor specific queries or tables:

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:

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:

   sudo systemctl status otelcol-contrib

2. Monitor Service Logs

   Check for any configuration errors or connection issues:

   sudo journalctl -u otelcol-contrib -f

3. Test MySQL Connection

   Verify the collector can connect to MySQL:

   # 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:

   -- 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.

   Look for metrics like:

   • mysql.connections
   • mysql.queries
   • mysql.buffer_pool.usage
   • mysql.innodb.transactions

Troubleshooting

Connection Issues

MySQL Connection Refused:

# 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:

-- 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:

-- 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:

# 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:

# 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 for real-time support
• Contact our support team at support@last9.io