Skip to content

Amazon RDS/Aurora Logs

You can publish database instance logs to Amazon CloudWatch Logs. Then, you can perform real-time analysis of the log data, store the data in highly durable storage, and manage the data with the CloudWatch Logs Agent.

Prerequisites

Make sure your database logs are enabled. Some databases logs are not enabled by default, and you need to update your database parameters to enable the logs.

Refer to How do I enable and monitor logs for an Amazon RDS MySQL DB instance? to learn how to output logs to CloudWatch Logs.

The table below lists the requirements for RDS/Aurora MySQL parameters.

Parameter Requirement
Audit Log The database instance must use a custom option group with the MARIADB_AUDIT_PLUGIN option.
General log The database instance must use a custom parameter group with the parameter setting general_log = 1 to enable the general log.
Slow query log The database instance must use a custom parameter group with the parameter setting slow_query_log = 1 to enable the slow query log.
Log output The database instance must use a custom parameter group with the parameter setting log_output = FILE to write logs to the file system and publish them to CloudWatch Logs.

Create log ingestion

You can create a log ingestion into Amazon OpenSearch Service either by using the Centralized Logging with OpenSearch console or by deploying a standalone CloudFormation stack.

Important

  • The RDS and CloudWatch region must be the same as the Centralized Logging with OpenSearch solution region.
  • The Amazon OpenSearch Service index is rotated on a daily basis by default, and you can adjust the index in the Additional Settings.

Using the Centralized Logging with OpenSearch Console

  1. Sign in to the Centralized Logging with OpenSearch Console.
  2. In the navigation pane, under Log Analytics Pipelines, choose Service Log.
  3. Choose the Create a log ingestion button.
  4. In the AWS Services section, choose Amazon RDS.
  5. Choose Next.
  6. Under Specify settings, choose Automatic or Manual for RDS log enabling. The automatic mode will detect your RDS log configurations and ingest logs from CloudWatch.
    • For Automatic mode, choose the RDS cluster from the dropdown list.
    • For Manual mode, enter the DB identifier, select the Database type and input the CloudWatch log location in Log type and location.
    • (Optional) If you are ingesting RDS/Aurora logs from another account, select a linked account from the Account dropdown first.
  7. Choose Next.
  8. In the Specify OpenSearch domain section, select an imported domain for Amazon OpenSearch domain.
  9. Choose Yes for Sample dashboard if you want to ingest an associated templated Amazon OpenSearch Service dashboard.
  10. You can change the Index Prefix of the target Amazon OpenSearch Service index if needed. The default prefix is the Database identifier.
  11. In the Log Lifecycle section, input the number of days to manage the Amazon OpenSearch Service index lifecycle. The Centralized Logging with OpenSearch will create the associated Index State Management (ISM) policy automatically for this pipeline.
  12. Choose Next.
  13. Add tags if needed.
  14. Choose Create.

Using the CloudFormation Stack

This automated AWS CloudFormation template deploys the Centralized Logging with OpenSearch - RDS Log Ingestion solution in the AWS Cloud.

