MySQL如何遷移數據

本篇內容介紹了“MySQL如何遷移數據”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一. 測試用例/過程

目前開發商上云（外部MySQL遷移到CDB）提供多種方案，其中開發商的MySQL實例有外網IP的可以直接使用騰訊云數據庫遷移工具完成遷移（其他的遷移方法參見鏈接本次遷移任務中該開發商的所有MySQL實例均有外網代理IP供使用，故直接選用遷移工具完成數據導入。

遷移工具的基本原理：通過待遷移實例提供的高權限帳號獲取源實例基本的MySQL實例配置，并同步到目標CDB實例；通過mysqldump直接將源實例導出傳輸到CDB實例后導入；源數據庫實例和目標CDB建立主從關系同步新數據。其中CDB實例與源IDC之間通過NAT方式以一臺帶外網的服務器為中轉發起通信。

1. 遷移工具基本功能

在騰訊云數據庫控制臺頁面根據引導建立遷移任務；在后臺管理頁面觀察遷移任務后臺日志等。

任務開始運行后檢測代理機器流量變化，CDB的寫入等數據展示

知識點：如何為測試數據庫產生較大的數據量。這里推薦一個工具mysql_gen_data。產生測試數據并導入到MySQL的過程如下：

#!/bin/bash
./mysql_gen_data -f "%1n,%100s,%100s,%100s,%100s,%100s" \
 -n 10000000 >random.dat
mysql –uroot –p*********** –e “create database cdbtest;use cdbtest; \
CREATE TABLE cdbtest_tb \
(c1 varchar(100),c2 varchar(100),c3 varchar(100), \
c4 varchar(100),c5 varchar(100),c6 varchar(100)) \
ENGINE=InnoDB DEFAULT CHARSET=utf8;”
for i in {1..10}; do
    echo "$(date '+%T') round $i start"
    echo "prepare data..."
    sed -i "s/^/$i/" random.dat
    echo "insert data..."
    mysql -uroot –p******** cdbtest -e "LOAD DATA local INFILE '/data/random.dat' into table cdbtest_tb fields terminated by',';"
    echo "$(date '+%T') round $i end"
done

后臺與騰訊云管理臺查看本次測試任務，遷移成功完成。

2. 主從以及從機和CDB建立主從的同步

由于本次遷移的開發商將使用他們自建IDC的從機向CDB遷移數據，簡單關系如下圖，之前沒有使用遷移工具進行過類似操作，故進行本次測試。

知識點：如何配置MySQL的主從關系。測試的MySQL主從的配置如下：（主MySQL）

server_id = 98
log_bin = binlog
binlog_format = ROW
innodb_stats_on_metadata = off

后臺與騰訊云管理臺查看本次測試任務，遷移成功完成。

3. 多實例+較大binlog并發同步

開發商在經過相關測試后，一期計劃15個實例并發遷移到CDB，每天總共產生約100G的binlog。由于之前遷移工具沒有大并發使用，且單日有較大數據更新，故提前測試用戶場景。測試的基本架構如下圖：在一個服務器上開啟15個MySQL實例映射到不同端口，15個MySQL實例同時和15個CDB實例建立主從，并發起遷移任務。

知識點：如何在一臺服務器上創建多個MySQL實例？這里使用的MySQL自帶的mysqld_multi工具，其實這只是一個perl腳本，開啟多實例配置如下（/etc/my.conf）可以視內存大小，開多個mysqld的配置項：

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root 
password = ******

[mysqld1]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysqld.pid
server_id = 11 
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off

symbolic-links=0
user = root
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld2]
port = 3312
datadir=/data/mysql12
socket=/data/mysql12/mysql.sock
pid-file=/data/mysql12/mysqld.pid
server_id = 12
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off
user = root
symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3]
........
[mysqld4]
.......

然后使用mysqld_multi start 1-4啟動配置項里面的對應數量實例即可。啟動多個MySQL實例如圖：

通過定時update對應數據庫實例的數據，產生較大量的binlog，單次update產生700Mbinlog，每2小時執行一次，每天產生7001215=126G.簡單代碼如下：

