MySQL RDS – Publish logs in Cloudwatch

You can access the logs that are produced by each RDS database engine for auditing and troubleshooting. Your database engine determines the kind of logs you need.

Using the Amazon RDS API, the AWS Management Console, or the AWS Command Line Interface (AWS CLI), you can retrieve database logs. Transaction logs cannot be seen, watched, or downloaded.

The database logs for an on-premises database are kept on the file system. The database logs on your DB instance’s file system cannot be accessed by Amazon RDS hosts. Amazon RDS enables you to export database logs to Amazon CloudWatch Logs as a result. You may analyze the log data in real time with CloudWatch Logs. Additionally, you can use the CloudWatch Logs Agent to manage the data and store it on extremely resilient storage.

Use Amazon CloudWatch Logs to track, store, and access your log files from Route 53, AWS CloudTrail, and Amazon Elastic Compute Cloud (Amazon EC2) instances, among other places.

In order to publish the logs to Cloudwatch, modify the RDS instance and apply the following.

• “Enhanced Monitoring” must be turned ON.
• IAM role (RDS-Monitoring-Role) is selected for monitoring.

And apply the following changes in the Parameter Group –

• activate_all_roles_on_login — Should be changed from 0 to 1
• general_log — Should be changed from 0 to 1
• log_output — Should be changed from TABLE to FILE
• slow_query_log — Should be changed from 0 to 1

Although a reboot is not necessary, give it a shot if the modifications don’t take. The aforementioned implementations will aid in log publication to Cloudwatch. But Audit logs are not included in this.

Follow the instructions below to publish the audit logs for Cloudwatch.

Create the “Options” group with “Audit plugin”

Screenshot-1: 

• Enter a name (for example, audit-mysql-8-0).
• Enter a description.
• Choose MySQL or your preferred engine.
  Available engines are as shown in the below screenshot-2)

Screenshot-2:

• Choose your desired engine version (for MySQL, 5.6, 5.7 and 8.0 are available as of now).
• Click on Create.
• Go back to the Option groups page and select the option group you just created. This can be edited or deleted or additional options can be added. Refer to the Screenshot-3.
• Click on “Add option”.

• There is a dropdown menu for “Option name”. Select “MariaDB_Audit_Plugin”. This is for MySQL as well.

• It is followed by an Option setting.
  SERVER_AUDIT: This setting is enabled for security and ensures that the plugin cannot be uninstalled at runtime. Allowed values are FORCE_PLUS_PERMANENT. SERVER_AUDIT_EVENTS: This is types of actions to be logged (connect, query, or table) Allowed values are – CONNECT, QUERY, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT
  SERVER_AUDIT_EXCL_USERS: This is to exclude specified users, by default NULL
  SERVER_AUDIT_FILE_PATH: This is for file location for logs – By default – /rdsdbdata/log/audit/
  SERVER_AUDIT_FILE_ROTATE_SIZE: The default range of values allowed is  1-1000000000
• Once you fill the required information, you can proceed to apply the changes to the Option-Group. It can be applied immediately or during a next reboot.
• Now, go back to the RDS dashboard, Select the DB instance that you want to use to export log data to CloudWatch.
• Choose Modify.
• Go to “Additional configuration” section in which the options can be changed such as Database options, backup turned on, Performance Insights turned on, Enhanced Monitoring turned off, maintenance, CloudWatch Logs, delete protection turned off.
• Change the Option group to the one you created. As per the example it is – audit-mysql-8-0.

Publish audit logs to CloudWatch

Hope you find this article informative.