Amazon Relational Database Service
User Guide (API Version 2014-10-31)
Did this page help you?  Yes | No |  Tell us about it...
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.

MySQL Database Log Files

You can monitor the MySQL error log, slow query log, and the general log directly through the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs. You can use the mysqlbinlog utility to download or stream a binary log. The MySQL 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 MySQL log files, the intervals for each type are given below.

Binary Logging Format

MySQL on Amazon RDS supports both the row-based and mixed binary logging formats for MySQL version 5.6. The default binary logging format is mixed. For DB instances running MySQL versions 5.1 and 5.5, only mixed binary logging is supported. For details on the different MySQL binary log formats, see Binary Logging Formats in the MySQL Reference Manual.

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 and can increase the amount of time to perform a restore operation of a DB instance.

To set the MySQL binary logging format:

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Click Parameter Groups in the left pane.

  3. For the default.mysql5.6 DB parameter group, click the Go to Details Page icon.

  4. Click the Edit Parameters button to modify the parameters in the DB parameter group.

  5. Set the binlog_format parameter to the binary logging format of your choice (MIXED or ROW).

  6. Click the Save Changes button to save the updates to the DB parameter group.

Important

Changing the default.mysql5.6 DB parameter group affects all MySQL version 5.6 DB instances that use that parameter group. If you want to specify different binary logging formats for different MySQL 5.6 DB instances in a region, you will need to create your own DB parameter group that identifies the different logging format and assign that DB parameter group to the intended DB instances.

For more information on DB parameter groups, see Working with DB Parameter Groups.

Log File Size

For MySQL version 5.6.20 and later, there is a size limit on BLOBs written to the redo log. To account for this limit, ensure that the innodb_log_file_size parameter for your MySQL DB instance is 10 times larger than the largest BLOB data size found in your tables, plus the length of other variable length fields (VARCHAR, VARBINARY, TEXT) in the same tables. For information on how to set parameter values, see Working with DB Parameter Groups. For information on the redo log BLOB size limit, go to Changes in MySQL 5.6.20.

Accessing MySQL Error Logs

The MySQL error log is written to the mysql-error.log file. You can view mysql-error.log by using the Amazon RDS console or by retrieving the log using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs. mysql-error.log 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.

MySQL 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 would result in a log entry.

Accessing the MySQL Slow Query and General Logs

The MySQL 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 MySQL 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.

  • 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.

      If either of the following conditions is met, Amazon RDS automatically rotates the slow and general logs in order to reduce the possibility of a large log either blocking database use or affecting performance:

      • Disk space usage is greater than 90% of the allocated space, and a single log uses either more than 10% of the allocated storage or more than 5 GB.

      • A single log uses more than 2% of the allocated disk space or more than 10 GB, regardless of total disk usage.

      In either situation, Amazon RDS records the log rotation in an Amazon RDS event and sends you a notification. Note that in the future Amazon RDS could change the logic about when an automatic log rotation occurs.

    • FILE– Write general and slow query logs to the file system. Log files are rotated hourly. Log files generated during the previous 24 hours are retained.

    • NONE– Disable logging.

  • 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_time value are logged. For example, setting long_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 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 long_query_time parameter.

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 MySQL 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 MySQL documentation:

Accessing File-Based MySQL Logs

You can access the file-based MySQL logs, such as the general or slow query logs, using the Amazon RDS features for working with logs:

Managing Table-Based MySQL 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 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. In order to enable logging to tables, you must also set the general_log and slow_query_log server parameters to 1.

Log tables will 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>:

            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.

Accessing MySQL 5.6 Binary Logs

You can use the mysqlbinlog utility to download or stream binary logs from Amazon RDS instances running MySQL 5.6. The binary log is downloaded to your local computer, where you can perform actions such as replaying the log using the mysql utility. For more information about using the mysqlbinlog utility, go to Using mysqlbinlog to Back Up Binary Log Files.

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 MySQL user that has been granted the replication slave permission.

  • --password: Specify the password for the user, or omit a password value so the utility will prompt you for a password.

  • To have the file downloaded in binary format, specify the --raw option.

  • --result-file: Specify the local file that will receive the raw 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.

  • To stream the binary log files, specify the --stop-never option.

For more information about mysqlbinlog options, go to mysqlbinlog - Utility for Processing Binary Log Files.

For example:

mysqlbinlog --read-from-remote-server --host=MySQL56Instance1.cg034hpkmmjt.region.rds.amazonaws.com --port=3306  --user ReplUser --password --raw --result-file=/tmp/ binlog.00098

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.

This 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;