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

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

背景

前端时间街道一个临时需求，要求根据每日的数据快照，统计计算出需要数据结果，并写入数据库，提供查询接口。

遇到两个自己没有尝试过的点：

阿里云导出的数据库是.frm和.ibd结构的数据，需要恢复的自己的服务器

需要自动化的定时拉取阿里云每日生成的镜像备份

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

解决

阿里云提供API和SDK

选择Python开发

Python安装aliyunsdkrds和aliyunsdkcore

代码

获取每日备份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 -s -r -i '

'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 -s -r -i '

'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