大家好，這次大表哥分享的是 mysql MGR 的滾動升級過程。

對于生產(chǎn)環(huán)境來說，升級數(shù)據(jù)庫是一件十分 critcal 重要的事情。 風(fēng)險是十分大的！ 尤其是大版本的數(shù)據(jù)庫升級，作為DBA必須要謹(jǐn)慎對待。
對于數(shù)據(jù)庫的升級：

1. 需要開發(fā)人員進(jìn)行必要的代碼改造
2. 需要測試人員進(jìn)行完全的回歸的功能點測試和必要的性能測試
3. 需要DBA根據(jù)自己數(shù)據(jù)庫特性，進(jìn)行前期分析升級的可能性以及故障點。 并且一定要準(zhǔn)備升級失敗的版本降級回滾的方案。
   DBA 需要根據(jù)升級的風(fēng)險以及數(shù)據(jù)庫的大?。哼x擇是inplace 的升級 還是 logical 的升級。
4. 需要全體人員在測試環(huán)境上進(jìn)行升級前的預(yù)演，及時發(fā)現(xiàn)潛在的問題。

簡單地說， 數(shù)據(jù)庫升級絕對不是簡單的DBA一個人的事情，前期就需要所有相關(guān)人員的參與。

本片本章到此其實最重要已經(jīng)說完了。

下面我們看看從DBA的技術(shù)層面，如何對MYSQL MGR 進(jìn)行升級：

首先中最要的說三遍：
一定要閱讀 官網(wǎng)的mysql 升級文檔?。。?br /> 一定要閱讀 官網(wǎng)的mysql 升級文檔?。?！
一定要閱讀 官網(wǎng)的mysql 升級文檔?。。?/p>

比起市面上任何的資料，博客，公眾號， 官方文檔是最權(quán)威的?。?！ 必要要仔細(xì)閱讀一遍。。。

https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

一共14個小的章節(jié)：

1）我們先看一下 mysql 版本的升級路徑

mysql 5.7 -> mysql 8.0
mysql 5.6 -> mysql 5.7 -> mysql 8.0
mysql 8.0.x - mysql 8.0.y

簡單地說： 是不能夠跨越大版本升級的， 想從 5.6 直接升級到 8.0 是不行的。

2）需要分析一下升級到 mysql 8.0 對你的應(yīng)用的影響(如果老的版本是5.7)

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

這里列出比較重要的幾點：

a)默認(rèn)權(quán)限插件的改變： mysql_native_password --》 caching_sha2_password
這個可能會導(dǎo)致應(yīng)用程序連不上
b)SQL_MODE 默認(rèn)值的改變： 可能會導(dǎo)致應(yīng)用的SQL語法解析報錯

3） 升級前對數(shù)據(jù)庫進(jìn)行全面的檢查

https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

官網(wǎng)推薦用： mysql shell 的升級檢查工具 ： mysqlsh> util.checkForServerUpgrade()

4）升級前一定要對數(shù)據(jù)庫進(jìn)行全備份。

建議使用 xtrabackup 或者企業(yè)版的備份工具

5）滾動升級數(shù)據(jù)庫

我們這次是 從 8.0.15 升級到 8.0.27, 采取的是MGR的滾動升級。

原有數(shù)據(jù)庫信息：

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 |        3072 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50  |        3072 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49  |        3072 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

我們采用滾動升級：

我們先是升級一下 secondary : 10.67.39.49 這臺機器：
老的mysql home : /opt/mysql/product/8.0/bin
新的mysql home: /opt/mysql/product/percona8.0/bin

a)設(shè)置 關(guān)閉數(shù)據(jù)庫為 slow 的方式：

mysql> set global innodb_fast_shutdown = 0;
Query OK, 0 rows affected (0.00 sec)

b)關(guān)閉數(shù)據(jù)庫

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)


mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

c) 新的 mysql 軟件啟動數(shù)據(jù)庫

/opt/mysql/product/percona8.0/bin/mysqld_safe --defaults-file=/data/mysql3072/my_mysql3072.cnf > update.log 2>&1 &

我們從error log 中也可以看到 data dictionary 和 mysql server upgrading 的信息的完成的信息

2022-06-01T13:37:45.561650+08:00 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80014' to '80023'.
2022-06-01T13:37:45.561688+08:00 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80015' to '80027'.

