脚本自动下载阿里云每日备份数据库镜像

脚本自动下载阿里云每日备份数据库镜像

背景

前端时间街道一个临时需求，要求根据每日的数据快照，统计计算出需要数据结果，并写入数据库，提供查询接口。
遇到两个自己没有尝试过的点：

1. 阿里云导出的数据库是.frm和.ibd结构的数据，需要恢复的自己的服务器
2. 需要自动化的定时拉取阿里云每日生成的镜像备份

今天的主要说的是，怎么使用自动脚本拉取阿里云备份的数据库快照

解决

1. 阿里云提供API和SDK
2. 选择Python开发
3. Python安装aliyunsdkrds和aliyunsdkcore

参考链接
python SDK
API

代码

获取每日备份URL的具体实现代码，可以根据需求自己修改

#!/usr/bin/env python
# coding=utf-8
from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkrds.request.v20140815.DescribeBackupsRequest import DescribeBackupsRequest
import json
import sys
import getopt


def parser_args(argv):
    access_key = ''
    access_secret = ''
    region = ''
    db_instance_id = ''
    try:
        opts, args = getopt.getopt(argv, "hk:s:r:i:", ["access_key=", "access_secret=", "region=", "db_instance_id="])
    except getopt.GetoptError:
        print(
            'Get opt Error, '
            'usage: mysql_db_back_url.py -k <access_key> -s <access_secret> -r <region> -i <db_instance_id> '
            'ex: mysql_db_back_url.py -k 12312 -s 12312 -r cn-shanghai -i rm-uf6d4vg47vtr432z9')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print(
                'Get opt Error, '
                'usage: mysql_db_back_url.py -k <access_key> -s <access_secret> -r <region> -i <db_instance_id>'
                'ex: mysql_db_back_url.py -k 12312 -s 12312 -r cn-shanghai -i rm-uf6d4vg47vtr432z9')
            sys.exit()
        elif opt in ("-k", "--access_key"):
            access_key = arg
        elif opt in ("-s", "--access_secret"):
            access_secret = arg
        elif opt in ("-r", "--region"):
            region = arg
        elif opt in ("-i", "--db_instance_id"):
            db_instance_id = arg

    return access_key, access_secret, region, db_instance_id


def main():
    access_key, access_secret, region, db_instance_id = parser_args(sys.argv[1:])

    client = AcsClient(access_key, access_secret, region)
    request = DescribeBackupsRequest()
    request.set_accept_format('json')
    request.set_DBInstanceId(db_instance_id)  # RDS数据库实例名

    try:
        response = client.do_action_with_exception(request)
        response = str(response, encoding='utf-8')
        response = json.loads(response)
        url = response["Items"]["Backup"][0]["BackupDownloadURL"]
        return url
    except ServerException as e:
        print(e)
    except ClientException as e:
        print(e)


if __name__ == '__main__':
    print(main())

定时任务

shell 脚本 back.sh

cur_date="`date  -d "1 day ago" +%Y%m%d`"
url="`python3 mysql_db_back_url.py -k your_access_key -s your_secret_id -r cn-shanghai -i aliyun_db_id`"
wget "$url" -O data$cur_date.tar.gz
tar -izxvf data$cur_date.tar.gz -C ~/mysql/data

crontab定时任务，每天凌晨1分开始拉取镜像

1 0 * * * ~/mysql/back.sh > ~/mysql/back.log 2>&1