使用mysqlbinlog+脚本实时备份binlog

# cat fetch_binlog.sh 
#!/bin/bash
# 从RDS获取binlog文件，并存放到本地（RDS里binlog存的比较少，大部分都上传到OSS了，不方便使用）
source /etc/profile

# 配置参数
DB_USER='dts'
DB_PASS='123456'
DB_PORT=3306
DB_HOST='rm-test-abcd.mysql.rds.aliyuncs.com'
CONNECTION_SERVER_ID=8888

# 构建mysqlbinlog命令
BINLOG_DUMP_CMD="mysqlbinlog --read-from-remote-server --host=${DB_HOST} --port=${DB_PORT} --user=${DB_USER} --password=${DB_PASS} --raw --stop-never  --connection-server-id=${CONNECTION_SERVER_ID} "

# 获取远程最早的binlog文件名
get_remote_first_binlogfile() {
    local binlogfile=$(mysql -u${DB_USER} -p${DB_PASS} --port ${DB_PORT} -h ${DB_HOST} 2>/dev/null -BNe 'show master logs;' | head -1 | awk '{print $1}')
    echo "$binlogfile"
}

# 从指定的binlog文件拉取binlog
pull_binlog_from_file() {
    local binlogfile=$1
    ${BINLOG_DUMP_CMD} ${binlogfile}
    local result=$?
    if [ $result -ne 0 ]; then
        echo "从binlog文件 ${binlogfile} 拉取binlog失败，错误码: $result"
    fi
    return $result
}

# 检查本地是否存在binlog文件
if ! ls -lhrt mysql-bin.* &> /dev/null; then
    echo "本地不存在binlog文件，从remote rds最早的binlog开始拉取"
    remote_first_binlogfile=$(get_remote_first_binlogfile)
    echo "remote 最早的binlog文件名: " $remote_first_binlogfile
    pull_binlog_from_file $remote_first_binlogfile
else
    echo "本地存在binlog文件，尝试从本地文件开始增量获取binlog"
    local_first_binlogfile=$(ls -lhrt mysql-bin.*| tail -1 | awk '{print $NF}')
    echo "本地最后一个binlog文件: " $local_first_binlogfile
    if pull_binlog_from_file $local_first_binlogfile; then
        echo "从本地文件成功增量获取binlog"
    else
        echo "本地最后一个binlog文件和远程的无法匹配到，可能发生过数据库切换，将从remote rds最早的binlog开始拉取"
        remote_first_binlogfile=$(get_remote_first_binlogfile)
        echo "remote 最早的binlog文件名: " $remote_first_binlogfile
        pull_binlog_from_file $remote_first_binlogfile
    fi
fi

cd /etc/supervisord.d
# cat test_fetch_binlog.ini 
[program:test_mysqlbinlog]
command=bash fetch_binlog.sh
directory=/mnt/rds_binlog/test_rds
autostart=true
autorestart=true
startretries=3
stderr_logfile=/tmp/mysqlbinlog.err.log
stdout_logfile=/tmp/mysqlbinlog.out.log
user=root
numprocs=1
numprocs_start=0
startsecs=5
exitcodes=0,2
stopsignal=TERM
stopwaitsecs=5
stopasgroup=false
killasgroup=false