MariaDB Database Log Files
You can monitor the MariaDB error log, slow query 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 Amazon RDS Database Log Files.
Accessing MariaDB Error Logs
The MariaDB error log is written to the
<host-name>.err file. You
can view this file by using the Amazon RDS console or by retrieving the log using the
Amazon RDS API, Amazon RDS CLI, or AWS SDKs. The
file is flushed every 5 minutes, and its contents are appended to
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
The MariaDB slow query log and the general log can be written to a file or a database table by setting parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see Working with DB Parameter Groups. 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, Amazon RDS CLI, or AWS SDKs.
You can control MariaDB logging by using the parameters in this list:
slow_query_log: 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: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query execution time to be logged, in seconds. The default is 10 seconds; the minimum is 0. If log_output = FILE, you can specify a floating point value that goes to microsecond resolution. If log_output = TABLE, you must specify an integer value with second resolution. Only queries whose execution time exceeds the
long_query_timevalue are logged. For example, setting
long_query_timeto 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 execution time is less than the value of the
log_output: You can specify one of the following options for the
TABLE (default)– Write general queries to the
mysql.general_logtable, and slow queries to the
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
TABLE logging approach rotation and
deletion as follows:
FILElogging is enabled, log files are examined every hour and log files older than 24 hours are deleted. If the remaining combined log file size after the deletion exceeds a threshold of 2 percent of a DB instance's allocated space, then the largest log files are deleted until the log file size no longer exceeds the threshold.
TABLElogging is enabled, log tables are rotated every 24 hours if the space used by the table logs is more than 20 percent of the allocated storage space or 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, then 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 or 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_logtable is named
mysql.general_log_backup. The backup log table for the
mysql.slow_logtable is named
You can rotate the
mysql.general_logtable by calling the
mysql.rds_rotate_general_logprocedure. You can rotate the
mysql.slow_logtable by calling the
Table logs are rotated during a database version upgrade.
Amazon RDS records both
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:
Log File Size
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.
Managing Table-Based MariaDB Logs
You can direct the general and slow query logs to tables on the DB instance by creating a
DB parameter group and setting the
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. In order to enable
logging to tables, you must also set the
slow_query_log server parameters to
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 does not 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> 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.
Binary Logging Format
MariaDB on Amazon RDS supports the row-based and mixed binary log formats, and does not support the statement-based binary log format. The default binary logging format is mixed. For details on the different MariaDB binary log formats, see Binary Log Formats in the MariaDB documentation.
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 and can increase the amount of time to perform a restore operation of a DB instance.
To set the MariaDB binary logging format
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
Create a new parameter group, following the instructions in Creating a DB Parameter Group.
Choose the new parameter group, and then choose Go to Details Page.
Choose Edit Parameters to modify the parameters in the DB parameter group.
binlog_formatparameter to the binary logging format of your choice, MIXED or ROW.
Choose Save Changes to save the updates to the DB parameter group.
For more information on DB parameter groups, see Working with DB Parameter Groups.
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 in the MariaDB documentation.
To run the mysqlbinlog utility against an Amazon RDS instance, use the following options:
--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 in the MariaDB documentation.
The following is an example:
For Linux, OS X, or Unix:
mysqlbinlog \ --read-from-remote-server \ --host=mariadbinstance1.1234abcd.region.rds.amazonaws.com \ --port=3306 \ --user ReplUser \ --password <password> \ --result-file=/tmp/binlog.txt
mysqlbinlog ^ --read-from-remote-server ^ --host=mariadbinstance1.1234abcd.region.rds.amazonaws.com ^ --port=3306 ^ --user ReplUser ^ --password <password> ^ --result-file=/tmp/binlog.txt
Amazon RDS normally purges a binary log as soon as possible, but 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 and 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 do not
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
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 a DB instance on MySQL
version 5.6 or later.
You can enable binary log annotations globally by creating a custom parameter group and
binlog_annotate_row_events parameter to
1. You can also enable annotations at the session level,
SET SESSION binlog_annotate_row_events = 1. Use the
replicate_annotate_row_events to replicate binary log annotations
to the slave 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 sames 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/*!*/;