摘要

至今,我们完成了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,因此这两个参数也必不可少。所以,最后我们的输入参数定义为以下七个参数。

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

具体实现

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

准备工作

代码实现

使用方法

准备工作

安装python

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

Windows

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

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

Mac /Linux/Unix

  1. $ python -V
  2. Python 2.7.10

安装SDK依赖包

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

Pip安装

  1. pip install aliyun-python-sdk-rds
  2. pip install oss2

源码安装

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

代码实现

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

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

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

使用方法

查看Help

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

  1. $ python ~/Downloads/RdsSQLCreateMigrateTask.py -h
  2. ~/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>

一个例子

以下是一个具体的例子:

  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. ***************************************************************************
  10. --[Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] will be migrated to your RDS [rm-2zesz4564ud8s7123] and the database name will be [testmigrate].
  11. *********************Migration response**********************************
  12. {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'}
  13. ************************************************************************

最后总结

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

参考链接

阿里云帮助文档中的CreateMigrateTask

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