MySQL數據遷移那些事兒
本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。
前言：
在平時工作中，經常會遇到數據遷移的需求，比如要遷移某個表、某個庫或某個實例。根據不同的需求可能要采取不同的遷移方案，數據遷移過程中也可能會遇到各種大小問題。本篇文章，我們一起來看下 MySQL 數據遷移那些事兒，希望能幫助到各位。
1.關于數據遷移
首先引用下維基百科中對數據遷移的解釋：
數據遷移(data migration)是指選擇、準備、提取和轉換數據，并將數據從一個計算機存儲系統永久地傳輸到另一個計算機存儲系統的過程。此外，驗證遷移數據的完整性和退役原來舊的數據存儲，也被認為是整個數據遷移過程的一部分。
數據遷移是任何系統實現、升級或集成的關鍵考慮因素，通常以盡可能自動化的方式執行，從而將人力資源從繁瑣的任務中解放出來。
數據遷移有多種原因，包括服務器或存儲設備更換、維護或升級、應用程序遷移、網站集成、災難恢復和數據中心遷移。
對于 MySQL 數據庫，也會經常碰到數據遷移的需求，比如說從測試庫遷到開發庫，或者從一臺機器遷移到另一臺機器，可能只是遷移一張表，也可能需要遷移整個數據庫實例。對于不同的需求可能要采取不同的遷移方案，但總體來講，MySQL 數據遷移方案大致可以分為物理遷移和邏輯遷移兩類。
2.遷移方案及注意點
物理遷移適用于大數據量下的整體遷移。物理遷移包括拷貝數據文件和使用 XtraBackup 備份工具兩種。不同服務器之間可以采用物理遷移，我們可以在新的服務器上安裝好同版本的數據庫軟件，創建好相同目錄，建議配置文件也要和原數據庫相同，然后從原數據庫方拷貝來數據文件及日志文件，配置好文件組權限，之后在新服務器這邊使用 mysqld 命令啟動數據庫。
使用物理遷移方案的優點是比較快速，但需要停機遷移并且要求 MySQL 版本及配置必須和原服務器相同，也可能引起未知問題。
相比來說，邏輯遷移適用范圍更廣，無論是部分遷移還是全量遷移，都可以使用邏輯遷移。邏輯遷移中使用最多的就是通過 mysqldump 等備份工具導出再導入了。
mysqldump 同樣適用于不同版本不同配置之間的遷移，不過全量遷移時，筆者不建議使用 -A 參數備份全部數據庫，特別是對于不同版本之間遷移，可能某些系統庫稍有不同，遷移后容易出現未知問題。若使用 mysqldump 進行全量遷移，可以按照下面步驟操作：
- # 原庫執行 得到的創建數據庫語句在新庫執行 (排除系統庫)
- 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' );
- # 原庫執行 得到的創建用戶語句在新庫執行 (排除系統用戶)
- 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' );
- # 原庫執行 show grants 得到用戶權限 然后逐一在新庫執行 （也可以復制那幾張權限表到新庫）
- show grants for 'testuser'@'%';
- # 至此 新環境已經存在要遷移的庫和用戶 只是沒有數據
- # 原環境使用mysqldump備份除系統庫外的所有庫
- mysqldump -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql
- # 然后導入新環境
- mysql -uroot -pxxxx < db.sql
對于遷移部分庫和部分表，也可以采用上方類似方案，只是備份時要按需備份，下面列舉出幾種常見的 mysqldump 備份場景：
- # 備份單個庫
- 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
- # 只備份結構或數據
- mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
- mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql
總得來說，使用 mysqldump 方案更加靈活，為了快速遷移，我們應該盡量減少備份內容，比如說排除無用的日志表。對于一些大表，我們也可以采用單獨遷移的方案，比方說，db1 中的 tb1 表特別大，我們可以在備份時先排除 tb1，對于大表 tb1 ，可以使用 LOAD DATA 方式或舍棄再導入表空間的方式來遷移。
數據遷移過程中，可能會遇到各種錯誤，一步步去解決即可。建議在新庫創建好用戶并授予好權限后再遷移，這樣可以避免出現視圖及函數導入錯誤，因為視圖及函數有個定義者的概念。新環境執行導入時，最好使用 root 等具有 SUPER 權限的管理員用戶，也可以避免一些因權限產生的問題。
遷移完成后，對于新環境，我們還應該再次進行檢查，比如表的個數是否相同，隨機抽查幾張表，數據是否相同、是否有亂碼等。只有確定無誤才大功告成。
總結：
本篇文章介紹了 MySQL 數據庫數據遷移相關方案及注意點，總結如下思維導圖：
圖片
