How do I enable and monitor logs for an Amazon RDS MySQL DB instance?

Last updated: 2021-02-15

I want to enable and monitor the error log, the slow query log, and the general log for an Amazon Relational Database Service (Amazon RDS) instance running MySQL. How can I do this?

Short description

You can monitor the MySQL error log, the slow query log, and the general log directly through the Amazon RDS console, Amazon RDS API, Amazon RDS AWS Command Line Interface (AWS CLI), or AWS SDKs. The MySQL error log file is generated by default. You can generate the slow query log and the general log.

Resolution

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

First, create a custom DB parameter group and modify the parameter. Then, associate the parameter group with your MySQL instance.

Create a DB parameter group

  1. Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
  2. Choose Create parameter group.
  3. From the Parameter group family drop-down list, choose a DB parameter group family.
  4. For Type, choose DB Parameter Group.
  5. Enter the name in the Group name field.
  6. Enter a description in the Description field.
  7. Choose Create.

Modify the new parameter group

  1. Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
  2. Choose the parameter group that you want to modify.
  3. Choose Parameter group actions, and then choose Edit.
  4. Choose Edit parameters, and set the following parameters to these values:
    General_log = 1 (default value is 0 or no logging)
    Slow_query_log = 1 (default value is 0 or no logging)
    Long_query_time = 2 (to log queries that run longer than two seconds)
    log_output = FILE (writes both the general and the slow query logs to the file system, and allows viewing of logs from the Amazon RDS console)
    log_output =TABLE (writes the queries to a table so you can view these logs with a query)
  5. Choose Save Changes.
    Note: You can't modify the parameter settings of a default DB parameter group. You can modify the parameter in a custom DB parameter group if Is Modifiable is set to true.

Associate the instance with the DB parameter group

  1. Open the Amazon RDS console, and then choose Databases from the navigation pane.
  2. Choose the instance that you want to associate with the DB parameter group, and then choose Modify.
  3. From the Database options section, choose the DB parameter group that you want to associate with the DB instance.
  4. Choose Continue.

    Note: The parameter group name changes and applies immediately, but parameter group isn't applied until you manually reboot the instance. There is a momentary outage when you reboot a DB instance, and the instance status displays as rebooting.

If log_output =TABLE, run the following command to query the log tables:

Select * from mysql.slow_log
Select * from mysql.general_log

Note: Enabling table logging can affect the database performance for high throughput workload. For more information about table-based MySQL logs, see Managing table-based MySQL logs.


Did this article help?


Do you need billing or technical support?