Amazon RDS/Aurora logs - Centralized Logging with OpenSearch
Create log ingestion (OpenSearch Engine)Create log ingestion (Light Engine)

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 must 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 following table lists the requirements for Amazon 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.

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

Create log ingestion (OpenSearch Engine)

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 Amazon RDS log configurations and ingest logs from CloudWatch.

    • For Automatic mode, choose the Amazon 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 Amazon 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 the Amazon OpenSearch Service 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. In the Log processor settings section, choose Log processor type, configure the Lambda concurrency if needed, and then 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 Management Console Download Template
AWS Regions

Launch stack button.

Template
AWS China Regions

Launch stack button.

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 that stores the logs.
    Log Source Account ID Optional input 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 (add a member account first).
    KMS-CMK ARN Optional input The KMS-CMK ARN for Amazon SQS encryption. Leave it blank to create a new AWS KMS key.
    Enable OpenSearch Ingestion as processor Optional input Ingestion table ARN. Leave empty if you do not use OSI as Processor.
    Log Group Names <Requires input> The names of the CloudWatch log group for the logs.
    Amazon S3 Backup Bucket <Requires input> The Amazon S3 backup bucket name to store the failed ingestion logs.
    Engine Type OpenSearch The engine type of the OpenSearch.
    OpenSearch Domain Name <Requires input> The domain name of the Amazon OpenSearch Service 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 that 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 that has access to the OpenSearch domain. The log processing Lambda function will reside in the subnets. Please make sure that the subnetshas access to the Amazon S3 service.
    Security Group ID <Requires input> Select a Security Group that will be associated to the log processing Lambda. Please make sure that the Security Group has access to the OpenSearch domain.
    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 input The age required to move the index into warm storage (for example, 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 input The age required to move the index into cold storage (for example, 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 input The age to retain the index (for example, 180d). Index age is the time between its creation and the present. Supported units are d (days) and h (hours). If the value is "", the index will not be deleted.
    Rollover Index Size Optional input The minimum size of the shard storage required to roll over the index (for example, 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.
    EnableS3Notification True An option to enable or disable notifications for Amazon S3 buckets. The default option is recommended for most cases.
    LogProcessorRoleName Optional input Specify a role name for the log processor. The name should NOT duplicate an existing role name. If no name is specified, a random name is generated.
    QueueName Optional input Specify a queue name for an Amazon SQS queue. The name should NOT duplicate an existing queue name. If no name is given, a random name will be generated.

  6. Choose Next.

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

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

  9. Choose Submit 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'), hostname ('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 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 Amazon 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 Amazon 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 compliance, detecting unauthorized access, and tracking 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 Amazon 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.

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

Amazon RDS/Aurora logs sample dashboard.

Create log ingestion (Light Engine)

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 Light Engine, choose Next.

  6. Under Specify settings, choose Automatic or Manual for RDS log enabling. The automatic mode will detect your Amazon RDS log configurations and ingest logs from CloudWatch.

    • For Automatic mode, choose the Amazon 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 Amazon RDS/Aurora logs from another account, select a linked account from the Account dropdown first.

  7. Choose Next.

  8. In the Specify Light Engine Configuration section, if you want to ingest associated templated Grafana dashboards, select Yes for the sample dashboard.

  9. You can choose an existing Grafana, or if you must import a new one, you can go to Grafana for configuration.

  10. Select an S3 bucket to store partitioned logs and define a name for the log table. We have provided a predefined table name, but you can modify it according to your business needs.

  11. If needed, change the log processing frequency, which is set to 5 minutes by default, with a minimum processing frequency of 1 minute.

  12. In the Log Lifecycle section, enter the log merge time and log archive time. We have provided default values, but you can adjust them based on your business requirements.

  13. Select Next.

  14. If desired, add tags.

  15. Select 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 Management Console Download Template
AWS Regions

Launch stack button.

Template
AWS China Regions

Launch button.

Template

  1. Log in to the AWS Management Console and select the preceding 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 stack 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.

    1. Parameters for Pipeline settings

      Parameter Default Description
      Pipeline Id <Requires input> The unique identifier for the pipeline is essential if you must create multiple Application Load Balancer pipelines and write different Application Load Balancer logs into separate tables. For uniqueness, you can generate a unique pipeline identifier using uuidgenerator.
      Staging Bucket Prefix AWSLogs/RDS The storage directory for logs in the temporary storage area should ensure the uniqueness and non-overlapping of the Prefix for different pipelines.

    2. Parameters for Destination settings

      Parameter Default Description
      Centralized Bucket Name <Requires input> Centralized S3 bucket name. For example, centralized-logging-bucket.
      Centralized Bucket Prefix datalake Centralized bucket prefix. By default, the data base location is s3://{Centralized Bucket Name}/{Centralized Bucket Prefix}/amazon_cl_centralized.
      Centralized Table Name rds Table name for writing data to the centralized database. You can modify it if needed.

    3. Parameters for Scheduler settings

      Parameter Default Description
      LogProcessor Schedule Expression rate(5 minutes) Task scheduling expression for performing log processing, with a default value of executing the LogProcessor every 5 minutes. Configuration for reference.
      LogMerger Schedule Expression cron(0 1 * ? ) Task scheduling expression for performing log merging, with a default value of executing the LogMerger at 1 AM every day. Configuration for reference.
      LogArchive Schedule Expression cron(0 2 * ? ) Task scheduling expression for performing log archiving, with a default value of executing the LogArchive at 2 AM every day. Configuration for reference.
      Age to Merge 7 Small file retention days, with a default value of 7, indicating that logs older than 7 days will be merged into small files. It can be adjusted as needed.
      Age to Archive 30 Log retention days, with a default value of 30, indicating that data older than 30 days will be archived and deleted. It can be adjusted as needed.

    4. Parameters for Notification settings

      Parameter Default Description
      Notification Service SNS Notification method for alerts. If your main stack is using China, you can only choose the SNS method. If your main stack is using Global, you can choose either the SNS or SES method.
      Recipients <Requires Input> Alert notification: If the Notification Service is SNS, enter the SNS Topic ARN here, ensuring that you have the necessary permissions. If the Notification Service is SES, enter the email addresses separated by commas here, ensuring that the email addresses are already Verified Identities in SES. The adminEmail provided during the creation of the main stack will receive a verification email by default.

    5. Parameters for Dashboard settings

      Parameter Default Description
      Import Dashboards FALSE Whether to import the Dashboard into Grafana, with a default value of false. If set to true, you must provide the Grafana URL and Grafana Service Account Token.
      Grafana URL <Requires Input> Grafana access URL，for example: https://alb-72277319.us-west-2.elb.amazonaws.com.
      Grafana Service Account Token <Requires Input> Grafana Service Account Token：Service Account Token created in Grafana.

  6. Choose Next.

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

  8. On the Review and create page, review and confirm the settings. Check the box acknowledging that the template creates IAM resources.

  9. Choose Submit 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 10 minutes.

View dashboard

Amazon RDS/Aurora logs sample dashboard.