#!/bin/sh

SET_STRING=`date +"%s"`
LOG_NAME="/data/log/update.log"
NOW_STRING=`date +"[%Y-%m-%d %H:%M:%S]"`

for i in {12..26}

do
BEGIN_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
echo ${BEGIN_TIME}" Update data in this time is:"$SET_STRING >> $LOG_NAME
echo ${BEGIN_TIME}" Update database"${i} "start..." >> $LOG_NAME
mysql -uroot migrate${i} -S /data/mysql${i}/mysql.sock -e "update tb set data1="${SET_STRING}""
END_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
echo ${END_TIME}" Update database"${i} "end..." >> $LOG_NAME
done

使用數據庫遷移工具建立15個遷移任務，控制臺和后臺檢查均遷移成功：

同時為了檢驗大量binlog情況下數據完整性，寫了簡單腳本定時檢查數據是否有更新，腳本如下：（這里經過測試發現可以通過廣州跳板機直接連接CDB實例的masterIP，故直接在廣州跳板機腳本拉取IDC更新數據，同時對比CDB實例數據，寫入日志）

#!/bin/sh

DATA_CORRECT=$1
NOW_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
cat my.file | while read line
do

IP=`echo $line | awk -F " " '{print $1}'`
PORT=`echo $line | awk -F " " '{print $2}'`
DATABASE=`echo $line | awk -F " " '{print $3}'`
DATA_INBASE=`mysql -uroot -P${PORT} -h${IP} -p123456cdb ${DATABASE} -e "select data1 from tb limit 1\G" | grep data1 | awk -F " " '{print $2}'`

echo ${NOW_TIME}"[INFO]Data you want to update to ${DATABASE} is:"$DATA_CORRECT
echo ${NOW_TIME}"[INFO]Data from Database "$DATABASE" is:"$DATA_INBASE

if [ $DATA_INBASE -eq $DATA_CORRECT ]
then
echo ${NOW_TIME}"[SUCCESS]"$DATABASE" update succesfully!"
else
echo ${NOW_TIME}"[ERROR]"$DATABASE" update ERROR!" 
fi

done

通過校驗日志可以看到，數據更新均成功完成。

二. 開發商遷移測試數據記錄

以上我方內部測試完成后，開發商自行進行了3次遷移，相關數據如下：

某次遷移的帶寬表現。
由于開發商出口帶寬只有約500Mbps，經過測試發現遷移瓶頸主要出現在帶寬限制上。實際并發時帶寬大小待二期遷移時確認。

三.遇到的問題

1. 首次創建主從無法連接源數據庫
   現象：如圖所示，每次建任務后總提示源數據庫無法連接
   Error:Can’t connect to MySQL server on 10.*.*.*
   分析解決：由于遷移工具本質是CDB代理經過NAT通過外網和IDCMySQL實例相連，CDB的代理系統時間和NAT外網機器有差異，同時IDC開啟連接重用，導致建立連接時前后時間不一致，系統認為為異常包，丟棄，連接失敗。直接修改IDC服務器的內核參數，即net.ipv4.tcp_timestamps = 0和net.ipv4.tcp_tw_recycle = 0即可

2. 跨版本遷移的存儲過程遷移失敗
   現象：如圖所示，開發商在遷移過程中出現proc表無法遷移的現象
   ERROR:Can’t load from mysql.proc. The table is probably corrupted
   解決：經CDB開發同事確認跨版本遷移的proc表因字段定義不同存在異常，發布版本跳過proc表解決。

3. 遷移測試中創建新數據庫導致binlog導入失敗
   現象：遷移任務出現錯誤，無法遷移存儲過程，binlog追加失敗
   errno:1049:Error ‘Unknown database ‘xxxx’on query.
   解決：原因為本次遷移選定了只遷移某個數據庫，遷移過程中新建了一個數據庫，并開啟binlog，導致CDB拉到的binlog有新數據庫信息，和遷移數據庫不匹配。解決方法為遷移過程不要出現DDL操作。

“MySQL如何遷移數據”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！