從 SQL Server 到 MySQL（二）：線上遷移，空中換髮動機

alswl發表於2019-01-12

（image via https://pixabay.com/en/military-stealth-bomber-refueling-602729/ ）

在上篇文章從 SQL Server 到 MySQL （一）：異構資料庫遷移 - Log4D 中，我們給大家介紹了從 SQL Server 到 MySQL 異構資料庫遷移的基本問題和全量解決方案。全量方案可以滿足一部分場景的需求，但是這個方案仍然是有缺陷的：遷移過程中需要停機，停機的時長和資料量相關。對於核心業務來說，停機就意味著損失。比如使用者中心的服務，以它的資料量來使用全量方案，會導致遷移過程中停機若干個小時。而一旦使用者中心停止服務，幾乎所有依賴於這個中央服務的系統都會停擺。

能不能做到無縫的線上遷移呢？系統不需要或者只需要極短暫的停機？作為有追求的技術人，我們一定要想辦法解決上面的問題。

線上遷移的原理和流程

針對 Oracle 到 MySQL，市面上已經有比較成熟的解決方案 - alibaba 的 yugong 專案。在解決 SQL Server 到 MySQL 線上遷移之前，我們先研究一下 yugong 是如何做到 Oracle 的線上遷移。

下圖是 yugong 針對 Oracle 到 MySQL 的增量遷移流程：

這其中有四個步驟：

增量資料收集 (建立 Oracle 表的增量物化檢視)
進行全量複製
進行增量複製 (可並行進行資料校驗)
原庫停寫，切到新庫

Oracle 物化檢視（Materialized View）是 Oracle 提供的一個機制。一個物化檢視就是主庫在某一個時間點上的複製，可以理解為是這個時間點上的 Snapshot。當主庫的資料持續更新時，物化檢視的更新可以通過獨立的批量更新完成，稱之為 refreshes。一批 refreshes 之間的變化，就對應到資料庫的內容變化情況。物化檢視經常用來將主庫的資料複製到從庫，也常常在資料倉儲用來快取複雜查詢。

物化檢視有多種配置方式，這裡比較關心重新整理方式和重新整理時間。重新整理方式有三種：

Complete Refresh：刪除所有資料記錄重新生成物化檢視
Fast Refresh：增量重新整理
Force Refresh：根據條件判斷使用 Complete Refresh 和 Fast Refres

重新整理機制有兩種模式： Refresh-on-commit 和 Refresh-On-Demand。

Oracle 基於物化檢視，就可以完成增量資料的獲取，從而滿足阿里的資料線上遷移。將這個技術問題泛化一下，想做到線上增量遷移需要有哪些特性？我們得到如下結論（針對源資料庫）：

增量變化：支援增量獲得增量資料庫變化
延遲：獲取變化資料這個動作耗時需要儘可能低
冪等一致性：變化資料的消費應當做到冪等，即不管目標資料庫已有資料什麼狀態，都可以無差別消費

回到我們面臨的問題上來，SQL Server 是否有這個機制滿足這三個特性呢？答案是肯定的，SQL Server 官方提供了 CDC 功能。

CDC 的工作原理

什麼是 CDC？ CDC 全稱 Change Data Capture，設計目的就是用來解決增量資料的。它是 SQL Server 2008 新增的特性，在這之前只能使用 SQl Server 2005 中的 after insert / after delete / after update Trigger 功能來獲得資料變化。

CDC 的工作原理如下：

當資料庫表發生變化時候，Capture process 會從 transaction log 裡面獲取資料變化，然後將這些資料記錄到 Change Table 裡面。有了這些資料，使用者可以通過特定的 CDC 查詢函式將這些變化資料查出來。

CDC 的資料結構和基本使用

CDC 的核心資料就是那些 Change Table 了，這裡我們給大家看一下 Change Table 長什麼樣，可以有個直觀的認識。

通過以下的函式開啟一張表（fruits）的 CDC 功能。

-- enable cdc for db
sys.sp_cdc_enable_db;
-- enable by table
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'fruits', @role_name = NULL;
-- list cdc enabled table
SELECT name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1;
複製程式碼

至此 CDC 功能已經開啟，如果需要檢視哪些表開啟了 CDC 功能，可以使用一下 SQL：

-- list cdc enabled table
SELECT name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1;
複製程式碼

開啟 CDC 會導致產生一張 Change Table 表 cdc.dbo_fruits_CT，這張表的表結構如何呢？

