数据库内核月报－ 2018/06 - MSSQL · 最佳实践 · RDS SDK实现数据库迁移上阿里云RDS SQL Server - 《数据库内核月报》

摘要
适用场景
实现分析
具体实现
准备工作
- 安装python
- 安装SDK依赖包
代码实现
使用方法
最后总结
参考链接

摘要

至今，我们完成了SQL Server备份还原专题系列七篇月报分享：三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、以及阿里云RDS SQL自动化迁移上云的一种解决方案，本期我们分享如何使用阿里云RDS SDK来实现用户线下数据备份文件迁移上阿里云RDS SQL Server。

适用场景

对于那些不想通过RDS控制台来实现数据库上云RDS SQL Server，具有编程能力的，可以考虑使用RDS SDK来实现线下数据库迁移上云RDS SQL实例上。

实现分析

由于用户线下数据库备份文件位于OSS中，所以迁移上云需要设计到OSS相关的SDK和RDS相关的SDK。

OSS详情

由于用户的线下数据库备份文件存放在OSS上的Bucket中，因此从OSS中获取数据库备份文件需要使用到OSS的SDK；从OSS上获取备份文件的同时，我们还需要知道OSS Bucket所在的Region，因为迁移上云要求RDS实例和OSS Bucket处在同一个Region中。从这些分析来看，我们必须要知道OSS Bucket名字，所在的Endpoint和备份文件名bak_file_key。

RDS详情

RDS实例是指用户需要迁移上云的目标RDS SQL实例，我们需要知道RDS SQL实例的版本信息（RDS SQL 2008R2和2012及以上版本输入参数稍有不同），实例所在的Region（RDS实例需要和OSS Bucket处在同一个Region）以及目标实例上数据库的名字。从分析来看RDS实例，我们需要知道RDS SQL ID和数据库名字。

输入参数

访问阿里云资源，用户需要带上阿里云账户或者子账户的AK，即access_key_id和access_key_secret，因此这两个参数也必不可少。所以，最后我们的输入参数定义为以下七个参数。

access_key_id        :   阿里云用户 access key id
access_key_secret    :   阿里云用户access key secret
rds_instance_id        :   RDS SQL实例ID
database_name        :   目标实例数据库名字
oss_endpoint        :   OSS Endpoint地址
oss_bucket             :   OSS Bucket名
bak_file_key        :   用户备份在OSS Bucket中的文件名

具体实现

实现分析完毕后，以下章节详细介绍具体的实现方法，包括：

准备工作

代码实现

使用方法

准备工作

安装python

首先请根据python官网的引导安装合适的Python版本，推荐安装2.7.10。安装完毕后，查看Python版本

Windows

C:\>c:\Python27\python.exe -V
Python 2.7.10

上面的输出表明您已经成功安装了Python 2.7.10版本。如果提示“不是内部或外部命令”，请检查配置“环境变量”-“Path”，增加Python的安装路径和pip命令的目录。如图:

Mac /Linux/Unix

$ python -V
Python 2.7.10

安装SDK依赖包

使用pip安装或者git clone源码安装，任选其一，推荐pip安装，简单方便快捷。

Pip安装

pip install aliyun-python-sdk-rds
pip install oss2

源码安装

## git 克隆openapi
git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git
## 安装阿里云 SDK 核心库
cd aliyun-python-sdk-core
python setup.py install
## 安装阿里云 RDS SDK
cd aliyun-python-sdk-rds
python setup.py install
## git 克隆OSS SDK
git clone https://github.com/aliyun/aliyun-oss-python-sdk.git
cd aliyun-oss-python-sdk
## 安装OSS2
python setup.py install

代码实现

在本文，使用python版RDS SDK实现数据库迁移上云RDS SQL Server，当然你也可以使用C#版、Java版等其他版本，详细的代码实现如下：

