電子發燒友App
摘要：?摘要 到目前，我們完成了SQL Server備份還原專題系列八篇月報分享：三種常見的數據庫備份、備份策略的制定、查找備份鏈、數據庫的三種恢復模式與備份之間的關系、利用文件組實現冷熱數據隔離備份方案、如何監控備份還原進度、阿里云RDS SQL自動化遷移上云的一種解決方案以及上個月分享的RDS SDK實現數據庫遷移上阿里云，本期我們分享如何將用戶線下或者ECS上自建實例級別數據庫一鍵遷移上阿里云RDS SQL Server。
摘要
到目前，我們完成了SQL Server備份還原專題系列八篇月報分享：三種常見的數據庫備份、備份策略的制定、查找備份鏈、數據庫的三種恢復模式與備份之間的關系、利用文件組實現冷熱數據隔離備份方案、如何監控備份還原進度、阿里云RDS SQL自動化遷移上云的一種解決方案以及上個月分享的RDS SDK實現數據庫遷移上阿里云，本期我們分享如何將用戶線下或者ECS上自建實例級別數據庫一鍵遷移上阿里云RDS SQL Server。
適用場景
在我們上一個月分享的RDS SDK實現數據庫遷移上阿里云RDS SQL Server方案中，我們實現了如何將用戶線下或者ECS上自建的SQL Server實例中的一個用戶數據庫自動化遷移上云到RDS SQL Server，話句話說，它實現的是數據庫級別的遷移上云方案，即每次遷移上云用戶線下一個數據庫。
但是，有的用戶可能會遇到這樣的場景，我的線下有幾十上百SQL Server實例，每個實例又有幾十上百個數據庫，總共就有成千上萬個數據庫遷移上云。如果是數據庫級別的遷移上云方案顯得力不從心，效率低下。為了解決用戶大批量數據庫遷移上云RDS for SQL Server，簡化上云操作步驟，提高上云效率，實例級別數據庫上云RDS SQL Server是我們迫切需要解決場景。
實現分析
由于在前一個月分享的RDS SDK實現數據庫遷移上阿里云RDS SQL Server中，我們已經實現了單個數據庫遷移上云方法，因此實現實例級別的遷移上云我們可以采用如下方案：
?將用戶線下實例上所有的數據庫全量備份文件上傳到OSS的一個文件夾中
?遍歷OSS上該文件夾所有的數據庫備份文件
?每一個備份文件生成一個遷移上云任務
輸入參數
基于以上的分析，我們的實現方法需要包含如下六個輸入參數，以及這六個輸入參數的解析參見下表：
access_key_id :???阿里云用戶?access?key?id access_key_secret :???阿里云用戶access?key?secret rds_instance_id :???RDS?SQL實例ID oss_endpoint :???OSS?Endpoint地址 oss_bucket? :???OSS?Bucket名 directory ????:???用戶數據庫備份文件在OSS上文件夾路徑，如果是根目錄，請傳入“/”
具體實現
準備工作
參見上一個月的月報分享MSSQL · 最佳實踐 · RDS SDK實現數據庫遷移上阿里云RDS SQL Server中的準備工作部分。
代碼實現
在本文，我們使用python版RDS SDK實現數據庫遷移上云RDS SQL Server，當然你也可以使用C#版、Java版等其他版本，詳細的代碼實現如下：
#!/usr/bin/python#?-*-?coding:?utf-8?-*-"""*************************************************************************************** #?Script?name :?RDSSQLCreateMigrateTasksBatchly.py #?Author :?jianming.wjm@alibaba-inc.com #?Create?Date???:?2018-05-17?19:27 #?Language? :?Python?2.7.10 #?Run?platform??:?Mac?OS?X?10.12.6 #?Purpose :? This?script?is?for?batchly?Migration?user?offline?SQL?Server?databases?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?all?offline?databases?backed-up?under?the?OSS?Bucket?folder?to?RDS?SQL. We?achieve?those?accomplishments?by?call?alibaba?cloud?RDS?OPENAPI. #?Limitation : RDS?Edition?:?Support?RDS?edition?listed?below '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' #?Preparation : ??1.?python?2.7.x?installing?(I'm?using?2.7.10) ??2.?pip?install?aliyun-python-sdk-rds ??3.?pip?install?oss2 #?Usage? : Help? :?python?RDSSQLCreateMigrateTasksBatchly.py?-h Example? :? python?~/Downloads/RDSSQLCreateMigrateTasksBatchly.py?-k??-s? ?-i? ?-e? ?-b? ?-d? 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 oss_endpoint :???OSS?Endpoint?address,?fg:?oss-cn-beijing.aliyuncs.com oss_bucket? :???OSS?Bucket?name,?fg:?atp-test-on-ecs directory? :???Sub?folder?name?under?OSS?Bucket,?fg:?Migration/OPENAPIDemo #?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-05-19?21:43 #?Function: #************************************************************************************** """import?jsonimport?osimport?sys,?getoptimport?reimport?oss2import?timefrom?aliyunsdkcore.client?import?AcsClientfrom?aliyunsdkrds.request.v20140815?import?DescribeMigrateTasksForSQLServerRequestfrom?aliyunsdkrds.request.v20140815?import?CreateMigrateTaskRequestfrom?aliyunsdkrds.request.v20140815?import?DescribeDBInstanceAttributeRequestdef?main(argv): access_key_id?=??access_key_secret?=??rds_instance_id?=??oss_endpoint?=??oss_bucket?=??directory?=?'' #?usage?help try: opts,?args?=?getopt.getopt(argv,"hk:s:i:e:b:d:",["access_key_id=",?"access_key_secret=",?"rds_instance_id=",?"oss_endpoint=",?"oss_bucket=",?"directory="]) except?getopt.GetoptError: print?('%s?-k? ?-s? ?-i? ?-e? ?-b? ?-d? '?%?(sys.argv[0])) sys.exit(2) for?opt,?arg?in?opts: if?opt?==?'-h': print?('%s?-k? ?-s? ?-i? ?-e? ?-b? ?-d? '?%?(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?("-e",?"-E",?"--oss_endpoint"): oss_endpoint?=?arg elif?opt?in?("-b",?"-B",?"--oss_bucket"): oss_bucket?=?arg elif?opt?in?("-d",?"-D",?"--directory"): if?arg.endswith("/"): directory?=?arg else: directory?=?str("%s/"?%?arg) #?show?the?input?parameters ??? print?("\n*********************Input?variables*************************************\n"?\??? "access_key_id?=?%s\naccess_key_secret?=?%s\nrds_instance_id?=?%s\noss_endpoint?=?%s\noss_bucket?=?%s\ndirectory?=?%s\n"?\??? "************************************************************************" ??? %?(access_key_id,?access_key_secret,?rds_instance_id,?oss_endpoint,?oss_bucket,?directory))??? #?check?RDS?&?OSS?region?to?make?sure?they?are?located?in?the?same?region. ??? 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"] ??? success,?oss_details?=?oss_bucket_details(access_key_id,?access_key_secret,?oss_endpoint,?oss_bucket)??? 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()??? #?RDS?&?OSS?Bucket?are?in?the?same?region. ??? print?("\n*********************Migration?requests**********************************") ??? full_migrate(access_key_id,?access_key_secret,?rds_instance_id,?oss_endpoint,?oss_bucket,?directory,?rds_db_version)??? print?("************************************************************************")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
def?full_migrate(access_key_id,?access_key_secret,?rds_instance_id,?oss_endpoint,?oss_bucket,?directory,?rds_db_version):
"""
this?supoort?full?backup?files?migration.
"""
#?get?all?backup?objects?under?sub_folder
key_parts_list,?do?=?oss_list_objects(access_key_id,?access_key_secret,?oss_endpoint,?oss_bucket,?directory),?0
#?foreach?object
for?key_parts?in?key_parts_list: print?("\n--%s.?[%s]?migrate?to?your?RDS:?[%s]?and?the?database?name?will?be:?[%s]."?%?(do,?key_parts.file_key,?rds_instance_id,?key_parts.db_name))
do?+=?1
#?print?("%s"?%?key_parts.sign_url)
request?=?CreateMigrateTaskRequest.CreateMigrateTaskRequest()
request.set_DBInstanceId(rds_instance_id)
request.set_DBName(key_parts.db_name)
request.set_BackupMode("FULL")
request.set_IsOnlineDB(True) if?rds_db_version?==?'2008r2':
request.set_DBName(key_parts.db_name.lower())
request.set_OSSUrls(key_parts.sign_url) else:
request.set_OSSUrls("")
request.set_OssObjectPositions("%s:%s:%s"?%?(oss_endpoint,?oss_bucket,?key_parts.file_key))?#?OSSEndpoint:OSSBucket:OSSFileKey
request.set_CheckDBMode("SyncExecuteDBCheck")
success,?response?=?_send_request(access_key_id,?access_key_secret,?request) if?success: print?response print?("--I'm?sleeping?for?2?seconds....")
time.sleep(2) else: print?("OPENAPI?Response?Error?!!!!!?:?%s"?%?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,?eclass?oss_key_parts(object):
????"""
????if?the?whole?object?file?key?looks?like?blow:
????Migration/OPENAPIDemo/TestMigration_FULL_20180518153544.bak
then
:?param?str?file_key:?OSS?object?file?key.
:?param?str?sub_folder:?OSS?sub?folder?name,?such?as?Migration/OPENAPIDemo
:?param?str?file_name:?OSS?object?file?name,?such?as?TestMigration_FULL_20180518153544.bak
????:?param?str?db_name:?database?name,?such?as?'TestMigration'
????:?param?str?bak_type:?backup?type?,?such?as?'FULL'
????:?param?str?date:?backup?date?time,?such?as?'20180518153544'
????:?param?str?ext:?backup?file?extendsion,?such?as?'bak'
????"""
????def?__init__(self):
???? self.file_key?=?''
???? self.sub_folder?=?''
???? self.file_name?=?''
????????self.db_name?=?''
????????self.bak_type?=?''
????????self.date?=?''
????????self.ext?=?''
????????self.sign_url?=?''"""
parse?the?OSS?file?key?string?into?oss?key?parts
and?return?oss_key_parts?object.
"""def?oss_key_parse(file_key):
????key_parts?=?oss_key_parts()????try:????????if?file_key.find('/')?>=?0:
????????????file_key_parts?=?file_key.rsplit('/',?1)????????else:
????????????file_key_parts?=?file_key
????????????file_key_parts?=?['/',?file_key]
????????key_parts.file_key?=?file_key
????????key_parts.sub_folder?=?file_key_parts[0]
????????key_parts.file_name?=?file_key_parts[1]
????????key_list?=?file_key_parts[1].rsplit('_',?2)
????????key_parts.db_name,?\
????????key_parts.bak_type,?\
????????key_parts.date,?\
????????key_parts.ext?=?key_list[0],?\
????????????????????????key_list[1],?\
????????????????????????key_list[2].rsplit('.',?1)[0],?\
????????????????????????key_list[2].rsplit('.',?1)[1]????except?Exception,?e:????????pass
????return?key_partsdef?oss_list_objects(access_key_id,?access_key_secret,?oss_endpoint,?oss_bucket,?directory):
"""
list?all?OSS?objects?under?the?specified?sub?folder
and?return?the?objects?list.
"""
bucket?=?oss2.Bucket(oss2.Auth(access_key_id,?access_key_secret),?oss_endpoint,?oss_bucket)
key_parts_list?=?[] #?OSS?Bucket?Root
if?directory?==?'/': for?object_info?in?oss2.ObjectIterator(bucket,?delimiter='/'): if?not?object_info.is_prefix():
key_part?=?oss_key_parse(object_info.key) #?get?object?sign_url
key_part.sign_url?=?bucket.sign_url('GET',?object_info.key,?24?*?3600) if?key_part.ext?in['bak',?'trn',?'log',?'diff']:
key_parts_list.append(key_part) else: print?("Warning!!!!!,?[%s]?is?not?backup?file,?filtered."?%?(key_part.file_key)) else: for?i,?object_info?in?enumerate(oss2.ObjectIterator(bucket,?prefix=directory)): #?have?to?the?backup?files,?not?folder
if?not?object_info.is_prefix(): if?object_info.key.startswith(directory)?and?object_info.key?!=?directory: #?print?("%s"?%?(object_info.key))
key_part?=?oss_key_parse(object_info.key)
#?get?object?sign_url
key_part.sign_url?=?bucket.sign_url('GET',?object_info.key,?24?*?3600) if?key_part.ext?in['bak',?'trn',?'log',?'diff']:
key_parts_list.append(key_part) else: print?("Warning!!!!!,?[%s]?is?not?a?vaild?backup?file,?filtered."?%?(key_part.file_key)) if?not?key_parts_list:
print("There?is?no?backup?file?on?OSS?Bucket?[%s]?under?[%s]?folder,?check?please."?%?(oss_bucket,?directory)) return?key_parts_listdef?oss_bucket_details(access_key_id,?access_key_secret,?oss_endpoint,?oss_bucket):
try:
bucket?=?oss2.Bucket(oss2.Auth(access_key_id,?access_key_secret),?oss_endpoint,?oss_bucket)
bucket_info?=?bucket.get_bucket_info() #?print?("bucket?name:%s,?region:?%s"?%?(bucket_info.name,?bucket_info.location))
return?True,?bucket_info except?Exception?as?e: return?False,?eif?__name__?==?'__main__':
????main(sys.argv[1:])當然，以上代碼，你也可以去下載以上python腳本。
使用方法
我們從以下三個方面簡要介紹下如何使用實例級別一鍵遷移上云：
?查看Help
?一個例子
?輸出結果
查看Help
你只需要使用-h來查看腳本的使用方法：
python?~/Downloads/RDSSQLCreateMigrateTasksBatchly.py?-h ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py?-k??-s? ?-i? ?-e? ?-b? ?-d?
一個例子
以下是一個具體的例子：
python?~/Downloads/RDSSQLCreateMigrateTasksBatchly.py?-k?LTAIQazXKPRwwErT?-s?BMkIUhroubQOLpOMqfA09IKlqp4G2k?-i?rm-2zesz5774ud8s71i5?-e?oss-cn-beijing.aliyuncs.com?-b?atp-test-on-ecs?-d?Migration/OPENAPIDemo
輸出結果
執行以上命令以后的結果輸出，分為兩個部分：
?第一部分輸入參數：展示所有你的輸入參數，以便查詢輸入錯誤
?第二部分提示信息：告訴你，哪一個備份文件會被遷移到哪個實例的哪一個數據庫 如下的一個實例的輸出信息：
*********************Input?variables*************************************
access_key_id?=?LTAIQazXKPRwwErT
access_key_secret?=?BMkIUhroubQOLpOMqfA09IKlqp4G2k
rds_instance_id?=?rm-2zesz5774ud8s71i5
oss_endpoint?=?oss-cn-beijing.aliyuncs.com
oss_bucket?=?atp-test-on-ecs
directory?=?Migration/OPENAPIDemo/*********************************************************************************************Migration?requests**********************************--0.?[Migration/OPENAPIDemo/TestCollation_FULL_20180523225534.bak]?migrate?to?your?RDS:?[rm-2zesz5774ud8s71i5]?and?the?database?name?will?be:?[TestCollation].
{u'DBInstanceId':?u'rm-2zesz5774ud8s71i5',?u'BackupMode':?u'FULL',?u'MigrateTaskId':?u'106121',?u'RequestId':?u'67E0DD7F-7219-4F67-AAE7-B27273921303',?u'TaskId':?u'68244691',?u'DBName':?u'TestCollation'}
--I'm?sleeping?for?2?seconds....
--1.?[Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak]?migrate?to?your?RDS:?[rm-2zesz5774ud8s71i5]?and?the?database?name?will?be:?[TestMigration].
{u'DBInstanceId':?u'rm-2zesz5774ud8s71i5',?u'BackupMode':?u'FULL',?u'MigrateTaskId':?u'106122',?u'RequestId':?u'0916CD14-861B-4BF7-A68A-409E3996B0D3',?u'TaskId':?u'68244695',?u'DBName':?u'TestMigration'}
--I'm?sleeping?for?2?seconds....
--2.?[Migration/OPENAPIDemo/testdb_FULL_20180523225534.bak]?migrate?to?your?RDS:?[rm-2zesz5774ud8s71i5]?and?the?database?name?will?be:?[testdb].
{u'DBInstanceId':?u'rm-2zesz5774ud8s71i5',?u'BackupMode':?u'FULL',?u'MigrateTaskId':?u'106123',?u'RequestId':?u'5835B154-2EE3-4059-BFC4-6F798CDCE546',?u'TaskId':?u'68244699',?u'DBName':?u'testdb'}
--I'm?sleeping?for?2?seconds....************************************************************************最后總結
利用本篇文章，我們可以輕松實現用戶線下或者ECS自建的SQL Server實例級別數據庫一鍵遷移上云，以此來極大的提高遷移上云效率，簡化操作，大大提升了用戶遷移上云體驗。
本文為云棲社區原創內容，未經允許不得轉載。
工商網監
評論