2022-06-01T13:37:46.236266+08:00 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80014' to '80023' completed.
2022-06-01T13:37:53.746243+08:00 5 [System] [MY-013381] [Server] Server upgrade from '80015' to '80027' completed.

根據(jù)官方的升級文檔來說升級是分為如下2個步驟：

Step 1: Data dictionary upgrade.

This step upgrades:

The data dictionary tables in the mysql schema. If the actual data dictionary version is lower than the current expected version, the server creates data dictionary tables with updated definitions, copies persisted metadata to the new tables, atomically replaces the old tables with the new ones, and reinitializes the data dictionary.

The Performance Schema, INFORMATION_SCHEMA, and ndbinfo.

Step 2: Server upgrade.

This step comprises all other upgrade tasks. If the server version of the existing MySQL installation is lower than that of the new installed MySQL version, everything else must be upgraded:

The system tables in the mysql schema (the remaining non-data dictionary tables).

The sys schema.

User schemas.

對于升級到 mysql 8.0.16版本以及之后的版本來說， 不再需要執(zhí)行 mysql_upgrade 這個命令， 未來這個命令會被廢棄掉。

d) 我們觀察這個時候MGR的集群狀態(tài)

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 |        3072 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50  |        3072 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49  |        3072 | ONLINE       | SECONDARY   | 8.0.27         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

e)我們再去升級另外一個節(jié)點10.67.39.149

mysql> set global innodb_fast_shutdown = 0;
Query OK, 0 rows affected (0.00 sec)


mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)


mysql> exit;
Bye

uat mysql@wqdcsrv3354[14:01:19]:~ $ /opt/mysql/product/percona8.0/bin/mysqld_safe --defaults-file=/data/mysql3072/my_mysql3072.cnf > update.log 2>&1 &
[1] 111199

f)我們查看MGR的集群的狀態(tài)

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 |        3072 | ONLINE       | SECONDARY   | 8.0.27         |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50  |        3072 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49  |        3072 | ONLINE       | SECONDARY   | 8.0.27         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

g) 手動切換主節(jié)點到 10.67.39.49

mysql> select group_replication_set_as_primary('16d296b7-83d1-11ec-8d13-005056aefbb6');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('16d296b7-83d1-11ec-8d13-005056aefbb6') |
+--------------------------------------------------------------------------+
| Primary server switched to: 16d296b7-83d1-11ec-8d13-005056aefbb6         |
+--------------------------------------------------------------------------+
1 row in set (0.17 sec)


mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 |        3072 | ONLINE       | PRIMARY     | 8.0.27         |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50  |        3072 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49  |        3072 | ONLINE       | SECONDARY   | 8.0.27         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

h) 關(guān)機升級最后一個節(jié)點

mysql> set global innodb_fast_shutdown = 0;
Query OK, 0 rows affected (0.00 sec)


mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)


mysql> exit;
Bye

uat mysql@wqdcsrv3352[14:16:46]:~ $ /opt/mysql/product/percona8.0/bin/mysqld_safe --defaults-file=/data/mysql3072/my_mysql3072.cnf > update.log 2>&1 &
[1] 33105

i) 查看此時的MGR的集群的狀態(tài)

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 16d296b7-83d1-11ec-8d13-005056aefbb6 | 10.67.39.149 |        3072 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | 30af63d9-7db1-11ec-9d58-f403439dfd00 | 10.67.38.50  |        3072 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
| group_replication_applier | 89866a9b-7db1-11ec-9d58-f403439dfd00 | 10.67.39.49  |        3072 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

至此所有的節(jié)點都升級到了 8.0.27 版本。

最后，還是要強調(diào)一下，DBA 必須要根據(jù)自身的應(yīng)用系統(tǒng)的特點，來制定合適的升級方案：

1）Inplace（本地升級）: 適合小版本的升級，代碼無需改動，MGR，或者master/slave這種的主從 可以實現(xiàn)不down機的滾動升級
2）logical （邏輯同步升級）: 適合數(shù)據(jù)大版本的跨越的升級， 前期需要進(jìn)行數(shù)據(jù)同步， 需要和應(yīng)用運維配置來實現(xiàn)系統(tǒng)的cutover，一旦出問題，可以快速切回到老版本的數(shù)據(jù)庫。