Launch in AWS Console Download Template
AWS Regions Launch Stack Template
AWS China Regions Launch Stack Template

  1. Log in to the AWS Management Console and select the button to launch the AWS CloudFormation template. You can also download the template as a starting point for your own implementation.

  2. To launch the Centralized Logging with OpenSearch in a different AWS Region, use the Region selector in the console navigation bar.

  3. On the Create stack page, verify that the correct template URL shows in the Amazon S3 URL text box and choose Next.

  4. On the Specify stack details page, assign a name to your solution stack.

  5. Under Parameters, review the parameters for the template and modify them as necessary. This solution uses the following parameters.

    Parameter Default Description
    Log Bucket Name <Requires input> The S3 bucket name to export the logs.
    Log Bucket Prefix <Requires input> The S3 bucket path prefix which stores the the logs.
    Log Source Account ID <Optional> The AWS Account ID of the CloudWatch log group. Required for cross-account log ingestion (Please add a member account first). By default, the Account ID you logged in at Step 1 will be used.
    Log Source Region <Optional input> The AWS Region of the CloudWatch log group. By default, the Region you selected at Step 2 will be used.
    Log Source Account Assume Role <Optional input> The IAM Role ARN used for cross-account log ingestion. Required for cross-account log ingestion (Please add a member account first).
    Log Group Names <Requires input> The names of the CloudWatch log group for the logs.
    Engine Type OpenSearch The engine type of the OpenSearch. Select OpenSearch or Elasticsearch.
    OpenSearch Domain Name <Requires input> The domain name of the Amazon OpenSearch cluster.
    OpenSearch Endpoint <Requires input> The OpenSearch endpoint URL. For example, vpc-your_opensearch_domain_name-xcvgw6uu2o6zafsiefxubwuohe.us-east-1.es.amazonaws.com
    Index Prefix <requires input> The common prefix of OpenSearch index for the log. The index name will be <Index Prefix>-<Log Type>-<Other Suffix>.
    Create Sample Dashboard Yes Whether to create a sample OpenSearch dashboard.
    VPC ID <requires input> Select a VPC which has access to the OpenSearch domain. The log processing Lambda will be resides in the selected VPC.
    Subnet IDs <requires input> Select at least two subnets which has access to the OpenSearch domain. The log processing Lambda will resides in the subnets. Please make sure the subnets has access to the Amazon S3 service.
    Security Group ID <requires input> Select a Security Group which will be associated to the log processing Lambda. Please make sure the Security Group has access to the OpenSearch domain.
    S3 Backup Bucket <Requires input> The S3 backup bucket name to store the failed ingestion logs.
    KMS-CMK ARN <Optional input> The KMS-CMK ARN for SQS encryption. Leave it blank to create a new KMS CMK.
    Number Of Shards 5 Number of shards to distribute the index evenly across all data nodes. Keep the size of each shard between 10-50 GiB.
    Number of Replicas 1 Number of replicas for OpenSearch Index. Each replica is a full copy of an index.
    Age to Warm Storage <Optional> The age required to move the index into warm storage (e.g. 7d). Index age is the time between its creation and the present. Supported units are d (days) and h (hours). This is only effective when warm storage is enabled in OpenSearch.
    Age to Cold Storage <Optional> The age required to move the index into cold storage (e.g. 30d). Index age is the time between its creation and the present. Supported units are d (days) and h (hours). This is only effective when cold storage is enabled in OpenSearch.
    Age to Retain <Optional> The age to retain the index (e.g. 180d). Index age is the time between its creation and the present. Supported units are d (days) and h (hours). If value is "", the index will not be deleted.
    Rollover Index Size <Optional> The minimum size of the shard storage required to roll over the index (e.g. 30GB).
    Index Suffix yyyy-MM-dd The common suffix format of OpenSearch index for the log(Example: yyyy-MM-dd, yyyy-MM-dd-HH). The index name will be <Index Prefix>-<Log Type>-<Index Suffix>-000001.
    Compression type best_compression The compression type to use to compress stored data. Available values are best_compression and default.
    Refresh Interval 1s How often the index should refresh, which publishes its most recent changes and makes them available for searching. Can be set to -1 to disable refreshing. Default is 1s.

  6. Choose Next.

  7. On the Configure stack options page, choose Next.

  8. On the Review page, review and confirm the settings. Check the box acknowledging that the template creates AWS Identity and Access Management (IAM) resources.

  9. Choose Create stack to deploy the stack.

You can view the status of the stack in the AWS CloudFormation console in the Status column. You should receive a CREATE_COMPLETE status in approximately 15 minutes.

View dashboard

The dashboard includes the following visualizations.

Visualization Name Source Field Description
Controller
  • db-identifier
  • sq-table-name
This visualization allows users to filter data based on the db-identifier and sq-table-name fields.
Total Log Events Overview
  • db-identifier
  • log event
This visualization presents an overview of the total log events for the specified database ('db-identifier'). It helps monitor the frequency of various log events.
Slow Query History
  • log event
This visualization shows the historical data of slow query log events. It allows you to track the occurrences of slow queries and identify potential performance issues.
Average Slow Query Time History
  • Average sq-duration
This visualization depicts the historical trend of the average duration of slow queries ('sq-duration'). It helps in understanding the database's performance over time and identifying trends related to slow query durations.
Total Slow Queries
  • log event
This visualization provides the total count of slow queries in the log events. It gives an immediate view of how many slow queries have occurred during a specific time period, which is useful for assessing the database's performance and potential bottlenecks.
Average Slow Query Duration
  • Average sq-duration
This visualization shows the average duration of slow queries ('sq-duration') over time. It is valuable for understanding the typical performance of slow queries in the database.
Top Slow Query IP
  • sq-ip
  • sq-duration