#!/usr/bin/python
# -*- coding: utf-8 -*-
"""***************************************************************************************
# Script name    : RdsSQLCreateMigrateTask.py
# Author        : jianming.wjm@alibaba-inc.com
# Create Date   : 2018-06-10 19:27
# Language         : Python 2.7.10
# Dev platform  : Mac OS X 10.12.6
# Purpose        : 
                    This script is for Migration user SQL Server databases localy to alibaba cloud RDS SQL Server.
                    Users' FULL backup files are located on theirselves' OSS Bucket folder already.
                    This script helps users to do migration a offline databases to alibaba cloud RDS SQL Server instance.
                    We achieve those accomplishments by call alibaba cloud RDS OPENAPI.
# Limitation    :
    RDS Edition : Support RDS edition listed below
                    '2008R2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
                       '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
                       '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha'
# Usage         :
    Help         : python RdsSQLCreateMigrateTask.py -h
    Example     : 
                    RdsSQLCreateMigrateTask.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>
                    variables description
                    access_key_id        :    alibaba cloud user access key id, fg: LTAIKeRvKPRwkaU3
                    access_key_secret    :    alibaba cloud user access key secret, fg: BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
                    rds_instance_id        :   RDS SQL instance ID, fg: rm-2zesz4564ud8s7123
                    database_name        :    The database name will be on RDS.
                    oss_endpoint        :   OSS Endpoint address, fg: oss-cn-beijing.aliyuncs.com
                    oss_bucket             :   OSS Bucket name, fg: test-oss-bucket
                    bak_file_key        :   The backup file key on OSS,fg: Migration/TestMigration_FULL_20180523225534.bak
                : calling example:
                $ python RdsSQLCreateMigrateTask.py -k LTAIKeRvKPRwkaU3 -s BbZ7xhrertQ0dfgMqfAZPByhnp4G2k -i rm-2zesz4564ud8s7123 -d testmigrate -e oss-cn-beijing.aliyuncs.com -b test-oss-bucket -f Migration/TestMigration_FULL_20180523225534.bak
# Output         : There two sesction output, one is the input variables and the other is the migration requests and response.
*********************Input variables*************************************
************************************************************************
*********************Migration requests**********************************
************************************************************************
# Modify Author : jianming.wjm@alibaba-inc.com
# Modify Date   : 2018-06-11 21:43
# Function:
#**************************************************************************************
"""
import json
import os
import sys, getopt
import re
import oss2
import time
from aliyunsdkcore.client import AcsClient
from aliyunsdkrds.request.v20140815 import DescribeMigrateTasksForSQLServerRequest
from aliyunsdkrds.request.v20140815 import CreateMigrateTaskRequest
from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest
def main(argv):
    access_key_id =  access_key_secret =  rds_instance_id =  oss_endpoint =  oss_bucket =  bak_file_key = database_name = ''
    # usage help
    try:
        opts, args = getopt.getopt(argv,"hk:s:i:d:e:b:f:",["access_key_id=", "access_key_secret=", "rds_instance_id=", "database_name=", "oss_endpoint=", "oss_bucket=", "bak_file_key="])
    except getopt.GetoptError:
        print ('%s -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>' % (sys.argv[0]))
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print ('%s -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>' % (sys.argv[0]))
            sys.exit()
        elif opt in ("-k", "-K", "--access_key_id"):
            access_key_id = arg
        elif opt in ("-s", "-S", "--access_key_secret"):
            access_key_secret = arg
        elif opt in ("-i", "-I", "--rds_instance_id"):
            rds_instance_id = arg
        elif opt in ("-d", "-D", "--database_name"):
            database_name = arg
        elif opt in ("-e", "-E", "--oss_endpoint"):
            oss_endpoint = arg
        elif opt in ("-b", "-B", "--oss_bucket"):
            oss_bucket = arg
        elif opt in ("-f", "-F", "--bak_file_key"):
            bak_file_key = arg
    # show the input parameters
       print ("\n*********************Input variables****************************************\n" \
           "access_key_id = %s\naccess_key_secret = %s\nrds_instance_id = %s\ndatabase_name = %s\n" \
           "oss_endpoint = %s\noss_bucket = %s\nbak_file_key = %s\n" \
           "***************************************************************************\n"
           % (access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key))
       ### check RDS & OSS region to make sure they are located in the same region.
       # get RDS details
       success, rds_details = rds_instnace_details(access_key_id, access_key_secret, rds_instance_id)
       if not success:
           print ("%s" % rds_details)
           sys.exit()
       rds_db_version, rds_engine, rds_region = rds_details["EngineVersion"], rds_details["Engine"], rds_details["RegionId"]
       # get OSS Bucket
       success, oss_details, sign_url = oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key)
       if not success:
           print ("%s" % oss_details)
           sys.exit()
       oss_region = oss_details.location
       # support db version checking.
       if rds_engine != 'SQLServer' \
           or rds_db_version not in [    '2008r2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
                                       '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
                                       '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha']:
           print("RDS engine doesn't support, this is only for RDS SQL Server engine.")
           sys.exit()
       # RDS & OSS Bucket are not under the same region.
       if not oss_region.endswith(rds_region):
           print("RDS & OSS Bucket are not located in the same region.")
           sys.exit()
       # everything is OK, we'd go to the real business.
       print ("--[%s] will be migrated to your RDS [%s] and the database name will be [%s]." % (bak_file_key, rds_instance_id, database_name))
       # RDS & OSS Bucket are in the same region.
       print ("\n*********************Migration response**********************************")
       do_migrate_database(access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key, rds_db_version, sign_url)
       print ("************************************************************************")
