**前言：**

在平時工作中，經常會遇到資料遷移的需求，比如要遷移某個表、某個庫或某個例項。根據不同的需求可能要採取不同的遷移方案，資料遷移過程中也可能會遇到各種大小問題。本篇文章，我們一起來看下 MySQL 資料遷移那些事兒，希望能幫助到各位。

#### 1.關於資料遷移

首先引用下維基百科中對資料遷移的解釋：

> 資料遷移（data migration）是指選擇、準備、提取和轉換資料，並將資料從一個計算機儲存系統永久地傳輸到另一個計算機儲存系統的過程。此外，驗證遷移資料的完整性和退役原來舊的資料儲存，也被認為是整個資料遷移過程的一部分。
> 資料遷移是任何系統實現、升級或整合的關鍵考慮因素，通常以儘可能自動化的方式執行，從而將人力資源從繁瑣的任務中解放出來。
> 資料遷移有多種原因，包括伺服器或儲存裝置更換、維護或升級、應用程式遷移、網站整合、災難恢復和資料中心遷移。

對於 MySQL 資料庫，也會經常碰到資料遷移的需求，比如說從測試庫遷到開發庫，或者從一臺機器遷移到另一臺機器，可能只是遷移一張表，也可能需要遷移整個資料庫例項。對於不同的需求可能要採取不同的遷移方案，但總體來講，MySQL 資料遷移方案大致可以分為物理遷移和邏輯遷移兩類。

#### 2.遷移方案及注意點

物理遷移適用於大資料量下的整體遷移。物理遷移包括複製資料檔案和使用 XtraBackup 備份工具兩種。不同伺服器之間可以採用物理遷移，我們可以在新的伺服器上安裝好同版本的資料庫軟體，建立好相同目錄，建議配置檔案也要和原資料庫相同，然後從原資料庫方複製來資料檔案及日誌檔案，配置好檔案組許可權，之後在新伺服器這邊使用 mysqld 命令啟動資料庫。

使用物理遷移方案的優點是比較快速，但需要停機遷移並且要求 MySQL 版本及配置必須和原伺服器相同，也可能引起未知問題。

相比來說，邏輯遷移適用範圍更廣，無論是部分遷移還是全量遷移，都可以使用邏輯遷移。邏輯遷移中使用最多的就是透過 mysqldump 等備份工具匯出再匯入了。

mysqldump 同樣適用於不同版本不同配置之間的遷移，不過全量遷移時，筆者不建議使用 -A 引數備份全部資料庫，特別是對於不同版本之間遷移，可能某些系統庫稍有不同，遷移後容易出現未知問題。若使用 mysqldump 進行全量遷移，可以按照下面步驟操作：

```sql
# 原庫執行 得到的建立資料庫語句在新庫執行 (排除系統庫)
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 備份場景：

```sql
# 備份單個庫
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 資料庫資料遷移相關方案及注意點，總結如下思維導圖：

![image.png](https://cdn.nlark.com/yuque/0/2021/png/119537/1619660572727-c716bb74-edaa-4514-9987-4a5c513e1a19.png)

MySQL資料遷移那些事兒

相關文章