Accessing MySQL binary logs - Amazon Relational Database Service (original) (raw)

You can use the mysqlbinlog utility to download or stream binary logs from RDS for MySQL DB instances. 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, see Using mysqlbinlog to back up binary log files in the MySQL documentation.

To run the mysqlbinlog utility against an Amazon RDS instance, use the following options:

--read-from-remote-server – Required.
--host – The DNS name from the endpoint of the instance.
--port – The port used by the instance.
--user – A MySQL user that has been granted the REPLICATION SLAVE permission.
--password – The password for the MySQL user, or omit a password value so that the utility prompts you for a password.
--raw – Download the file in binary format.
--result-file – The local file to receive the raw output.
--stop-never – Stream the binary log files.
--verbose – When you use the ROW binlog format, include this option to see the row events as pseudo-SQL statements. For more information on the --verbose option, see mysqlbinlog row event display in the MySQL documentation.
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, see mysqlbinlog — Utility for processing binary log files in the MySQL documentation.

The following examples show how to use the mysqlbinlog utility.

For Linux, macOS, or Unix:

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

For Windows:

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

Binary logs must remain available on the DB instance for the mysqlbinlog utility to access them. To ensure their availability, use the mysql.rds_set_configuration stored procedure and specify a period with enough time for you to download the logs. If this configuration isn't set, Amazon RDS purges the binary logs as soon as possible, leading to gaps in the binary logs that the mysqlbinlog utility retrieves.

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;