"""
action to migrate database into RDS
"""
def do_migrate_database(access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key, rds_db_version, sign_url):
    request = CreateMigrateTaskRequest.CreateMigrateTaskRequest()
    request.set_DBInstanceId(rds_instance_id)
    request.set_DBName(database_name)
    request.set_BackupMode("FULL")
    request.set_IsOnlineDB(True)
    if rds_db_version == '2008r2':
        request.set_DBName(database_name.lower())
        request.set_OSSUrls(sign_url)
    else:
        request.set_OSSUrls("")
        request.set_OssObjectPositions("%s:%s:%s" % (oss_endpoint, oss_bucket, bak_file_key)) # OSSEndpoint:OSSBucket:OSSFileKey
        request.set_CheckDBMode("SyncExecuteDBCheck")
    success, response = _send_request(access_key_id, access_key_secret, request)
    if success:
        print response
    else:
        print ("OPENAPI Response Error !!!!! : %s" % response)
"""
get RDS SQL Instance details via RDS OPENAPI.
"""
def rds_instnace_details(access_key_id, access_key_secret, rds_instance_id):
    request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
    request.set_DBInstanceId(rds_instance_id)
    success, response = _send_request(access_key_id, access_key_secret, request)
    if success:
        if response["Items"]["DBInstanceAttribute"]:
            # print response["Items"]["DBInstanceAttribute"][0]["EngineVersion"]
            # print response["Items"]["DBInstanceAttribute"][0]["RegionId"]
            return True, response["Items"]["DBInstanceAttribute"][0]
        else:
            return False, "Couldn't find specify RDS [%s]." % rds_instance_id
    return False, response
"""
send request to OPENAPI
and get the response details
"""
def _send_request(access_key_id, access_key_secret, request, region='cn-hangzhou'):
    request.set_accept_format('json')
    try:
        # clt = AcsClient(access_key_id, access_key_secret, 'cn-hangzhou')
        clt = AcsClient(access_key_id, access_key_secret, region)
        response_str = clt.do_action_with_exception(request)
        response_detail = json.loads(response_str)
        return True, response_detail
    except Exception as e:
        return False, e
"""
get OSS Bucket details via OSS OPENAPI
"""
def oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key):
    try:
        bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), oss_endpoint, oss_bucket)
        bucket_info = bucket.get_bucket_info()
        return True, bucket_info, bucket.sign_url('GET', bak_file_key, 24 * 3600)
    except Exception as e:
        return False, e, None
"""
process entrance main.
"""
if __name__ == '__main__':
    main(sys.argv[1:])

当然，以上代码，你也可以去这里下载以上python脚本。

使用方法

查看Help

你只需要使用-h来查看脚本的使用方法：

$ python ~/Downloads/RdsSQLCreateMigrateTask.py -h
~/Downloads/RdsSQLCreateMigrateTask.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>

一个例子

以下是一个具体的例子：

$ python ~/Downloads/RdsSQLCreateMigrateTask.py -k LTAIKeRvKPRwkaU3 -s BbZ7xhrertQ0dfgMqfAZPByhnp4G2k -i rm-2zesz4564ud8s7123 -d testmigrate -e oss-cn-beijing.aliyuncs.com -b test-oss-bucket -f Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak

输出结果

执行以上命令以后的结果输出，分为三个部分：

第一部分输入参数：展示所有你的输入参数，以便查询输入错误

第二部分提示信息：告诉你，哪一个备份文件会被迁移到哪个实例的哪一个数据库

第三部分调用返回：RDS OPENAPI SDK的返回信息

*********************Input variables****************************************
access_key_id = LTAIKeRvKPRwkaU3
access_key_secret = BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
rds_instance_id = rm-2zesz4564ud8s7123
database_name = testmigrate
oss_endpoint = oss-cn-beijing.aliyuncs.com
oss_bucket = test-oss-bucket
bak_file_key = Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak
***************************************************************************
--[Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] will be migrated to your RDS [rm-2zesz4564ud8s7123] and the database name will be [testmigrate].
*********************Migration response**********************************
{u'DBInstanceId': u'rm-2zesz4564ud8s7123', u'BackupMode': u'FULL', u'MigrateTaskId': u'107081', u'RequestId': u'F5419B88-7575-47BA-BDEB-64D280E36C54', u'TaskId': u'70630059', u'DBName': u'testmigrate'}
************************************************************************

最后总结

利用本篇文档，你可以很清楚的了解到使用RDS SDK OPENAPI的最佳实践的同时，又可以实现用户线下数据库迁移上阿里云RDS SQL Server的全自动实现方法。

参考链接

阿里云帮助文档中的CreateMigrateTask

阿里云RDS OPENAPI SDK，在github中的源代码，aliyun-openapi-python-sdk