rds binlog 留存機制

Apr 16, 2021 · 1 min read · cloud aws databases  ·
Share on:

AWS RDS 備份 snapshot/day 間隔過長, 研究抓取 RDS binlog 並留存在 EC2 上

參照這篇範例 如何計劃 Amazon RDS MySQL 數據庫實例二進制日誌文件向 Amazon S3 的上傳? 做調整.

Install mysql 5.7

1apt update
2apt-cache policy mysql-server
3apt install -y mysql-server

登入 mysql, 延長 rds binlog retention hours, 保留時間為 2h

1show master status;
2show BINARY LOGS;
3show binlog events;
4
5call mysql.rds_show_configuration;
6call mysql.rds_set_configuration('binlog retention hours', 2);

建立 EC2 主機, 並作初始化

建立資料夾

1mkdir -p /opt/mysql-binlog-backup/binlog/

建立 shell secipt, 注意編輯參數

1touch /opt/mysql-binlog-backup/rds-binlog-to-s3.sh
2chmod +x /opt/mysql-binlog-backup/rds-binlog-to-s3.sh
3vim /opt/mysql-binlog-backup/rds-binlog-to-s3.sh

貼上 rds-binlog-to-s3.sh 內容

 1#!/bin/bash
 2#Script to download RDS MySQL binlog files using mysqlbinlog command and the AWS CLI tools to upload them to S3.
 3#Install AWS CLI tools see: "http://docs.aws.amazon.com/cli/latest/userguide/installing.html"
 4#Config your AWS config File (aws configure)
 5AWS_PATH=/opt/aws
 6Binlog_dir=/opt/mysql-binlog-backup/binlog
 7Backup_dir=$Binlog_dir/$(date "+%Y-%m-%d")
 8Bucket='rds-binlogs'
 9RDS='host.rds.amazonaws.com'
10master='admin'
11export MYSQL_PWD='password'
12
13mysql_binlog_filename=$(mysql -u $master -h $RDS -e "show master logs"|grep "mysql-bin"|awk '{print $1}')
14
15for file in $mysql_binlog_filename
16do
17    if ! test -d $Backup_dir
18    then
19        mkdir -p $Backup_dir
20    fi
21    #remote read binlog
22    `mysqlbinlog -u $master -h $RDS --read-from-remote-server $file --result-file=$Backup_dir/ --raw`
23done
24
25# Upload to S3 bucket
26#aws s3 sync $Backup_dir s3://$Bucket/binlog
27
28# Clean binlog on disk 7 day ago
29`find $Binlog_dir -mtime +7 -name "*" -exec rm -rf {} \;`

設立排程, 下面為每 15 分鐘執行一次的 cron

1echo '*/15 * * * * root bash /opt/mysql-binlog-backup/rds-binlog-to-s3.sh' >> /etc/crontab
comments powered by Disqus