This visualization highlights the IP addresses ('sq-ip') associated with the slowest queries and their respective durations ('sq-duration'). It helps identify sources of slow queries and potential areas for optimization.
Slow Query Scatter Plot
  • sq-duration
  • sq-ip
  • sq-query
This scatter plot visualization represents the relationship between the duration of slow queries ('sq-duration'), the IP addresses ('sq-ip') from which they originated, and the actual query content ('sq-query'). It helps in understanding query performance patterns and identifying potential issues related to specific queries and their sources.
Slow Query Pie
  • sq-query
This pie chart visualization shows the distribution of slow queries based on their content ('sq-query'). It provides an overview of the types of queries causing performance issues, allowing you to focus on optimizing specific query patterns.
Slow Query Table Name Pie
  • sq-table-name
This pie chart visualization displays the distribution of slow queries based on the table names ('sq-table-name') they access. It helps identify which tables are affected by slow queries, enabling targeted optimization efforts for specific tables.
Top Slow Query
  • sq-query
This visualization presents the slowest individual queries based on their content ('sq-query'). It is helpful in pinpointing specific queries that have the most significant impact on performance, allowing developers and administrators to focus on optimizing these critical queries.
Slow Query Logs
  • db-identifier
  • sq-db-name
  • sq-table-name
  • sq-query
  • sq-ip
  • sq-host-name
  • sq-rows-examined
  • sq-rows-sent
  • sq-id
  • sq-duration
  • sq-lock-wait
This visualization provides detailed logs of slow queries, including database ('sq-db-name'), table ('sq-table-name'), query content ('sq-query'), IP address ('sq-ip'), host name ('sq-host-name'), rows examined ('sq-rows-examined'), rows sent ('sq-rows-sent'), query ID ('sq-id'), query duration ('sq-duration'), and lock wait time ('sq-lock-wait'). It is beneficial for in-depth analysis and troubleshooting of slow query performance.
Total Deadlock Queries
  • log event
This visualization shows the total number of deadlock occurrences based on the log events. Deadlocks are critical issues that can cause database transactions to fail, and monitoring their frequency is essential for ensuring database stability.
Deadlock History
  • log event
This visualization displays the historical data of deadlock occurrences based on the log events. Understanding the pattern of deadlocks over time can help identify recurring issues and take preventive measures to reduce their impact on the database.
Deadlock Query Logs
  • db-identifier
  • log-detail
  • deadlock-ip-1
  • deadlock-action-1
  • deadlock-os-thread-handle-1
  • deadlock-query-1
  • deadlock-query-id-1
  • deadlock-thread-id-1
  • deadlock-user-1
  • deadlock-action-2
  • deadlock-ip-2
  • deadlock-os-thread-handle-2
  • deadlock-query-2
  • deadlock-query-id-2
  • deadlock-thread-id-2
  • deadlock-user-2
 This visualization provides detailed logs of deadlock occurrences
Total Error Logs
  • log event
This visualization presents the total count of error log events. Monitoring error logs helps identify database issues and potential errors that need attention and resolution.
Error History
  • log event
This visualization shows the historical data of error log events. Understanding the error patterns over time can aid in identifying recurring issues and taking corrective actions to improve the database's overall health and stability.
Error Logs
  • db-identifier
  • err-label
  • err-code
  • err-detail
  • err-sub-system
  • err-thread
 This visualization displays the error logs generated by the AWS RDS instance. It provides valuable insights into any errors, warnings, or issues encountered within the database system, helping to identify and troubleshoot problems effectively. Monitoring error logs is essential for maintaining the health and reliability of the database.
Audit History
  • log event
This visualization presents the audit history of the AWS RDS instance. It tracks the various log events and activities related to database access, modifications, and security-related events. Monitoring the audit logs is crucial for ensuring compliance, detecting unauthorized access, and keeping track of changes made to the database.
Audit Logs
  • db-identifier
  • audit-operation
  • audit-ip
  • audit-query
  • audit-retcode
  • audit-connection-id
  • audit-host-name
  • audit-query-id
  • audit-user
 This visualization provides an overview of the audit logs generated by the AWS RDS instance. It shows the operations performed on the database, including queries executed, connection details, IP addresses, and associated users. Monitoring audit logs enhances the security and governance of the database, helping to detect suspicious activities and track user actions.

Sample Dashboard

You can access the built-in dashboard in Amazon OpenSearch to view log data. For more information, see Access Dashboard.

You can click the below image to view the high-resolution sample dashboard.

rds-db