MySQL

MySQL is a relational database management system that runs as a server providing multi-user access to a number of databases. For more information visit mysql.com.

Prerequisites

To collect MySQL telemetry, you must install Ops Agent version 2.5.0 or higher.

Configure the Ops Agent for MySQL

Following the guide for Configuring the Ops Agent, add the required elements to collect logs from your MySQL instances, and restart the agent.

Example configuration

The following command creates the configuration file to collect and ingest logs for MySQL and restart the Ops Agent on Linux.

sudo tee /etc/google-cloud-ops-agent/config.yaml > /dev/null << EOF
logging:
  receivers:
    mysql_error:
      type: mysql_error
    mysql_general:
      type: mysql_general
    mysql_slow:
      type: mysql_slow
  service:
    pipelines:
      mysql:
        receivers:
          - mysql_error
          - mysql_general
          - mysql_slow
EOF
sudo service google-cloud-ops-agent restart

Configure logs collection

To ingest logs from MySQL, you must create receivers for the logs MySQL produces and then create a pipeline for the new receivers.

To configure a receiver for your mysql_error logs, specify the following fields:

Field Default Description
type This value must be mysql_error.
include_paths [/var/log/mysqld.log, /var/log/mysql/mysqld.log, /var/log/mysql/error.log] A list of filesystem paths to read by tailing each file. A wild card (*) can be used in the paths; for example, /var/log/mysql/*.log.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.


To configure a receiver for your mysql_general logs, specify the following fields:

Field Default Description
type This value must be mysql_general.
include_paths [/var/lib/mysql/${HOSTNAME}.log] The log files to read.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.


To configure a receiver for your mysql_slow logs, specify the following fields:

Field Default Description
type This value must be mysql_slow.
include_paths [/var/lib/mysql/${HOSTNAME}-slow.log] The log files to read.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.

What is logged

The logName of the mysql_error, mysql_general, and mysql_slow logs are derived from the receiver IDs specified in the configuration.

Depending on your MySQL version, mysql_error logs might contain the following fields inside the LogEntry:

Field Type Description
jsonPayload.level string Log entry level
jsonPayload.tid number Thread ID where the log originated
jsonPayload.errorCode string MySQL error code associated with the log
jsonPayload.subsystem string MySQL subsystem where the log originated
jsonPayload.message string Log message
severity string (LogSeverity) Log entry level (translated)
timestamp string (Timestamp) Time the request was received

Any fields that are blank or missing will not be present in the log entry.


mysql_general logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.tid number Thread ID where the log originated
jsonPayload.command string Type of database action being logged
jsonPayload.message string Log of the database action
timestamp string (Timestamp) Time the entry was logged


mysql_slow logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.user string User that executed the query
jsonPayload.database string Database where the query was executed
jsonPayload.host string Host of the database instance
jsonPayload.ipAddress string Address of the database instance
jsonPayload.tid number Thread ID where the query was logged
jsonPayload.queryTime number The statement execution time in seconds
jsonPayload.lockTime number The time to acquire locks in seconds
jsonPayload.rowsSent number The number of rows sent to the client
jsonPayload.rowsExamined number The number of rows examined by the server layer
jsonPayload.errorNumber number The statement error number, or 0 if no error occurred
jsonPayload.killed number If the statement was terminated, the error number indicating why, or 0 if the statement terminated normally
jsonPayload.bytesReceived number The number of bytes received from all clients
jsonPayload.bytesSent number The number of bytes sent to all clients
jsonPayload.readFirst number The number of times the first entry in an index was read
jsonPayload.readLast number The number of requests to read the last key in an index
jsonPayload.readKey number The number of requests to read a row based on a key
jsonPayload.readNext number The number of requests to read the next row in key order
jsonPayload.readPrev number The number of requests to read the previous row in key order
jsonPayload.readRnd number The number of requests to read a row based on a fixed position
jsonPayload.readRndNext number The number of requests to read the next row in the data file
jsonPayload.sortMergePasses number The number of merge passes that the sort algorithm has had to do
jsonPayload.sortRangeCount number The number of sorts that were done using ranges
jsonPayload.sortRows number The number of sorted rows
jsonPayload.sortScanCount number The number of sorts that were done by scanning the table
jsonPayload.createdTmpDiskTables number The number of internal on-disk temporary tables created by the server
jsonPayload.createdTmpTables number The number of internal temporary tables created by the server
jsonPayload.startTime string The statement execution start time
jsonPayload.endTime string The statement execution end time
jsonPayload.message string Full text of the query
timestamp string (Timestamp) Time the entry was logged

  These fields are only provided if the log_slow_extra system variable is set to 'ON'. This variable is available as of MySQL 8.0.14.

For information about these fields, refer to the MySQL documentation.

Verify the configuration

You can use the Logs Explorer to verify that you correctly configured the MySQL receiver. It might take one or two minutes for the Ops agent to begin collecting logs.

To verify the logs are ingested, go to the Logs Explorer and run the following query to view the MySQl logs:

resource.type="gce_instance"
logName=("projects/PROJECT_ID/logs/mysql_error" OR "projects/PROJECT_ID/logs/mysql_general" OR "projects/PROJECT_ID/logs/mysql_slow")