Aurora MySQL database logs

You can monitor the following types of Aurora MySQL log files:

·         Error log

·         Slow query log

·         General log

·         Audit log

Error log :

The mysql-error.log file is where Aurora MySQL records errors. However, it only writes to the error log during startup, shutdown, and when encountering errors. This design ensures that the log focuses on unexpected events, such as errors, while omitting certain database information like query progress.

It's important to note that the error logs may increase in size due to ongoing database activities, even if no actual errors occur. Therefore, when viewing the error logs in the AWS Management Console, the displayed size in bytes or kilobytes may differ from the files you download, which could have a size of 0 bytes.

Aurora MySQL saves the mysql-error.log file to disk approximately every 5 minutes. The contents of the log are appended to the mysql-error-running.log file. This rotation process occurs every hour, ensuring that the log files are regularly managed and organized.

To access the error log for an Aurora MySQL database, you can follow these steps:

1.  Open the AWS Management Console and navigate to the Amazon RDS service.

2.  Select your Aurora MySQL database instance from the list.

3.  In the "Details" section, locate the "Logs & Events" tab and click on it.

4.  Under the "Logs" tab, you'll find a section called "Logs". Look for the file named "error/mysql-error-running.log". Select the log and click View or Download the Log file.

Alternatively, you can also access the error log using the AWS Command Line Interface (CLI) or an SSH connection to the Aurora MySQL instance. Here's how to do it using the CLI:

Open your terminal or command prompt.

1.  Use the AWS CLI command to retrieve the error log:

2.  aws rds download-db-log-file-portion --db-instance-identifier <your-db-instance-identifier> --log-file-name error/mysql-error-running.log

