|« PreviousNext »|
|Did this page help you? Yes | No | Tell us about it...|
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. All log files are rotated every hour; only the logs for the previous 24 hours are retained.
The MySQL error log is written to the
mysql-error.log file. You can
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.
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.
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.
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.
general_log: To create the general log, set to 1.
log_output: To write the logs to the file system, set to
FILE. To write the logs to a table, set to
long_query_time: To prevent fast-running queries from being
logged in the slow query log, specify a floating point value for the
shortest query execution time to be logged. 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 takes longer than 100 milliseconds from being logged.
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.
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 20% 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.
You can direct the general and slow query logs to a table on the DB instance and access
these files by a query. Enabling this logging increases the amount of data written
to the database, which can degrade performance. When you create a MySQL DB instance,
both the general log and the slow query log are disabled. In order to enable
logging, you create a DB parameter group and then set the
slow_query_log server parameters to
Setting the parameter
1 causes the
mysql.general_log table to start accumulating all activity on the
database. Similarly, setting the parameter
1 causes the
mysql.slow_log table to start
accumulating all slow queries. A query is defined as slow if the time it takes to
run exceeds the time specified by the
long_query_time parameter. The
default value is 10 seconds, but you can change it by modifying the DB parameter
group that is associated with the DB instance.
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> 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.
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:
--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
--result-file: Specify the local file that will receive the raw
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
For more information about mysqlbinlog options, go to mysqlbinlog - Utility for Processing Binary Log Files.
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. Specify a period in
which have time to download the logs. If you set the retention period, monitor
storage usage for the instance to verify that the instance starts has sufficient
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