Monitor errors in Amazon Aurora MySQL and Amazon RDS for MySQL using Amazon CloudWatch and send notifications using Amazon SNS

May 2023: This post was reviewed and updated for accuracy.

Monitoring databases is essential for any DBA, from dev-test databases to mission-critical databases. You want to capture system and user-defined events for monitoring and troubleshooting problems related to your database instance. MySQL records these events in error logs.

In this post, we show you how to monitor different events, such as deadlocks, access denied errors, and communications errors, in Amazon Aurora MySQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for MySQL using Amazon CloudWatch Logs send notifications using Amazon Simple Notification Service (Amazon SNS).

For information about using CloudWatch Logs to monitor databases in Amazon RDS for MySQL and MariaDB, check out Monitor Amazon Aurora MySQL, Amazon RDS for MySQL and MariaDB logs with Amazon CloudWatch.

Solution overview

Amazon Aurora MySQL-Compatible Edition and Amazon RDS for MySQL are managed database offerings from AWS that support the MySQL database engine. It takes a few clicks on the AWS Management Console to launch and connect to a MySQL database. Amazon RDS and Aurora instances are preconfigured with parameters and settings for the server type you choose. Both services can integrate with CloudWatch to provide metrics for instances, and both provide an option to export MySQL logs to publish to CloudWatch Logs.

With CloudWatch Logs, you can view metrics, analyze log data, and create real-time alarms. To work with CloudWatch Logs, we configure an RDS or Aurora instance to publish log data to a log group.

The following diagram illustrates the architecture of our solution, which sets up monitoring Amazon RDS or Aurora database error logs using CloudWatch and sending notifications with Amazon SNS.

Prerequisites

You should have the following prerequisites:

• Aurora MySQL or Amazon RDS for MySQL
• Access to the console and CloudWatch
• An email address for notifications
• MySQL Workbench or any tools to connect to your MySQL database

This post is applicable to Amazon RDS for MySQL Community Edition versions 5.6, 5.7, and 8.0 and Aurora MySQL 2.x and 1.x versions.

Walkthrough overview

You can configure your database instance to publish log data to a log group in CloudWatch Logs. With CloudWatch Logs, you can perform real-time analysis of the log data, and use CloudWatch to create alarms and view metrics. You can use CloudWatch to detect anomalous behavior in your environments, set alarms, visualize logs and metrics side by side, take automated actions, troubleshoot issues, and discover insights to keep your applications running smoothly.

You can configure your RDS for MySQL or Aurora MySQL database instance to publish log data to a log group in CloudWatch Logs. Then, you can perform real-time analysis of the log data. You can create a metrics filter based on the content of the log file and create alarms based on it.

In this post, we complete the following high-level steps:

1. Publish database logs to CloudWatch Logs from the console.
2. Create a filter pattern and CloudWatch alarm.

Then we walk you through use cases of enabling deadlock capturing events, checking communication errors, and monitoring for access denied errors.

Publish database logs to CloudWatch Logs

To publish your database logs to CloudWatch Logs, complete the following steps:

1. On the Amazon RDS console, choose Databases in the navigation pane.
2. Choose the name of the DB instance that you want to configure CloudWatch metrics for.
3. Choose Modify.
4. On the CloudWatch console, choose Log groups in the navigation pane.
5. In the Log exports section, select Error log to send the logs to CloudWatch Logs.

6. Choose Continue.
7. In the Scheduling of modifications section, choose Apply immediately, then choose Modify DB instance.

If you’re using Aurora, the database will remain online and no downtime is needed.

Create a filter pattern and CloudWatch alarm

To create a filter pattern and CloudWatch alarm, complete the following steps:

1. On the CloudWatch console, under Logs in the navigation pane, choose Log groups.
2. Select the RDS for MySQL error logs for your RDS DB instance (/aws/rds/instance/<Your-RDS-Instance-Name>/error).

If you’re using Aurora, a new log group is automatically created for the Aurora DB cluster under the prefix /aws/rds/cluster/cluster-name/log_type, in which cluster-name represents the DB cluster name, and log_type represents the log type.

3. Choose Create metric filter.
4. In the Filter Pattern section, enter the word you want to monitor in the log file, such as deadlock/error/shutdown.
5. Choose Assign metric.
6. For Filter name and Metric name, enter deadlock.
7. Set the metric value to 1.
8. Choose Create filter.

9. After the metric filter is created, choose Create alarm.
10. On the Specify metric and conditions page, under Metric name, select name of metric (deadlock).

For Aurora MySQL, an instance-level deadlock metric is available in CloudWatch, which you can use to create an alarm in place of log filtering if necessary.

11. Under Statistic, choose Minimum.
12. Under Period, choose the time period for the alarm, for example, 1 minute.
13. In the Conditions section, for Threshold type, choose Static.
14. For Whenever Deadlock is, choose Greater > threshold.
15. For Than, enter 0.
16. Choose Next.
17. In the Notification section, under Alarm state trigger, select In alarm.
18. For Select an SNS topic, you can choose an existing topic or create a new topic using the email address you want to receive alerts.

For more information about SNS topics, see Subscribing to an Amazon SNS topic.

19. Choose Next.

20. In the Name and description section, enter an alarm name and description, and choose Next.
21. On the Preview and create page, review your alarm configuration, then choose Create alarm.
22. Confirm the notification in the email.

Capture deadlock events for Amazon RDS for MySQL

A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE or SELECT … FOR UPDATE), but in the opposite order. To capture deadlock events, complete the following steps:

1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
2. Choose Create parameter group.
3. For Parameter group family, choose the MySQL and edition you’re using.
4. Enter a group name and description.
5. Choose Create.

