# MariaDB database log files
You can monitor the MariaDB error log, slow query log, the IAM database authentication error log, and the general log. The MariaDB error log is generated by default; you can generate the slow query and general logs by setting parameters in your DB parameter group. Amazon RDS rotates all of the MariaDB log files; the intervals for each type are given following.
You can monitor the MariaDB logs directly through the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs. You can also access MariaDB logs by directing the logs to a database table in the main database and querying that table. You can use the mysqlbinlog utility to download a binary log.
For more information about viewing, downloading, and watching file-based database logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
**Topics**
+ [Accessing MariaDB error logs](USER_LogAccess.MariaDB.Errorlog.md)
+ [Accessing the MariaDB slow query and general logs](USER_LogAccess.MariaDB.Generallog.md)
+ [Publishing MariaDB logs to Amazon CloudWatch Logs](USER_LogAccess.MariaDB.PublishtoCloudWatchLogs.md)
+ [Log rotation and retention for MariaDB](USER_LogAccess.MariaDB.LogFileSize.md)
+ [Managing table-based MariaDB logs](Appendix.MariaDB.CommonDBATasks.Logs.md)
+ [Configuring MariaDB binary logging](USER_LogAccess.MariaDB.BinaryFormat.md)
+ [Accessing MariaDB binary logs](USER_LogAccess.MariaDB.Binarylog.md)
+ [Enabling MariaDB binary log annotation](USER_LogAccess.MariaDB.BinarylogAnnotation.md)
# Accessing MariaDB error logs
The MariaDB error log is written to the `.err` file. You can view this file by using the Amazon RDS console, You can also retrieve the log using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs. The `.err` file is flushed every 5 minutes, and its contents are appended to `mysql-error-running.log`. The `mysql-error-running.log` file is then rotated every hour and the hourly files generated during the last 24 hours are retained. Each log file has the hour it was generated (in UTC) appended to its name. The log files also have a timestamp that helps you determine when the log entries were written.
MariaDB writes to the error log only on startup, shutdown, and when it encounters errors. A DB instance can go hours or days without new entries being written to the error log. If you see no recent entries, it's because the server did not encounter an error that resulted in a log entry.
# Accessing the MariaDB slow query and general logs
You can write the MariaDB slow query log and general log to a file or database table by setting parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). You must set these parameters before you can view the slow query log or general log in the Amazon RDS console or by using the Amazon RDS API, AWS CLI, or AWS SDKs.
You can control MariaDB logging by using the parameters in this list:
+ `slow_query_log` or `log_slow_query`: To create the slow query log, set to 1. The default is 0.
+ `general_log`: To create the general log, set to 1. The default is 0.
+ `long_query_time` or `log_slow_query_time`: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query run time to be logged, in seconds. The default is 10 seconds; the minimum is 0. If log\$1output = FILE, you can specify a floating point value that goes to microsecond resolution. If log\$1output = TABLE, you must specify an integer value with second resolution. Only queries whose run time exceeds the `long_query_time` or `log_slow_query_time` value are logged. For example, setting `long_query_time` or `log_slow_query_time` to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
+ `log_queries_not_using_indexes`: To log all queries that do not use an index to the slow query log, set this parameter to 1. The default is 0. Queries that do not use an index are logged even if their run time is less than the value of the `long_query_time` parameter.
+ `log_output option`: You can specify one of the following options for the `log_output` parameter:
+ **TABLE** (default)– Write general queries to the `mysql.general_log` table, and slow queries to the `mysql.slow_log` table.
+ **FILE**– Write both general and slow query logs to the file system. Log files are rotated hourly.
+ **NONE**– Disable logging.
When logging is enabled, Amazon RDS rotates table logs or deletes log files at regular intervals. This measure is a precaution to reduce the possibility of a large log file either blocking database use or affecting performance. `FILE` and `TABLE` logging approach rotation and deletion as follows:
+ When `FILE` logging is enabled, log files are examined every hour and log files older than 24 hours are deleted. In some cases, the remaining combined log file size after the deletion might exceed the threshold of 2 percent of a DB instance's allocated space. In these cases, the largest log files are deleted until the log file size no longer exceeds the threshold.
+ When `TABLE` logging is enabled, in some cases log tables are rotated every 24 hours. This rotation occurs if the space used by the table logs is more than 20 percent of the allocated storage space. It also occurs if the size of all logs combined is greater than 10 GB. If the amount of space used for a DB instance is greater than 90 percent of the DB instance's allocated storage space, the thresholds for log rotation are reduced. Log tables are then rotated if the space used by the table logs is more than 10 percent of the allocated storage space. They're also rotated if the size of all logs combined is greater than 5 GB.
When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If the backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.general_log` table is named `mysql.general_log_backup`. The backup log table for the `mysql.slow_log` table is named `mysql.slow_log_backup`.
You can rotate the `mysql.general_log` table by calling the `mysql.rds_rotate_general_log` procedure. You can rotate the `mysql.slow_log` table by calling the `mysql.rds_rotate_slow_log` procedure.
Table logs are rotated during a database version upgrade.
Amazon RDS records both `TABLE` and `FILE` log rotation in an Amazon RDS event and sends you a notification.
To work with the logs from the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs, set the `log_output` parameter to FILE. Like the MariaDB error log, these log files are rotated hourly. The log files that were generated during the previous 24 hours are retained.
For more information about the slow query and general logs, go to the following topics in the MariaDB documentation:
+ [Slow query log](http://mariadb.com/kb/en/mariadb/slow-query-log/)
+ [General query log](http://mariadb.com/kb/en/mariadb/general-query-log/)
# Publishing MariaDB logs to Amazon CloudWatch Logs
You can configure your MariaDB DB instance to publish log data to a log group in Amazon 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 Logs to store your log records in highly durable storage.
Amazon RDS publishes each MariaDB database log as a separate database stream in the log group. For example, suppose that you configure the export function to include the slow query log. Then slow query data is stored in a slow query log stream in the `/aws/rds/instance/my_instance/slowquery` log group.
The error log is enabled by default. The following table summarizes the requirements for the other MariaDB logs.
| Log | Requirement |
| --- | --- |
| Audit log | The DB instance must use a custom option group with the `MARIADB_AUDIT_PLUGIN` option. |
| General log | The DB instance must use a custom parameter group with the parameter setting `general_log = 1` to enable the general log. |
| Slow query log | The DB instance must use a custom parameter group with the parameter setting `slow_query_log = 1` or `log_slow_query = 1` to enable the slow query log. |
| IAM database authentication error log | You must enable the log type `iam-db-auth-error` for a DB instance by creating or modifying a DB instance. |
| Log output | The DB 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. |
## Console
**To publish MariaDB logs to CloudWatch Logs from the console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**, and then choose the DB instance that you want to modify.
1. Choose **Modify**.
1. In the **Log exports** section, choose the logs that you want to start publishing to CloudWatch Logs.
1. Choose **Continue**, and then choose **Modify DB Instance** on the summary page.
## AWS CLI
You can publish a MariaDB logs with the AWS CLI. You can call the [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command with the following parameters:
+ `--db-instance-identifier`
+ `--cloudwatch-logs-export-configuration`
**Note**
A change to the `--cloudwatch-logs-export-configuration` option is always applied to the DB instance immediately. Therefore, the `--apply-immediately` and `--no-apply-immediately` options have no effect.
You can also publish MariaDB logs by calling the following AWS CLI commands:
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html)
Run one of these AWS CLI commands with the following options:
+ `--db-instance-identifier`
+ `--enable-cloudwatch-logs-exports`
+ `--db-instance-class`
+ `--engine`
Other options might be required depending on the AWS CLI command you run.
**Example**
The following example modifies an existing MariaDB DB instance to publish log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `EnableLogTypes`, and its value is an array of strings with any combination of `audit`, `error`, `general`, and `slowquery`.
For Linux, macOS, or Unix:
```
1. aws rds modify-db-instance \
2. --db-instance-identifier mydbinstance \
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
```
For Windows:
```
1. aws rds modify-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
```
**Example**
The following command creates a MariaDB DB instance and publishes log files to CloudWatch Logs. The `--enable-cloudwatch-logs-exports` value is a JSON array of strings. The strings can be any combination of `audit`, `error`, `general`, and `slowquery`.
For Linux, macOS, or Unix:
```
1. aws rds create-db-instance \
2. --db-instance-identifier mydbinstance \
3. --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' \
4. --db-instance-class db.m4.large \
5. --engine mariadb
```
For Windows:
```
1. aws rds create-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' ^
4. --db-instance-class db.m4.large ^
5. --engine mariadb
```
## RDS API
You can publish MariaDB logs with the RDS API. You can call the [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) operation with the following parameters:
+ `DBInstanceIdentifier`
+ `CloudwatchLogsExportConfiguration`
**Note**
A change to the `CloudwatchLogsExportConfiguration` parameter is always applied to the DB instance immediately. Therefore, the `ApplyImmediately` parameter has no effect.
You can also publish MariaDB logs by calling the following RDS API operations:
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html)
Run one of these RDS API operations with the following parameters:
+ `DBInstanceIdentifier`
+ `EnableCloudwatchLogsExports`
+ `Engine`
+ `DBInstanceClass`
Other parameters might be required depending on the AWS CLI command you run.
# Log rotation and retention for MariaDB
When logging is enabled, Amazon RDS rotates table logs or deletes log files at regular intervals. This measure is a precaution to reduce the possibility of a large log file either blocking database use or affecting performance.
The MariaDB slow query log, error log, and the general log file sizes are constrained to no more than 2 percent of the allocated storage space for a DB instance. To maintain this threshold, logs are automatically rotated every hour and log files older than 24 hours are removed. If the combined log file size exceeds the threshold after removing old log files, then the largest log files are deleted until the log file size no longer exceeds the threshold.
Amazon RDS rotates IAM database authentication error log files larger than 10 MB. Amazon RDS removes IAM database authentication error log files that are older than five days or larger than 100 MB.
# Managing table-based MariaDB logs
You can direct the general and slow query logs to tables on the DB instance. To do so, create a DB parameter group and set the `log_output` server parameter to `TABLE`. General queries are then logged to the `mysql.general_log` table, and slow queries are logged to the `mysql.slow_log` table. You can query the tables to access the log information. Enabling this logging increases the amount of data written to the database, which can degrade performance.
Both the general log and the slow query logs are disabled by default. To enable logging to tables, you must also set the following server parameters to `1`:
+ `general_log`
+ `slow_query_log` or `log_slow_query`
Log tables keep growing until the respective logging activities are turned off by resetting the appropriate parameter to `0`. A large amount of data often accumulates over time, which can use up a considerable percentage of your allocated storage space. Amazon RDS doesn't allow you to truncate the log tables, but you can move their contents. Rotating a table saves its contents to a backup table and then creates a new empty log table. You can manually rotate the log tables with the following command line procedures, where the command prompt is indicated by `PROMPT>`:
```
PROMPT> CALL mysql.rds_rotate_slow_log;
PROMPT> CALL mysql.rds_rotate_general_log;
```
To completely remove the old data and reclaim the disk space, call the appropriate procedure twice in succession.
# Configuring MariaDB binary logging
The *binary log* is a set of log files that contain information about data modifications made to a MariaDB server instance. The binary log contains information such as the following:
+ Events that describe database changes such as table creation or row modifications
+ Information about the duration of each statement that updated data
+ Events for statements that could have updated data but didn't
The binary log records statements that are sent during replication. It is also required for some recovery operations. For more information, see [Binary Log](https://mariadb.com/kb/en/binary-log/) in the MariaDB documentation.
The automated backups feature determines whether binary logging is turned on or off for MariaDB. You have the following options:
Turn binary logging on
Set the backup retention period to a positive nonzero value.
Turn binary logging off
Set the backup retention period to zero.
For more information, see [Enabling automated backups](USER_WorkingWithAutomatedBackups.Enabling.md).
MariaDB on Amazon RDS supports the *row-based*, *statement-based*, and *mixed* binary logging formats. The default binary logging format is *mixed*. For details on the different MariaDB binary log formats, see [Binary Log Formats](http://mariadb.com/kb/en/mariadb/binary-log-formats/) in the MariaDB documentation.
If you plan to use replication, the binary logging format is important. This is because it determines the record of data changes that is recorded in the source and sent to the replication targets. For information about the advantages and disadvantages of different binary logging formats for replication, see [Advantages and Disadvantages of Statement-Based and Row-Based Replication](https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html) in the MySQL documentation.
**Important**
Setting the binary logging format to row-based can result in very large binary log files. Large binary log files reduce the amount of storage available for a DB instance. They also can increase the amount of time to perform a restore operation of a DB instance.
Statement-based replication can cause inconsistencies between the source DB instance and a read replica. For more information, see [Unsafe Statements for Statement-based Replication](https://mariadb.com/kb/en/library/unsafe-statements-for-statement-based-replication/) in the MariaDB documentation.
Enabling binary logging increases the number of write disk I/O operations to the DB instance. You can monitor IOPS usage with the `WriteIOPS` CloudWatch metric.
**To set the MariaDB binary logging format**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Parameter groups**.
1. Choose the parameter group that is used by the DB instance that you want to modify.
You can't modify a default parameter group. If the DB instance is using a default parameter group, create a new parameter group and associate it with the DB instance.
For more information on DB parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. For **Parameter group actions**, choose **Edit**.
1. Set the `binlog_format` parameter to the binary logging format of your choice (**ROW**, **STATEMENT**, or **MIXED**).
You can turn off binary logging by setting the backup retention period of a DB instance to zero, but this disables daily automated backups. Disabling automated backups turns off or disables the `log_bin` session variable. This disables binary logging on the RDS for MariaDB DB instance, which in turn resets the `binlog_format` session variable to the default value of `ROW` in the database. We recommend that you don't disable backups. For more information about the **Backup retention period** setting, see [Settings for DB instances](USER_ModifyInstance.Settings.md).
1. Choose **Save changes** to save the updates to the DB parameter group.
Because the `binlog_format` parameter is dynamic in RDS for MariaDB, you don't need to reboot the DB instance for the changes to apply.
**Important**
Changing a DB parameter group affects all DB instances that use that parameter group. If you want to specify different binary logging formats for different MariaDB DB instances in an AWS Region, the DB instances must use different DB parameter groups. These parameter groups identify different logging formats. Assign the appropriate DB parameter group to the each DB instance.
# Accessing MariaDB binary logs
You can use the mysqlbinlog utility to download binary logs in text format from MariaDB DB instances. The binary log is downloaded to your local computer. For more information about using the mysqlbinlog utility, go to [Using mysqlbinlog](http://mariadb.com/kb/en/mariadb/using-mysqlbinlog/) in the MariaDB documentation.
To run the mysqlbinlog utility against an Amazon RDS instance, use the following options:
+ Specify the `--read-from-remote-server` option.
+ `--host`: Specify the DNS name from the endpoint of the instance.
+ `--port`: Specify the port used by the instance.
+ `--user`: Specify a MariaDB user that has been granted the replication slave permission.
+ `--password`: Specify the password for the user, or omit a password value so the utility prompts you for a password.
+ `--result-file`: Specify the local file that receives the output.
+ Specify the names of one or more binary log files. To get a list of the available logs, use the SQL command SHOW BINARY LOGS.
For more information about mysqlbinlog options, go to [mysqlbinlog options](http://mariadb.com/kb/en/mariadb/mysqlbinlog-options/) in the MariaDB documentation.
The following is an example:
For Linux, macOS, or Unix:
```
mysqlbinlog \
--read-from-remote-server \
--host=mariadbinstance1.1234abcd.region.rds.amazonaws.com \
--port=3306 \
--user ReplUser \
--password \
--result-file=/tmp/binlog.txt
```
For Windows:
```
mysqlbinlog ^
--read-from-remote-server ^
--host=mariadbinstance1.1234abcd.region.rds.amazonaws.com ^
--port=3306 ^
--user ReplUser ^
--password ^
--result-file=/tmp/binlog.txt
```
Amazon RDS normally purges a binary log as soon as possible. However, the binary log must still be available on the instance to be accessed by mysqlbinlog. To specify the number of hours for RDS to retain binary logs, use the `mysql.rds_set_configuration` stored procedure. Specify a period with enough time for you to download the logs. After you set the retention period, monitor storage usage for the DB instance to ensure that the retained binary logs don't take up too much storage.
The following example sets the retention period to 1 day.
```
call mysql.rds_set_configuration('binlog retention hours', 24);
```
To display the current setting, use the `mysql.rds_show_configuration` stored procedure.
```
call mysql.rds_show_configuration;
```
# Enabling MariaDB binary log annotation
In a MariaDB DB instance, you can use the `Annotate_rows` event to annotate a row event with a copy of the SQL query that caused the row event. This approach provides similar functionality to enabling the `binlog_rows_query_log_events` parameter on an RDS for MySQL DB instance.
You can enable binary log annotations globally by creating a custom parameter group and setting the `binlog_annotate_row_events` parameter to **1**. You can also enable annotations at the session level, by calling `SET SESSION binlog_annotate_row_events = 1`. Use the `replicate_annotate_row_events` to replicate binary log annotations to the replica instance if binary logging is enabled on it. No special privileges are required to use these settings.
The following is an example of a row-based transaction in MariaDB. The use of row-based logging is triggered by setting the transaction isolation level to read-committed.
```
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE square(x INT PRIMARY KEY, y INT NOT NULL) ENGINE = InnoDB;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
INSERT INTO square(x, y) VALUES(5, 5 * 5);
COMMIT;
```
Without annotations, the binary log entries for the transaction look like the following:
```
BEGIN
/*!*/;
# at 1163
# at 1209
#150922 7:55:57 server id 1855786460 end_log_pos 1209 Table_map: `test`.`square` mapped to number 76
#150922 7:55:57 server id 1855786460 end_log_pos 1247 Write_rows: table id 76 flags: STMT_END_F
### INSERT INTO `test`.`square`
### SET
### @1=5
### @2=25
# at 1247
#150922 7:56:01 server id 1855786460 end_log_pos 1274 Xid = 62
COMMIT/*!*/;
```
The following statement enables session-level annotations for this same transaction, and disables them after committing the transaction:
```
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE square(x INT PRIMARY KEY, y INT NOT NULL) ENGINE = InnoDB;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION binlog_annotate_row_events = 1;
BEGIN;
INSERT INTO square(x, y) VALUES(5, 5 * 5);
COMMIT;
SET SESSION binlog_annotate_row_events = 0;
```
With annotations, the binary log entries for the transaction look like the following:
```
BEGIN
/*!*/;
# at 423
# at 483
# at 529
#150922 8:04:24 server id 1855786460 end_log_pos 483 Annotate_rows:
#Q> INSERT INTO square(x, y) VALUES(5, 5 * 5)
#150922 8:04:24 server id 1855786460 end_log_pos 529 Table_map: `test`.`square` mapped to number 76
#150922 8:04:24 server id 1855786460 end_log_pos 567 Write_rows: table id 76 flags: STMT_END_F
### INSERT INTO `test`.`square`
### SET
### @1=5
### @2=25
# at 567
#150922 8:04:26 server id 1855786460 end_log_pos 594 Xid = 88
COMMIT/*!*/;
```