.schema cdc.dbo_fruits_CT
name            default  nullable  type          length  indexed
--------------  -------  --------  ------------  ------  -------
__$end_lsn      null     YES       binary        10      NO
__$operation    null     NO        int           4       NO
__$seqval       null     NO        binary        10      NO
__$start_lsn    null     NO        binary        10      YES
__$update_mask  null     YES       varbinary     128     NO
id              null     YES       int           4       NO
name            null     YES       varchar(255)  255     NO
複製程式碼

這張表中以 __ 開頭的欄位是 CDC 所記錄的後設資料，id 和 name 是 fruits 表的原始欄位。這意味著 CDC 的表結構和原始表結構是一一對應的。

接下來我們做一些業務操作，讓資料庫的資料發生一些變化，然後檢視 CDC 的 Change Table：

-- 1 step
DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);
-- 2 step
SET @begin_time = '2017-09-11 14:03:00.000';
SET @end_time   = '2017-09-11 14:10:00.000';
-- 3 step
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
-- 4 step
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_fruits(@begin_lsn, @end_lsn, 'all');
複製程式碼

這裡的操作含義是：

定義儲存過程中需要使用的 4 個變數
begin_time / end_time 是 Human Readable 的字串格式時間
begin_lsn / end_lsn 是通過 CDC 函式轉化過的 Log Sequence Number，代表資料庫變更的唯一操作 ID
根據 begin_lsn / end_lsn 查詢到 CDC 變化資料

查詢出來的資料如下所示：

__$start_lsn          __$end_lsn  __$seqval             __$operation  __$update_mask  id  name
--------------------  ----------  --------------------  ------------  --------------  --  ------
0000dede0000019f001a  null        0000dede0000019f0018  2             03              1   apple
0000dede000001ad0004  null        0000dede000001ad0003  2             03              2   apple2
0000dede000001ba0003  null        0000dede000001ba0002  3             02              2   apple2
0000dede000001ba0003  null        0000dede000001ba0002  4             02              2   apple3
0000dede000001c10003  null        0000dede000001c10002  2             03              3   apple4
0000dede000001cc0005  null        0000dede000001cc0002  1             03              3   apple4
複製程式碼

可以看到 Change Table 已經如實的記錄了我們操作內容，注意 __$operation 代表了資料庫操作：

1 => 刪除
2 => 插入
3 => 更新前資料
4 => 更新後資料

根據查出來的資料，我們可以重現這段時間資料庫的操作：

新增了 id 為 1 / 2 的兩條資料
更新了 id 為 2 的資料
插入了 id 為 3 的資料
刪除了 id 為 3 的資料

CDC 調優

有了 CDC 這個利器，終於意味著我們的方向是沒有問題的，我們終於稍稍吁了一口氣。但除了瞭解原理和使用方式，我們還需要深入瞭解 CDC 的工作機制，對其進行壓測、調優，瞭解其極限和邊界，否則一旦線上出現不可控的情況，就會對業務帶來巨大損失。

我們先看看 CDC 的工作流程，就可以知道有哪些核心引數可以調整：

上圖是 CDC Job 的工作流程：

藍色區域是一次 Log 掃描執行的最大掃描次數：maxscans number（maxscans）
藍色區域同時被最大掃描 transcation 數量控制：maxtrans
淺藍色區域是掃描間隔時間，單位是秒：pollinginterval

這三個引數平衡著 CDC 的伺服器資源消耗、吞吐量和延遲，根據具體場景，比如大欄位，寬表，BLOB 表，可以調整從而達到滿足業務需要。他們的預設值如下：

maxscan 預設值 10
maxtrans 預設值 500
pollinginterval 預設值 5 秒

CDC 壓測

掌握了能夠調整的核心引數，我們即將對 CDC 進行了多種形式的測試。在壓測之前，我們還需要確定關鍵的健康指標，這些指標有：

記憶體：buffer-cache-hit / page-life-expectancy / page-split 等
吞吐：batch-requets / sql-compilations / sql-re-compilations / transactions count
資源消耗：user-connections / processes-blocked / lock-waits / checkpoint-pages
作業系統層面：CPU 利用率、磁碟 IO

出於篇幅考慮，我們無法將所有測試結果貼出來，這裡放一個在併發 30 下面插入一百萬資料（隨機資料）進行展示：

測試結論是，在預設的 CDC 引數下面：

CDC 的開啟/關閉過程中會導致若干個 Process Block，大流量請求下面（15k TPS）過程會導致約 20 個左右 Process Block。這個過程中對伺服器的 IO / CPU 無明顯波動，開啟/關閉瞬間會帶來 mssql.sql-statistics.sql-compilations 劇烈波動。 CDC 開啟後，在大流量請求下面對 QPS / Page IO 無明顯波動，對伺服器的 IO / CPU 也無明顯波動， CDC 開啟後可以在 16k TPS 下正常工作。

如果對效能不達標，官方有一些簡單的優化指南：