6. On the Parameter groups page, choose the group you created.
7. Choose Edit parameters, and select deadlock – innodb_print_all_deadlocks and innodb_deadlock_detect.
8. Edit the Values for both parameters to 1.

You can update Innodb_deadlock_detect parameter using the DB parameter group in Amazon Aurora. Enabling this parameter can cause a performance impact on high throughput workloads.

9. Choose Preview changes.
10. On the next page, choose Save changes.
11. In the navigation pane, choose Databases.
12. In the DB identifier section, choose your RDS DB instance.
13. Choose Modify.
14. In the Database options section, for DB parameter group, choose the parameter group you created.

Creating a new parameter group and attaching it to an RDS DB instance requires that you reboot the RDS DB instance for the changes to take effect.

If you have a custom parameter group attached to the RDS DB instance, you can update deadlock parameters. Deadlock parameters are dynamic hence it doesn’t require a reboot of DB instances.

Now let’s simulate a deadlock in your MySQL database.

15. Connect to your MySQL database using MySQL Workbench or any preferred tool.
16. Create two query connections to the same MySQL instance and run the following commands in each connection.

CREATE DATABASE test;

USE test;
CREATE TABLE `table1` 
( `id` INT NOT NULL AUTO_INCREMENT, 
`name` VARCHAR(255) NOT NULL, 
`marks` INT NOT NULL, 
PRIMARY KEY (`id`) ) ENGINE=InnoDB;

INSERT INTO table1 (id, name, marks) 
VALUES (1, "abc", 5);
INSERT INTO table1 (id, name, marks) 
VALUES (2, "xyz", 1);

USE test;

BEGIN;

BEGIN;

UPDATE table1 SET marks=marks-1 
WHERE id=1; 
-- X lock acquired on session 1

UPDATE table1 SET marks=marks+1 
WHERE id=2;
-- X lock acquired on session 2

UPDATE table1 SET marks=marks-1 
WHERE id=1; 
-- LOCK WAIT!

UPDATE table1 SET marks=marks+1 
WHERE id=2;
--DEADLOCK!

You can see the deadlock detected error message as command output in the MySQL Workbench query output.

17. Now, go to CloudWatch alarms and check the alarm status.

You can also verify the email received from Amazon SNS. The following screenshot is the email that you receive when a deadlock is detected.

18. After you receive the email, you can go to MySQL Workbench to get the details of the queries involved in deadlock.
19. Run the following query using MySQL Workbench:

show engine innodb status

The following screenshot shows the output.

To learn more about deadlocks and how to handle deadlocks in MySQL, see How to Minimize and Handle Deadlocks.

Check communication errors

Another common error found in MySQL logs is related to communication errors.

If a client can’t connect, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for the following reasons:

• A client attempts to access a database but has no privileges for it
• A client uses an incorrect password
• A connection packet doesn’t contain the right information
• It takes more than connect_timeout seconds to obtain a connect packet

If a client successfully connects but later disconnects improperly or is stopped, the server increments the Aborted_clients status variable, and logs a stopped connection message to the error log. The cause can be any of the following:

• The client program didn’t call mysql_close() before exiting
• The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server
• The client program ended abruptly in the middle of a data transfer

To follow communication error capturing events, we first enable detailed log monitoring in Amazon RDS for MySQL.

1. Create a custom parameter group and edit the parameter log_error_verbosity and set the value to 3.

For Aurora, this value is already 3; the default in Amazon RDS for MySQL is 1. Also, the parameter log_error_verbosity doesn’t exist in Aurora 5.6 (1.x); you can use the log_warnings parameter.

2. Attach the customer parameter group to Amazon RDS for MySQL.
3. Reproduce the problem by connecting to the MySQL database with the wrong password or keep the database connection open and restart the database in the middle of a data transfer.
4. Check the status of Aborted_clients and Aborted_Connections using the show global status

For additional connection-related information, check the Connection_errors_xxx status variables and the host_cache table.

Events are written to the MySQL error log and it is available in CloudWatch log events. You can create a metric filter and create an alert accordingly.

Monitor access denied errors

You can also monitor another set of common issues related to MySQL servers, which are access denied errors.

The following screenshot shows a sample event produced for an access denied error.

You can create a filter for access denied errors in a CloudWatch log metrics filter and send SNS notifications. For more information about using audit logging to get more information on connection errors, see Installing or Uninstalling MySQL Enterprise Audit.

Clean up

If you’re no longer using the resources in this post, you can clean up to avoid incurring unwanted charges. Delete the Aurora or RDS for MySQL instance and CloudWatch Logs.

Conclusion

In this post, we showed how to monitor an Aurora MySQL or Amazon RDS for MySQL database for any errors and integrate monitoring with CloudWatch Events and Amazon SNS. With this solution, you can automate your Aurora MySQL or Amazon RDS for MySQL error log files monitoring and alerting.

In our use case, you received email notifications for deadlock events in Aurora MySQL or RDS for MySQL databases. This helps automate deadlock reporting and can take appropriate actions to resolve deadlock conflicts.

You can use a similar setup for monitoring other Aurora MySQL or Amazon RDS for MySQL errors, such as access errors, communication errors, or any other Innodb errors.

Use this link to find more posts about monitoring databases with Amazon CloudWatch.

To learn more about monitoring Aurora MySQL or Amazon RDS for MySQL, check out Monitoring OS metrics using Enhanced Monitoring and Monitoring with Performance Insights on Amazon RDS.

About the Author

Yogeshwari Barot is Microsoft Specialist Senior Solution Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.