```

Replace `<your-db-instance-identifier>` with the identifier of your Aurora MySQL database instance.

3.  The command will return a portion of the error log file. If the log is large, you may need to use the --marker parameter to retrieve additional portions of the log.

Slow Query Log:

The slow query log in Aurora MySQL is a feature that allows you to capture and analyze slow-running queries in your database. It provides valuable insights into queries that take longer to execute, helping you identify performance bottlenecks and optimize your database.

Here are some key points about the Aurora slow query log:

1.  Activation: By default, the slow query log is not enabled in Aurora MySQL. You need to explicitly enable it to start capturing slow queries.

2.  Configuration: To enable the slow query log, you can modify the parameter group associated with your Aurora cluster. Set the slow_query_log parameter to 1 to enable logging.

3.  Threshold: The slow query log captures queries that exceed a predefined execution time threshold. You can specify this threshold using the long_query_time parameter, which represents the minimum duration in seconds that a query must exceed to be logged.

4.  Log Output: The slow query log records information about each slow query, including the query itself, execution time, timestamp, and other relevant details. The log is stored in a file named slowquery/mysql-slowquery.log within the RDS instance associated with your Aurora cluster.

5.  log_queries_not_using_indexes: To enable logging of queries that do not utilize indexes in the slow query log, you can set the log_queries_not_using_indexes parameter to 1. This configuration ensures that queries lacking index usage, regardless of their runtime, are recorded in the slow query log. By default, this parameter is set to 0.

6.  Log Rotation: The slow query log file is automatically rotated by Aurora MySQL. The rotation process occurs when the log file reaches a certain size or after a specified time interval. Rotated log files are compressed and retained for a configurable period, allowing you to access historical slow query logs.

7.  Analysis: Once you have enabled the slow query log and captured some queries, you can review the log to identify problematic queries. Analyzing the slow query log helps you pinpoint queries that require optimization, such as adding indexes, rewriting queries, or adjusting database configuration parameters.

Remember to disable the slow query log when you no longer need it to avoid unnecessary performance overhead.

By leveraging the Aurora slow query log, you can gain valuable insights into the performance of your database and make informed decisions to optimize query execution and improve overall database efficiency.

General Log:

The general log in Aurora MySQL is a feature that captures a record of all SQL statements executed on the database. It provides a detailed history of the queries and actions performed, allowing you to analyze the database's activity and troubleshoot any issues that may arise.

Here are some important points about the Aurora general log:

1.  Activation: By default, the general log is not enabled in Aurora MySQL. You need to enable it explicitly to start recording SQL statements.

2.  Configuration: To enable the general log, you can modify the parameter group associated with your Aurora cluster. Set the general_log parameter to 1 to enable logging.

3.  Log Output: The general log records every SQL statement executed on the database, including queries, DDL statements (e.g., CREATE, ALTER), DML statements (e.g., INSERT, UPDATE, DELETE), and administrative actions. The log captures details such as the timestamp, connection ID, and the actual SQL statement.

4.  Log Location: The general log file is stored in a file named general/mysql-general.log within the RDS instance associated with your Aurora cluster.

5.  Log Rotation: Aurora MySQL automatically rotates the general log file to prevent it from growing indefinitely. The rotation process occurs when the log file reaches a certain size or after a specified time interval. Rotated log files are compressed and retained for a configurable period, allowing you to access historical general logs.

6.  Analysis: Once you have enabled the general log and captured SQL statements, you can review the log to understand the database's activity and diagnose any issues. Analyzing the general log helps you identify slow queries, track changes made to the database, detect unauthorized access attempts, and gather information for performance tuning or debugging purposes.

7.  Caution: Enabling the general log can have a significant impact on database performance and disk space usage. It is recommended to enable the general log only for specific troubleshooting or auditing purposes and disable it when not required.

By utilizing the Aurora general log, you can gain a comprehensive understanding of the SQL activity within your database, enabling you to monitor and troubleshoot effectively and make informed decisions for optimizing and maintaining your Aurora MySQL cluster.

log_output option: 

The log_output parameter in Aurora MySQL provides different options for specifying the destination of log output.

Here are the available options for the log_output parameter:

·         TABLE: With log_output set to TABLE, general queries are logged to the mysql.general_log table, while slow queries are logged to the mysql.slow_log table.

·         FILE: When log_output is set to FILE, both general and slow query logs are written to the file system.

·         NONE: Setting log_output to NONE disables logging, meaning no logs are generated.

In Aurora MySQL version 2, the default value for log_output is FILE.

Audit Log:

Amazon Aurora Advanced Auditing is a feature that provides enhanced auditing capabilities for Amazon Aurora databases. It enables you to capture a more detailed record of database activities, including user actions, system events, and data access, to meet compliance requirements, monitor database activity, and investigate security incidents.

Here are key points about Amazon Aurora Advanced Auditing:

1.  Activation: Advanced Auditing must be enabled at the cluster level to start capturing audit logs. You can enable it during cluster creation or modify an existing cluster to enable auditing. The parameter server_audit_logging  defaults to OFF; set it to ON to enable Advanced Auditing.

2.  Audit Logs: Advanced Auditing captures detailed audit logs containing information such as the timestamp, user, IP address, SQL statement, and affected table or object for each audited event.

3.  Event Categories: You can choose specific event categories to audit using parameter server_audit_events.  Events must be specified in all caps, and there should be no white space between the list elements, for example: CONNECT,QUERY_DDL.

You can log any combination of the following events:

·         CONNECT – Logs both successful and failed connections and also disconnections. This event includes user information.

·         QUERY – Logs all queries in plain text, including queries that fail due to syntax or permission errors.

·         QUERY_DCL – Similar to the QUERY event, but returns only data control language (DCL) queries (GRANT, REVOKE, and so on).

·         QUERY_DDL – Similar to the QUERY event, but returns only data definition language (DDL) queries (CREATE, ALTER, and so on).

·         QUERY_DML – Similar to the QUERY event, but returns only data manipulation language (DML) queries (INSERT, UPDATE, and so on, and also SELECT).

·         TABLE – Logs the tables that were affected by query execution.

4.  Integration with CloudWatch Logs: The audit logs can be integrated to Amazon CloudWatch Logs, where you can store, analyze, and set up alerts for specific audit events.

5.  Filtering and Customization: You can configure filters to include or exclude specific users, IP addresses, or SQL statements from the audit logs. Parameter server_audit_incl_users and server_audit_excl_users used to include or exclude specific users.

6.  Compliance Requirements: Amazon Aurora Advanced Auditing helps meet regulatory compliance requirements by providing a detailed audit trail of database activities, which can be useful for audits and security assessments.

7.  Performance Impact: Enabling Advanced Auditing may have a performance impact on the database due to the additional overhead of capturing and processing audit logs. It is recommended to carefully consider the performance implications and test the impact on your workload.

By leveraging Amazon Aurora Advanced Auditing, you can enhance the security and compliance posture of your Aurora database by capturing and analyzing detailed audit logs of database activities. It allows you to gain insights into user behavior, detect anomalies, and maintain a robust audit trail for your database environment.