适用场景

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

实现分析

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

由于用户的线下数据库备份文件存放在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，因此这两个参数也必不可少。所以，最后我们的输入参数定义为以下七个参数。

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

准备工作

代码实现

准备工作

安装python

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

Windows

  2. Python 2.7.10

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

Mac /Linux/Unix

  1. $ python -V
  2. Python 2.7.10

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

Pip安装

源码安装

  1. ## git 克隆openapi
  2. git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git
  4. ## 安装阿里云 SDK 核心库
  5. cd aliyun-python-sdk-core
  6. python setup.py install
  8. ## 安装阿里云 RDS SDK
  9. cd aliyun-python-sdk-rds
  10. python setup.py install
  12. ## git 克隆OSS SDK
  13. git clone https://github.com/aliyun/aliyun-oss-python-sdk.git
  14. cd aliyun-oss-python-sdk
  16. ## 安装OSS2
  17. python setup.py install

代码实现

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

  1. #!/usr/bin/python
  3. # -*- coding: utf-8 -*-
  4. """***************************************************************************************
  5. # Script name    : RdsSQLCreateMigrateTask.py
  6. # Author        : jianming.wjm@alibaba-inc.com
  7. # Create Date   : 2018-06-10 19:27
  8. # Language         : Python 2.7.10
  9. # Dev platform  : Mac OS X 10.12.6
  11. # Purpose        : 
  12.                     This script is for Migration user SQL Server databases localy to alibaba cloud RDS SQL Server.
  13.                     Users' FULL backup files are located on theirselves' OSS Bucket folder already.
  14.                     This script helps users to do migration a offline databases to alibaba cloud RDS SQL Server instance.
  15.                     We achieve those accomplishments by call alibaba cloud RDS OPENAPI.
  17. # Limitation    :
  18.     RDS Edition : Support RDS edition listed below
  19.                     '2008R2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
  20.                        '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
  21.                        '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha'
  23. # Usage         :
  24.     Help         : python RdsSQLCreateMigrateTask.py -h
  25.     Example     : 
  26.                     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>
  28.                     variables description
  29.                     access_key_id        :    alibaba cloud user access key id, fg: LTAIKeRvKPRwkaU3
  30.                     access_key_secret    :    alibaba cloud user access key secret, fg: BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
  31.                     rds_instance_id        :   RDS SQL instance ID, fg: rm-2zesz4564ud8s7123
  32.                     database_name        :    The database name will be on RDS.
  33.                     oss_endpoint        :   OSS Endpoint address, fg: oss-cn-beijing.aliyuncs.com
  34.                     oss_bucket             :   OSS Bucket name, fg: test-oss-bucket
  35.                     bak_file_key        :   The backup file key on OSS,fg: Migration/TestMigration_FULL_20180523225534.bak
  37.                 : calling example:
  38.                 $ 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
  41. # Output         : There two sesction output, one is the input variables and the other is the migration requests and response.
  42. *********************Input variables*************************************
  44. ************************************************************************
  46. *********************Migration requests**********************************
  48. ************************************************************************
  51. # Modify Author : jianming.wjm@alibaba-inc.com
  52. # Modify Date   : 2018-06-11 21:43
  53. # Function:
  54. #**************************************************************************************
  56. import json
  57. import os
  58. import sys, getopt
  59. import re
  60. import oss2
  61. import time
  63. from aliyunsdkcore.client import AcsClient
  64. from aliyunsdkrds.request.v20140815 import DescribeMigrateTasksForSQLServerRequest
  66. from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest
  69. def main(argv):
  70.     access_key_id =  access_key_secret =  rds_instance_id =  oss_endpoint =  oss_bucket =  bak_file_key = database_name = ''
  72.     # usage help
  73.     try:
  74.         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="])
  75.     except getopt.GetoptError:
  76.         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]))
  77.         sys.exit(2)
  79.     for opt, arg in opts:
  80.         if opt == '-h':
  81.             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]))
  82.             sys.exit()
  83.         elif opt in ("-k", "-K", "--access_key_id"):
  84.             access_key_id = arg
  85.         elif opt in ("-s", "-S", "--access_key_secret"):
  86.             access_key_secret = arg
  87.         elif opt in ("-i", "-I", "--rds_instance_id"):
  88.             rds_instance_id = arg
  89.         elif opt in ("-d", "-D", "--database_name"):
  90.             database_name = arg
  91.         elif opt in ("-e", "-E", "--oss_endpoint"):
  92.             oss_endpoint = arg
  93.         elif opt in ("-b", "-B", "--oss_bucket"):
  94.             oss_bucket = arg
  95.         elif opt in ("-f", "-F", "--bak_file_key"):
  96.             bak_file_key = arg
  98.     # show the input parameters
  99.        print ("\n*********************Input variables****************************************\n" \
  100.            "access_key_id = %s\naccess_key_secret = %s\nrds_instance_id = %s\ndatabase_name = %s\n" \
  101.            "oss_endpoint = %s\noss_bucket = %s\nbak_file_key = %s\n" \
  102.            "***************************************************************************\n"
  103.            % (access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key))
  106.        ### check RDS & OSS region to make sure they are located in the same region.
  108.        # get RDS details
  109.        success, rds_details = rds_instnace_details(access_key_id, access_key_secret, rds_instance_id)
  110.        if not success:
  111.            print ("%s" % rds_details)
  112.            sys.exit()
  114.        rds_db_version, rds_engine, rds_region = rds_details["EngineVersion"], rds_details["Engine"], rds_details["RegionId"]
  116.        # get OSS Bucket
  117.        success, oss_details, sign_url = oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key)
  118.        if not success:
  119.            print ("%s" % oss_details)
  120.            sys.exit()
  122.        oss_region = oss_details.location
  123.        # support db version checking.
  125.        if rds_engine != 'SQLServer' \
  126.            or rds_db_version not in [    '2008r2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
  127.                                        '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
  128.                                        '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha']:
  129.            print("RDS engine doesn't support, this is only for RDS SQL Server engine.")
  130.            sys.exit()
  132.        # RDS & OSS Bucket are not under the same region.
  133.        if not oss_region.endswith(rds_region):
  134.            print("RDS & OSS Bucket are not located in the same region.")
  135.            sys.exit()
  137.        # everything is OK, we'd go to the real business.
  138.        print ("--[%s] will be migrated to your RDS [%s] and the database name will be [%s]." % (bak_file_key, rds_instance_id, database_name))
  140.        # RDS & OSS Bucket are in the same region.
  141.        print ("\n*********************Migration response**********************************")
  142.        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)
  143.        print ("************************************************************************")
  145. """
  146. """
  147. 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):
  148.     request = CreateMigrateTaskRequest.CreateMigrateTaskRequest()
  149.     request.set_DBInstanceId(rds_instance_id)
  151.     request.set_BackupMode("FULL")
  152.     request.set_IsOnlineDB(True)
  153.     if rds_db_version == '2008r2':
  154.         request.set_DBName(database_name.lower())
  155.         request.set_OSSUrls(sign_url)
  156.     else:
  157.         request.set_OSSUrls("")
  158.         request.set_OssObjectPositions("%s:%s:%s" % (oss_endpoint, oss_bucket, bak_file_key)) # OSSEndpoint:OSSBucket:OSSFileKey
  159.         request.set_CheckDBMode("SyncExecuteDBCheck")
  161.     success, response = _send_request(access_key_id, access_key_secret, request)
  163.     if success:
  164.         print response
  165.     else:
  166.         print ("OPENAPI Response Error !!!!! : %s" % response)
  169. """
  170. get RDS SQL Instance details via RDS OPENAPI.
  171. """
  172. def rds_instnace_details(access_key_id, access_key_secret, rds_instance_id):
  173.     request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
  174.     request.set_DBInstanceId(rds_instance_id)
  175.     success, response = _send_request(access_key_id, access_key_secret, request)
  177.     if success:
  178.         if response["Items"]["DBInstanceAttribute"]:
  179.             # print response["Items"]["DBInstanceAttribute"][0]["EngineVersion"]
  180.             # print response["Items"]["DBInstanceAttribute"][0]["RegionId"]
  181.             return True, response["Items"]["DBInstanceAttribute"][0]
  182.         else:
  183.             return False, "Couldn't find specify RDS [%s]." % rds_instance_id
  186.     return False, response
  189. """
  190. send request to OPENAPI
  191. and get the response details
  192. """
  193. def _send_request(access_key_id, access_key_secret, request, region='cn-hangzhou'):
  194.     request.set_accept_format('json')
  195.     try:
  196.         # clt = AcsClient(access_key_id, access_key_secret, 'cn-hangzhou')
  197.         clt = AcsClient(access_key_id, access_key_secret, region)
  198.         response_str = clt.do_action_with_exception(request)
  199.         response_detail = json.loads(response_str)
  200.         return True, response_detail
  201.     except Exception as e:
  202.         return False, e
  205. """
  206. get OSS Bucket details via OSS OPENAPI
  207. """
  208. def oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key):
  209.     try:
  210.         bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), oss_endpoint, oss_bucket)
  211.         bucket_info = bucket.get_bucket_info()
  212.         return True, bucket_info, bucket.sign_url('GET', bak_file_key, 24 * 3600)
  213.     except Exception as e:
  214.         return False, e, None
  217. """
  218. process entrance main.
  219. """
  220. if __name__ == '__main__':
  221.     main(sys.argv[1:])

查看Help

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

以下是一个具体的例子：

  1. $ 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的返回信息

  1. *********************Input variables****************************************
  2. access_key_id = LTAIKeRvKPRwkaU3
  3. access_key_secret = BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
  4. rds_instance_id = rm-2zesz4564ud8s7123
  5. database_name = testmigrate
  6. oss_endpoint = oss-cn-beijing.aliyuncs.com
  7. oss_bucket = test-oss-bucket
  8. bak_file_key = Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak
  9. ***************************************************************************
  11. --[Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] will be migrated to your RDS [rm-2zesz4564ud8s7123] and the database name will be [testmigrate].
  13. *********************Migration response**********************************

最后总结

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

参考链接

阿里云帮助文档中的CreateMigrateTask