MySQL數(shù)據(jù)遷移那些事兒

前言：

在平時(shí)工作中，經(jīng)常會(huì)遇到數(shù)據(jù)遷移的需求，比如要遷移某個(gè)表、某個(gè)庫或某個(gè)實(shí)例。根據(jù)不同的需求可能要采取不同的遷移方案，數(shù)據(jù)遷移過程中也可能會(huì)遇到各種大小問題。本篇文章，我們一起來看下 MySQL 數(shù)據(jù)遷移那些事兒，希望能幫助到各位。

  1.關(guān)于數(shù)據(jù)遷移

首先引用下維基百科中對數(shù)據(jù)遷移的解釋：

數(shù)據(jù)遷移（data migration）是指選擇、準(zhǔn)備、提取和轉(zhuǎn)換數(shù)據(jù)，并將數(shù)據(jù)從一個(gè)計(jì)算機(jī)存儲系統(tǒng)永久地傳輸?shù)搅硪粋€(gè)計(jì)算機(jī)存儲系統(tǒng)的過程。此外，驗(yàn)證遷移數(shù)據(jù)的完整性和退役原來舊的數(shù)據(jù)存儲，也被認(rèn)為是整個(gè)數(shù)據(jù)遷移過程的一部分。

數(shù)據(jù)遷移是任何系統(tǒng)實(shí)現(xiàn)、升級或集成的關(guān)鍵考慮因素，通常以盡可能自動(dòng)化的方式執(zhí)行，從而將人力資源從繁瑣的任務(wù)中解放出來。

數(shù)據(jù)遷移有多種原因，包括服務(wù)器或存儲設(shè)備更換、維護(hù)或升級、應(yīng)用程序遷移、網(wǎng)站集成、災(zāi)難恢復(fù)和數(shù)據(jù)中心遷移。

對于 MySQL 數(shù)據(jù)庫，也會(huì)經(jīng)常碰到數(shù)據(jù)遷移的需求，比如說從測試庫遷到開發(fā)庫，或者從一臺機(jī)器遷移到另一臺機(jī)器，可能只是遷移一張表，也可能需要遷移整個(gè)數(shù)據(jù)庫實(shí)例。對于不同的需求可能要采取不同的遷移方案，但總體來講，MySQL 數(shù)據(jù)遷移方案大致可以分為物理遷移和邏輯遷移兩類。

  2.遷移方案及注意點(diǎn)

物理遷移適用于大數(shù)據(jù)量下的整體遷移。物理遷移包括拷貝數(shù)據(jù)文件和使用 XtraBackup 備份工具兩種。不同服務(wù)器之間可以采用物理遷移，我們可以在新的服務(wù)器上安裝好同版本的數(shù)據(jù)庫軟件，創(chuàng)建好相同目錄，建議配置文件也要和原數(shù)據(jù)庫相同，然后從原數(shù)據(jù)庫方拷貝來數(shù)據(jù)文件及日志文件，配置好文件組權(quán)限，之后在新服務(wù)器這邊使用 mysqld 命令啟動(dòng)數(shù)據(jù)庫。

使用物理遷移方案的優(yōu)點(diǎn)是比較快速，但需要停機(jī)遷移并且要求 MySQL 版本及配置必須和原服務(wù)器相同，也可能引起未知問題。

相比來說，邏輯遷移適用范圍更廣，無論是部分遷移還是全量遷移，都可以使用邏輯遷移。邏輯遷移中使用最多的就是通過 mysqldump 等備份工具導(dǎo)出再導(dǎo)入了。

mysqldump 同樣適用于不同版本不同配置之間的遷移，不過全量遷移時(shí)，筆者不建議使用 -A 參數(shù)備份全部數(shù)據(jù)庫，特別是對于不同版本之間遷移，可能某些系統(tǒng)庫稍有不同，遷移后容易出現(xiàn)未知問題。若使用 mysqldump 進(jìn)行全量遷移，可以按照下面步驟操作：

# 原庫執(zhí)行 得到的創(chuàng)建數(shù)據(jù)庫語句在新庫執(zhí)行 (排除系統(tǒng)庫)
SELECT
  CONCAT( 'CREATE DATABASE IF NOT EXISTS ', '`', SCHEMA_NAME, '`', ' DEFAULT CHARACTER SET ', DEFAULT_CHARACTER_SET_NAME, ';' ) AS CreateDatabaseQuery 
FROM
  information_schema.SCHEMATA 
WHERE
  SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
  
 # 原庫執(zhí)行 得到的創(chuàng)建用戶語句在新庫執(zhí)行 (排除系統(tǒng)用戶)
 SELECT
  CONCAT( 'create user \'', USER, '\'@\'', HOST, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQuery 
FROM
  mysql.`user` 
WHERE
  `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );
  
# 原庫執(zhí)行 show grants 得到用戶權(quán)限 然后逐一在新庫執(zhí)行 （也可以復(fù)制那幾張權(quán)限表到新庫）
show grants for 'testuser'@'%';

# 至此 新環(huán)境已經(jīng)存在要遷移的庫和用戶 只是沒有數(shù)據(jù)
# 原環(huán)境使用mysqldump備份除系統(tǒng)庫外的所有庫 
mysqldump  -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

# 然后導(dǎo)入新環(huán)境
mysql -uroot -pxxxx < db.sql

對于遷移部分庫和部分表，也可以采用上方類似方案，只是備份時(shí)要按需備份，下面列舉出幾種常見的 mysqldump 備份場景：

# 備份單個(gè)庫
mysqldump -uroot -pxxxxx -R -E --single-transactio --databases db1 > db1.sql

# 備份部分表
mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql

# 排查某些表
mysqldump -uroot -pxxxxx db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > db1.sql

# 只備份結(jié)構(gòu)或數(shù)據(jù)
mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql

總得來說，使用 mysqldump 方案更加靈活，為了快速遷移，我們應(yīng)該盡量減少備份內(nèi)容，比如說排除無用的日志表。對于一些大表，我們也可以采用單獨(dú)遷移的方案，比方說，db1 中的 tb1 表特別大，我們可以在備份時(shí)先排除 tb1，對于大表 tb1 ，可以使用 LOAD DATA 方式或舍棄再導(dǎo)入表空間的方式來遷移。

數(shù)據(jù)遷移過程中，可能會(huì)遇到各種錯(cuò)誤，一步步去解決即可。建議在新庫創(chuàng)建好用戶并授予好權(quán)限后再遷移，這樣可以避免出現(xiàn)視圖及函數(shù)導(dǎo)入錯(cuò)誤，因?yàn)橐晥D及函數(shù)有個(gè)定義者的概念。新環(huán)境執(zhí)行導(dǎo)入時(shí)，最好使用 root 等具有 SUPER 權(quán)限的管理員用戶，也可以避免一些因權(quán)限產(chǎn)生的問題。

遷移完成后，對于新環(huán)境，我們還應(yīng)該再次進(jìn)行檢查，比如表的個(gè)數(shù)是否相同，隨機(jī)抽查幾張表，數(shù)據(jù)是否相同、是否有亂碼等。只有確定無誤才大功告成。

總結(jié)：

本篇文章介紹了 MySQL 數(shù)據(jù)庫數(shù)據(jù)遷移相關(guān)方案及注意點(diǎn)，總結(jié)如下思維導(dǎo)圖：