調整 maxscan maxtrans pollinginterval
減少在插入後立刻插入
避免大批量寫操作
限制需要記錄的欄位
儘可能關閉 net changes
沒任務壓力時跑 cleanup
監控 log file 大小和 IO 壓力，確保不會寫爆磁碟
要設定 filegroup_name
開啟 sp_cdc_enable_table 之前設定 filegroup

yugong 的線上遷移機制

OK，截目前位置，我們已經具備了 CDC 這個工具，但是這僅僅提供了一種可能性，我們還需要一個工具將 CDC 的資料消費出來，並喂到 MySQL 裡面去。

好在有 yugong。 Yugong 官方提供了 Oracle 到 MySQL 的封裝，並且抽象了 Source / Target / SQL Tempalte 等介面，我們只要實現相關介面，就可以完成從 SQL Server 消費資料到 MySQL 了。

這裡我們不展開，我還會花專門的一篇文章講如何在 yugong 上面進行開發。可以提前劇透一下，我們已經將支援 SQL Server 的 yugong 版本開源了。

如何回滾

資料庫遷移這樣的專案，我們不僅僅要保證單向從 SQL Server 到 MySQL 的寫入，同時要從 MySQL 寫入 SQL Server。

這個流程同樣考慮增量寫入的要素：增量消費，延遲，冪等一致性。

MySQL 的 binlog 可以滿足這三個要素，需要注意的是，MySQL binlog 有三種模式， Statement based，Row based 和 Mixed。只有 Row based 才能滿足冪等一致性的要求。

確認理論上可行之後，我們一樣需要一個工具將 binlog 讀取出來，並且將其轉化為 SQL Server 可以消費的資料格式，然後寫入 SQL Server。

我們目光轉到 alibaba 的另外一個專案 Canal。 Canal 是阿里中介軟體團隊提供的 binlog 增量訂閱 & 消費元件。之所以叫元件，是由於 Canal 提供了 Canal-Server 應用和 Canal Client Library， Canal 會模擬成一個 MySQL 例項，作為 Slave 連線到 Master 上面，然後實時將 binlog 讀取出來。至於 binlog 讀出之後想怎麼使用，權看使用者如何使用。

我們基於 Canal 設計了一個簡單的資料流，在 yugong 中增加了這麼幾個功能：

SQL Server 的寫入功能
消費 Canal 資料來源的功能

Canal Server 中的 binlog 只能做一次性消費，內部實現是一個 Queue，為了滿足我們可以重複消費資料的能力，我們還額外設計了一個環節，將 Canal 的資料放到 Queue 中，在未來任意時間可以重複消費資料。我們選擇了 Redis 作為這個 Queue，資料流如下。

最佳實踐

資料庫的遷移在去 Windows 中，是最不容得出錯的環節。應用是無狀態的，出現問題可以通過回切較快地回滾。但資料庫的遷移就需要考慮周到，做好資源準備，釋出流程，故障預案處理。

考慮到多個事業部都需要經歷這個一個過程，我們專案組將每一個步驟都固化下來，形成了一個最佳實踐。我們的遷移步驟如下，供大家參考：

大階段	階段	事項	負責人
白天	存量資料階段	建立 MySQL 資料庫，準備相關賬號資源	DBA
		開啟 CDC	DBA
		從 Slave SQLServer dump 一份 snapshot 到 Backup SQL Server	DBA
		Backup SQL Server 消費資料， ETL 到 MySQL	DBA
	增量資料階段	確認 ETL 資料已經消費完成，檢查資料總條數	DBA
		從 Slave SQLServer 開始消費 CDC 資料，持續寫入 MySQL	DBA
		使用 yugong 檢查一天內資料的一致性	DBA
		檢查不一致的資料，10 分鐘之後人工進行檢查，確認是 CDC 延遲帶來的問題	DBA
		檢查資料總量條目	DBA
		使用 yugong 對抽樣表進行全量檢查	DBA
凌晨	應用釋出階段	停止 SQL Server 的應用	技術經理
		檢查沒有連線進入 SQL Server	DBA
		使用 yugong 檢查一天內資料的一致性	DBA
		檢查資料總量條目	DBA
		啟用基於 MySQL 的應用	運維
	測試階段	測試應用是否正常，迴歸所有功能	QA
		（臨時新增）測試 ReadOnly DB 的應用訪問情況	QA
	完成階段	接入流量	運維
	（可選）回滾階段	發現問題，直接將應用切回 SQL Server	運維
		事後進行資料審計，進行新增資料補償	DBA
		（可選）回滾過程中，使用 Canal 讀取 binlog，並使用 Canal Client 重放到 SQL Server	DBA