下载binlog

先参照上节内容开启binlog

连接到MySQL，运行 show binary logs 命令，查看在数据库实例上的二进制日志列表：

mysql> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.068462 |       729 |
| mysql-bin-changelog.068463 |       578 |
| mysql-bin-changelog.068464 |       578 |
| mysql-bin-changelog.068465 |       154 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

下载binary log

访问 MySQL 二进制日志 以从 RDS 数据库实例中下载或流传输日志：

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

提前执行一些CURD语句插入数据：

CREATE DATABASE IF NOT EXISTS test;
use test;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);
-- Insert data into the table
INSERT INTO employees (name, age, department) VALUES
    ('John Doe', 30, 'Sales'),
    ('Jane Smith', 35, 'Marketing'),
    ('Mike Johnson', 25, 'IT');
delete from employees where age=30;

下载后查看binlog的内容：

image-20230626134811211

每天自动上传binlog到S3

mysql每天都会产生一些binlog，如果想要将它们定期备份到S3, 可以借助脚本来实现：

AWS_PATH=/opt/aws
Backup_dir=/home/ec2-user/backup/binlog/$(date "+%Y-%m-%d")
Bucket='rds-binlogs'
RDS='mysql57.xxxxxxxxxx.us-west-2.rds.amazonaws.com'
dbuser='admin'
export MYSQL_PWD='test1234'

mysql_binlog_filename=$(mysql -u $dbuser -h $RDS -e "show master logs"|grep "mysql-bin"|awk '{print $1}')

for file in $mysql_binlog_filename
do
        if ! test -d $Backup_dir
        then
                mkdir $Backup_dir
        fi
        #remote read binlog
        `mysqlbinlog -u $dbuser -h $RDS --read-from-remote-server $file --result-file=$Backup_dir/$file`
done

# Upload to S3 bucket
aws s3 sync $Backup_dir s3://$Bucket/binlog

# Clean binlog on disk 1 day ago
`find /home/ec2-user/backup/binlog -mtime +1 -name "mysql-bin-changelog.*" -exec rm -rf {} \;`

来源：https://github.com/awslabs/rds-support-tools/blob/main/mysql/rds-binlog-to-s3/rds-binlog-to-s3.sh

在一台EC2上设置一个crontab任务，然后每天运行上面的脚本：

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