記一次MySQL資料遷移到SQLServer全過程

久曲健發表於2022-04-24
MySqlServer

為什麼要做遷移？

由於系統版本、資料庫的升級，導致測試流程阻塞，為了保證資料及系統版本的一致性，我又迫切需要想用這套環境做效能測試，所以和領導、開發請示，得到批准後，便有了這次學習的機會，所以特此來記錄下整個過程。

使用方案：

藉助工具與編碼相結合形式，備份MySQL資料庫，並把備份資料庫還原到本地MySQL資料庫，使用第三方工具完成資料遷移，程式碼實現SQL條數統計按照庫名和表名回寫結果，使用ultracompare實現比對。

使用工具：

第一種遷移工具

Microsoft SQL Server Migration Assistant for MySQL：推薦這款工具，微軟出的，但是也會有些問題,如部分表資料不能完全遷移

第二種遷移工具

Navicat Premium 12：不推薦，速度慢，極容易失敗

第三種遷移工具

Tapdata：這款也不錯，第三方工具，但不穩定，總記憶體溢位，底層Java寫的，需要與客服溝通解決使用中問題，客服響應速度不是很理想

比對工具

ultracompare：比對結果使用

工具使用

第一種遷移工具使用

Microsoft SQL Server Migration Assistant for MySQL，這款工具是微軟出的，真的很好用，而且速度也算比較快。

從https://www.microsoft.com/en-us/download/details.aspx?id=54257，下載安裝.

下面來介紹如何使用這款工具，具體步驟如下：

第一步：建立一個遷移工程

需要注意的是你需要選擇遷移到的SQL Server資料庫的版本，目前支援：SQL Azure,SQL Server 2005,SQL Server2008,SQL Server 2012,SQL Server2014,根據實際需要選擇你要遷移到目標資料庫的版本。

第二步：連線源資料庫和目標資料庫

上面的是源：MySQL，下面的是目標：SQL Server

第三步:選擇需要遷移的資料庫建立遷移分析報告

此報告會分析當前需要遷移的資料庫中的所有表結構並會生成一個可行性報告

生成的報告如下：

分析需要轉換的物件，表，資料庫有多少個，是否存在不可轉換的物件等資訊，如有檢查錯誤會下下面輸出

第四步： 轉換schema 也就是資料庫結構

遷移分兩步：1.轉換資料庫結構，2.遷移資料；

第五步：在源資料庫轉換完schema之後記得在目標資料庫上執行同步schema操作

否則轉換的資料庫結構是不會到目標資料庫的

點選同步之後同樣會有一個同步的報告：

點選OK之後就真正執行同步操作會將你轉換完的結構同步到目標資料庫上，建立對應的表及其他物件。同步操作完成之後會有如下輸出：

第六步：結構同步完成之後接下來就是資料遷移操作了

我們可以看到右邊有幾個tab頁，當前選中的是Type Map，會列出源資料庫和目標資料庫的欄位型別的mapping關係

因為不同資料庫之間的資料型別還是有所差異的。

點選Migrate Data之後需要再次確認輸入源資料庫密碼和目標資料庫密碼，然後開始真正的資料的遷移。

執行之後就等待完成就好，同樣會生成一個資料遷移完成的報告。至此資料遷移就可以完成了。

第二種遷移工具使用

Navicat Premium 12這款工具操作更簡單，因為很多步驟可以圖形化，相對簡便。

具體操作步驟如下：

建立MySQL、SqlServer連線，

雙擊MySQL的連線，建立連線

然後選擇navicat 的左上角工具

資料將自動匯入

注意：該工具將不會同步約束，比如：預設值之類的。但是非空約束是可以傳遞到SqlServer。

第三種遷移工具

Tapdata，這個工具是永久免費的，也算比較好用，具體使用方法如下：

第一步：配置MySQL 連線

1、點選 Tapdata Cloud 操作後臺左側選單欄的【連線管理】，然後點選右側區域【連線列表】右上角的【建立連線】按鈕，開啟連線型別選擇頁面，然後選擇MySQL

2、在開啟的連線資訊配置頁面依次輸入需要的配置資訊

【連 接 名 稱】：設定連線的名稱，多個連線的名稱不能重複

【資料庫地址】：資料庫 IP / Host

【端 口】：資料庫埠

【資料庫名稱】：tapdata 資料庫連線是以一個 db 為一個資料來源。這裡的 db 是指一個資料庫例項中的 database，而不是一個 mysql 例項。

【賬 號】：可以訪問資料庫的賬號

【密 碼】：資料庫賬號對應的密碼

【時 間 時 區】：預設使用該資料庫的時區；若指定時區，則使用指定後的時區設定

第二步：配置 SQL Server 連線

3、同第一步操作，點選左側選單欄的【連線管理】，然後點選右側區域【連線列表】右上角的【建立連線】按鈕，開啟連線型別選擇頁面，然後選擇 SQL Server

4、在開啟的連線資訊配置頁面依次輸入需要的配置資訊，配置完成後測試連線儲存即可。

