SQL Server 遷移 MySQL 百萬級資料異構同步架構實戰

記錄一個 SQL Server → MySQL 異構資料遷移專案的完整技術棧、環境佈署、效能調優與避坑經驗，單表千萬級 5 分鐘導入、15G 全庫 5 小時完成，解決亂碼、類型映射、高併發寫入等核心難題。

架構總覽

• 接收端 (寫入 MySQL)： Linux + MySQL 5.6+ (支援 GIS 地理坐標) + HandlerSocket (高效寫入)
• 執行端 (讀取 SQL Server、轉換、推送)： Linux + Perl + DBD::mysql + unixODBC + HandlerSocket
• 資料流向： SQL Server → Perl 腳本 (類型轉換/編碼處理) → HandlerSocket 直寫 MySQL (繞過 SQL 層)
• 水平擴展： 將大表按主鍵範圍分片，多執行端並行處理，線性提升吞吐。

環境佈署清單 (CentOS 6.4 為例)

1. 接收端 MySQL 5.6.14 編譯安裝

# 依賴
yum install -y cmake ncurses-devel bison openssl-devel

# 下載編譯
wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
tar zxf mysql-5.6.14.tar.gz
cd mysql-5.6.14
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_ZLIB=system -DWITH_SSL=system
make -j$(nproc) && make install

# 初始化與啟動
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
cp support-files/mysql.server /etc/init.d/mysqld
service mysqld start

2. 執行端 Perl 環境

# Perl 核心模組 (CPAN 安裝)
cpan DBI DBD::mysql

# unixODBC + FreeTDS 連接 SQL Server
yum install -y unixODBC unixODBC-devel freetds freetds-devel
# 配置 /etc/odbcinst.ini、/etc/odbc.ini 定義 SQL Server DSN

3. HandlerSocket 高效寫入插件 (雙端皆裝)

# MySQL 端安裝
cd /usr/local/mysql
./bin/mysql -u root -p < ./share/mysql/handlersocket/install.sql
# 配置 my.cnf
[mysqld]
handlersocket_port = 9998
handlersocket_port_wr = 9999
handlersocket_threads = 16
handlersocket_threads_wr = 8

# Perl 客戶端
cpan Net::HandlerSocket

核心技術難點與解決方案

1. 字符集與亂碼 (核心痛點)

• SQL Server 預設 Chinese_PRC_CI_AS (實際 GBK/CP936)，MySQL 目標 utf8mb4。
• Perl 端統一轉碼：Encode::decode('cp936', $raw) → Encode::encode('utf8', $decoded)。
• ODBC 連接字串指定 ClientCharset=UTF-8 (FreeTDS 0.91+ 支援)。
• HandlerSocket 直寫二進位，避免 SQL 層再次轉碼。

2. 資料類型映射表

3. 大表分片並行策略

# Perl 端邏輯
my $total = $dbh->selectrow_array("SELECT COUNT(*) FROM big_table");
my $chunk = 100000;  # 每片 10 萬行
my $workers = 8;     # 8 個執行端進程

for (my $i=0; $i<$workers; $i++) {
    my $start = int($i * $total / $workers);
    my $end   = int(($i+1) * $total / $workers) - 1;
    # 啟動子進程處理 ID 範圍 [$start, $end]
    fork && next;
    process_range($start, $end);
    exit;
}
wait; # 父進程等待所有子進程結束

4. HandlerSocket 寫入模式優化

• 使用 insert 模式 (主鍵衝突報錯) 或 update 模式 (不存在則插入)。
• 批次提交：累積 1000 行發一次 execute_batch，減少網路往返。
• 關閉雙寫緩衝 innodb_doublewrite=0 (遷移期間臨時，遷移後恢復)。
• 調大 innodb_log_file_size=1G、innodb_buffer_pool_size=70% 記憶體。

效能數據實測

遷移檢查清單 (上線前必驗)

1. 行數核對：SELECT COUNT(*) FROM src vs SELECT COUNT(*) FROM dst
2. 關鍵欄位抽樣比對：MD5/CRC32 校驗、業務關鍵欄位全量比對腳本 3. 索引/約束/觸發器/視圖/預存程序 完整對齊 4. 應用端連線測試：唯讀/讀寫分離、連接池、事務行為驗證 5. 回滾預案：保留 SQL Server 快照/備份 48 小時，DNS 切換可逆

經驗總結

1. 異構遷移核心是「類型映射+編碼統一」，Perl/Python 等腳本語言靈活度高，適合 ETL 中轉層。
2. HandlerSocket 繞過 SQL 解析層，大幅降低 CPU 開銷，百萬級寫入首選。
3. 水平分片 + 並行執行 是線性擴展吞吐的唯一路徑，避免單點瓶頸。
4. 遷移不止資料，更要驗證「語義等價」：排序規則、大小寫敏感、時區、NULL 語義、自增行為。

這套架構已在多個 TB 級遷移專案驗證穩定，關鍵在於細節打磨：編碼轉換零遺漏、類型映射無損失、並行調度無熱點、驗證機制全覆蓋。

1. 上一篇文章： MySQL 查詢資料庫下所有表的標準流程
2. 下一篇文章： MySQL 開發規範 35 條軍規：從命名到架構的完整標準

MySQL 資料庫相關知識

MySQL 資料庫基礎核心知識體系回顧

系統梳理 MySQL 核心基礎知識：資料表操作 (CRUD、

Windows 10 安裝 MySQL 5.7.13 解壓版最佳實踐：配置、初始化、安全設置

前言：解壓版部署，乾淨、可攜、易版本切換 MySQL 5.7

MySQL 索引優化深度實戰：從 B-Tree 到覆蓋索引、鎖優化

MySQL 索引優化深度實戰：從 B-Tree 到覆蓋索引、

MySQL ALTER TABLE 修改表結構實戰：欄位增刪、型別變更、索引重構、重命名與表選項調整全指南

ALTER TABLE 語法核心 ALTER [IGNOR