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

Last updated: 2022-01-07

I want to activate 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

First, if you don't have a customer DB parameter group associated with your MySQL instance, create a custom DB parameter group and modify the parameter. Then, associate the parameter group with your MySQL instance.

If you already have a custom DB parameter group associated with the RDS instance, then proceed with modifying the required parameters.

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

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 both the general and the slow query logs to a table so you can view these logs with a SQL 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.

View the log

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.

If log_output =FILE, view database log files for your DB engine using the AWS Management Console.

Note: Error logs are stored as files and are not affected by the log_output parameter.


Did this article help?


Do you need billing or technical support?