【Golang+mysql】記一次mysql資料庫遷移（一）

# 記一次mysql資料庫遷移（一）

原文： https://github.com/stayfoo/stayfoo-hub/blob/master/docs/mysql/blog/%E8%AE%B0%E4%B8%80%E6%AC%A1mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%81%E7%A7%BB%EF%BC%88%E4%B8%80%EF%BC%89.md

## 一、準備

- 目標：

騰訊雲 `CVM` 自建 `mysql` 資料遷移到騰訊雲資料庫 `mysql` 。

- 騰訊雲 `CVM` 自建 `mysql` 現狀：
- 1、mysql 版本：`Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)`
- 2、mysql 檔案目錄資料大小： 2.4 G (`/var/lib/mysql`)
> - 檢視 `msyql` 資料目錄位置：`mysql> show variables like '%datadir%';`
> - 檢視 `/var/lib/mysql` 目錄的大小: `du -sh`

```
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.04 sec)
```

- 3、只有一個 `root` 賬戶，只允許 `localhost` 訪問
- 4、資料庫字符集：`charset=utf8`

- 起因：

`api` 服務、報表計劃任務、以及其他計劃任務、自建 `mysql` 都在同一臺騰訊 `CVM` 上面。計劃任務執行的時候，`mysql` 佔用 `CPU` 過高，會導致 `api` 服務無法正常使用。所以考慮騰訊雲資料庫 `mysql`，使用主從架構（先嚐試使用一主一從），`master mysql` 主要服務於 `api` 服務，`slave mysql` 主要用於計劃任務。

- 騰訊雲資料庫 `mysql` ：
- 1、騰訊雲資料庫 `mysql` 與騰訊雲伺服器 `CVM`，同一賬號下，同一個地域支援使用內網 `ip` 訪問。（比如 `CVM` 是重慶，雲資料庫必須也是重慶，並且是在同一賬號下）

- 2、購買騰訊雲 `mysql 5.7`（支援的最大版本，沒有`8.0`）：先購買一臺高可用版 `mysql`，作為 `master mysql`，之後可以擴充套件多臺 `slave mysql`

- 3、遷移工具：騰訊雲`DTS`，`mysqldump`（備用）


## 二、遷移

- 1、使用 DTS 遷移

購買完雲 `mysql`，初始化，開始使用 `DTS` 進行遷移。 自建 `mysql` 和 雲 `mysql` 是屬於同一個賬號下，同一區域下，可以使用內網直接訪問。



- 2、檢視 `CVM` 自建 `mysql` 使用者

```
mysql> select host,user,plugin from user;
```

發現只有一個 `root` 賬號，只能 `localhost` 訪問，需要建立新賬號，指定購買的雲 `mysql` 的內網 `ip` 授權訪問的新賬號。在 `CVM` 自建 `mysql` 建立賬號：

```
mysql> GRANT ALL PRIVILEGES ON *.* TO "stay"@"1xx.xx.0.0" IDENTIFIED BY "111";
```

建立賬號，並授權，發現報錯。提示不能用 `grant` 建立使用者。 原來 `mysql8.0` 以前的版本可以使用 `grant` 在授權的時候隱式的建立使用者。`mysql8.0` 以後已經不支援。`mysql8.0` 必須先建立使用者，然後再授權，命令如下:

```
mysql> CREATE USER 'stay'@'172.30.0.0' IDENTIFIED BY '密碼';
Query OK, 0 rows affected (0.48 sec)

mysql> grant all privileges on *.* TO 'stay'@'172.30.0.0';
Query OK, 0 rows affected (0.48 sec)
```

> 注意：密碼不能和 root 賬戶相同。

> 參考 mysql8.0 文件：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html


- 3、在騰訊雲 `mysql` 操作 `DTS`

輸入源庫設定，接入型別選擇雲主機自建，選擇自建 `mysql` 所在的主機例項`ID`，所屬區域，自建 `mysql` 的埠號，輸入新建的賬號，密碼。

配置完成之後，測試連通性，`Telnet` 通過，`MySQL Connect` 失敗。提示：`MySQL Connect無法連線源例項。請檢查例項賬號、密碼，並確認源例項是否取消對[172.30.0.0/20]的訪問限制` 如圖：



建立賬號的時候，對`[172.30.0.0/20]`的訪問是授權的。

嘗試設定 `CVM` 伺服器安全組，把 `172.30.0.0`（即雲`mysql`內網 `ip`）設定為信任訪問 `3306` 埠。測試連線依然不行。

嘗試新建立了一個對所有`ip`都能訪問授權的賬號，並測試在本地電腦遠端連線自建 `mysql` 是沒有問題的。然後把這個賬號密碼配置到 `DTS` 源庫上，測試連通性，依然不行。

給騰訊雲 mysql 提了工單，讓騰訊雲工程師協助解決。工單：



> 源庫連線失敗原因：
> - 資料庫賬號或密碼不正確
> - 源資料庫對來源IP進行了限制
> - 源資料庫伺服器設定了防火牆
> - 網路互通問題
> 可以參考：https://help.aliyun.com/document_detail/52099.html?spm=a2c4g.11186623.2.27.2d2e37admhRAPG


知道是因為版本問題導致的，自建 `mysql` 是 `8.0.15`，而騰訊雲 `mysql` 最高支援 `5.7`，`DTS` 不支援從 `8.0.15` 遷移到 `5.7`，所以連通性測試會一直失敗。


- 4、使用 `mysqldump` 手動遷移資料

> mysqldump 工作原理：查出需要備份的表結構 -> 生成一個 create sql語句（sql字尾名的文字檔案）；表中所有記錄 -> 轉成一條 insert 語句。

先從源資料庫匯出資料庫表結構資訊：
```
# 匯出資料庫表結構（匯出的是建立表結構的sql語句）
mysqldump --opt -d[庫名] -u[資料庫使用者名稱] -p[密碼] > /匯出的檔名字儲存路徑.sql
```

從源資料庫匯出資料資訊：
```
mysqldump -t[資料庫名] -u[資料庫使用者名稱] -p[資料庫密碼] > xxx.sql
```

`CVM` 主機上使用內網 `ip` 連結雲 `mysql`，先建立同名資料庫：

```
mysql> create DATABASE sf_factory charset=utf8;
```

然後，依次匯入資料庫表結構資訊，資料庫資料：

```
mysql> source [匯出的資料庫表結構.sql];
mysql> source [匯出的資料庫資料.sql];
```

> 參考 msyql 8.0官方文件：https://dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-to-other-server.html


- 5、驗證資料完整性

- 6、刪除自建 `mysql` 多餘賬號資訊

```
mysql> drop user stay@"172.30.0.0/20";
```

方式一：`drop` 不僅會將 `user` 表中的資料刪除，還會刪除對應許可權表內容。

```
#drop user xxxx; 預設刪除的是 'xxxx'@'%'
drop user xxxx;

drop user 'xxxx'@'localhost';
drop user 'xxxx'@'172.xx.x.x';
```

方式二：`delete` 只會刪除 `user` 中的內容。所以使用 `delete` 刪除使用者後需要執行 `FLUSH PRIVILEGES;`重新整理許可權，否則下次使用 `create` 建立使用者會報錯。