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

3 minuto de leitura
0

I need to back up Amazon Relational Database Service (Amazon RDS) for 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 for MySQL DB instance to a S3 bucket that 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

Note: If you receive errors when running AWS Command Line Interface (AWS CLI) commands, make sure that you’re using the most recent AWS CLI version.

1.    Enable binlog file retention on the RDS DB instance so that you can access MySQL binary logs. This 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 Amazon RDS for 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, 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 the AWS CLI. Modify the mysqlbinlog command in the script to include any mysqlbinlog options you want to add:

$ 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

Related information

MySQL database log files