第三步：選擇同步模式-全量/增量/全+增

進入Tapdata Cloud 操作後臺任務管理頁面，點選新增任務按鈕進入任務設定流程

根據剛才建好的連線，選定源端與目標端。

根據資料需求，選擇需要同步的庫、表，如果你對錶名有修改需要，可以通過頁面中的表名批量修改功能對目標端的表名進行批量設定。

在以上選項設定完畢後，下一步選擇同步型別，平臺提供全量同步、增量同步、全量+增量同步，設定寫入模式和讀取數量。

如果選擇的是全量+增量同步，在全量任務執行完畢後，Tapdata Agent 會自動進入增量同步狀態。在該狀態中，Tapdata Agent 會持續監聽源端的資料變化（包括：寫入、更新、刪除），並實時的將這些資料變化寫入目標端。

點選任務名稱可以開啟任務詳情頁面，可以檢視任務詳細資訊。

點選任務監控可以開啟任務執行詳情頁面，可以檢視任務進度/里程碑等的具體資訊。

第四步：進行資料校驗

一般同步完成後，我都習慣性進行一下資料校驗，防止踩坑。

Tapdata 有三種校驗模式，我常用最快的快速count校驗 ，只需要選擇到要校驗的表，不用設定其他複雜的引數和條件，簡單方便。

如果覺得不夠用，也可以選擇表全欄位值校驗 ，這個除了要選擇待校驗表外，還需要針對每一個表設定索引欄位。

在進行表全欄位值校驗時，還支援進行高階校驗。通過高階校驗可以新增JS校驗邏輯，可以對源和目標的資料進行校驗。

還有一個校驗方式關聯欄位值校驗 ，建立關聯欄位值校驗時，除了要選擇待校驗表外，還需要針對每一個表設定索引欄位。

以上就是 MySQL資料實時同步到 SQL Server 的操作分享。

使用到的SQL技術

MySQL部分

查詢某個庫的所有表名稱

select table_name from information_schema.tables where table_schema='資料庫名';

查詢某個資料庫中所有的表名 列名 欄位長度

SELECT TABLE_NAME as '表名', COLUMN_NAME as '列名',COLUMN_COMMENT,DATA_TYPE as '欄位型別' ,COLUMN_TYPE as '長度加型別' FROM information_schema.`COLUMNS` where TABLE_SCHEMA='資料庫名' order by  TABLE_NAME,COLUMN_NAME

sqlserver部分

SQLserver 查詢當前庫 所有表名

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name;

查詢資料庫中重複資料按照ID查詢

SELECT id FROM 資料庫名 where id<>'' GROUP BY id HAVING COUNT(*)>1

刪除一個表中各欄位完全相同情況，只留一條資料

-- delete  top(1) from 資料庫名 where id =id值

刪除日誌

USE [master]
GO
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE   --簡單模式
GO
USE 資料庫名
GO
DBCC SHRINKFILE (N'資料庫名_log' , 2, TRUNCATEONLY)  --設定壓縮後的日誌大小為2M，可以自行指定
GO
USE [master]
GO
ALTER DATABASE 資料庫名 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 資料庫名 SET RECOVERY FULL  --還原為完全模式
GO

修改表欄位

alter table 資料庫名 alter column 欄位名	欄位型別(長度)

解決sqlserver問題：超時時間已到。在操作完成之前超時時間已過或伺服器未響應。

1、點開選單欄：工具 - > 選項

2、設定指令碼執行超時時間(根據自己需求，0為不限制)

3、設定連結字串更新時間(根據自己需求,範圍為1-65535)

Navicat Premium 16 無限試用

@echo off

echo Delete HKEY_CURRENT_USER\Software\PremiumSoft\NavicatPremium\Registration[version and language]
for /f %%i in ('"REG QUERY "HKEY_CURRENT_USER\Software\PremiumSoft\NavicatPremium" /s | findstr /L Registration"') do (
    reg delete %%i /va /f
)
echo.

echo Delete Info folder under HKEY_CURRENT_USER\Software\Classes\CLSID
for /f %%i in ('"REG QUERY "HKEY_CURRENT_USER\Software\Classes\CLSID" /s | findstr /E Info"') do (
    reg delete %%i /va /f
)
echo.

echo Finish

pause

遷移資料成功後遇到的問題

  1. 部分表資料會重複，多次嘗試遷移引起，需要手動刪除重複資料，極小概率出現，一般出現在資料量大的表；
  2. 部分表欄位型別會改變，遷移工具會自動轉換成SqlServer支援欄位型別，並會影響部分應用服務，使其不能正常啟動，需要開發同事定位並修改成正確型別；
  3. 部分表會出現沒有主鍵和索引的現象，需要自行手動新增；
  4. 表欄位型別及索引、主鍵修改，如果逐表修改的話，工作量會很大。

寫在最後

整個遷移過程，共耗時近兩週，比我想象中的要難得多，遇到的問題也是真的很棘手，不得不說，當資料量很大時，確實會給資料的操作帶來巨大挑戰。

相關文章