How can I schedule Amazon RDS MySQL DB instance binlog files to be uploaded to Amazon S3?

Last updated: 2020-06-09

I need to back up Amazon Relational Database Service (Amazon RDS) MySQL DB instance binlog files to Amazon Simple Storage Service (Amazon S3). How can I schedule downloading the files and syncing them to an S3 bucket?

Short Description

By default, you can't copy binlog files from an Amazon RDS MySQL DB instance to a S3 bucket you choose. Instead, download the binlog files to an Amazon Elastic Compute Cloud (Amazon EC2) instance. Then, sync the files to an S3 bucket by using a cron job.

Resolution

1.    Enable binlog file retention on the RDS DB instance so that you can access MySQL binary logs. The following example sets the retention period to 24 hours:

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);

2.    Run the following command on your RDS MySQL DB instance. Confirm that the binlog retention hours parameter is correctly set to 24 (or 1 day):

mysql> call mysql.rds_show_configuration;

Expected output:

+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                               |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| binlog retention hours | 24    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted.      |
| source delay           | 0     | source delay specifies replication delay in seconds between current instance and its master.              |
| target delay           | 0     | target delay specifies replication delay in seconds between current instance and its future read-replica. |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+

3.    Install MySQL on the EC2 instance for the mysql and mysqlbinlog commands:

$ sudo yum update
$ sudo yum install mysql57

4.    Confirm that you installed the utilities correctly by running the following command:

$ rpm -qa|grep mysql

Expected output:

mysql57-server-5.7.21-2.6.amzn1.x86_64
mysql57-common-5.7.21-2.6.amzn1.x86_64
mysql57-errmsg-5.7.21-2.6.amzn1.x86_64
mysql57-5.7.21-2.6.amzn1.x86_64

5.    Create an S3 bucket and an AWS Identity and Access Management (IAM) user account.

6.    Grant the IAM user permissions to access the S3 bucket.

7.    Install the AWS Command Line Interface (AWS CLI), and then configure the AWS CLI, if you haven't already.

8.    Download the rds-binlog-to-s3 script from the GitHub website. This script downloads binlog files, and then uploads the files to an S3 bucket.

9.    Modify the script permissions:

$ chmod 744 rds-binlog-to-s3.sh

10.    Run the rds-binlog-to-s3 script on the EC2 instance using AWS CLI:

$ rds-binlog-to-s3.sh

11.    Set a cron job to sync the binlog files to the S3 bucket:

$ crontab -e
0 0  * * * /home/ec2-user/scheduler/RDS-binlog-to-s3.sh

Did this article help you?

Anything we could improve?


Need more help?