升級版本選擇原則和建議

MySQL的升級需要遵循以下幾條原則和建議：

• 支持從MySQL5.7升級到8.0，注意僅支持GA版本之間的升級。
• 不支持跨大版本的升級，如從5.6升級到8.0是不支持的。
• 建議升級大版本前先升級到當前版本的最近小版本，如5.7先升級到5.7.35后再升級到8.0。
• 在大版本內部GA的小版本可以直接升級，如從8.0.9直接升級到8.0.26。

MySQL8.0的一些變化

在升級到8.0之前需要充分考慮版本變化帶來的使用兼容性的問題，其中不兼容的部分需要特別關注，建議升級大版本前做充分的測試。下面簡單介紹下MySQL8.0的部分變化。

• 數據字典

不同于之前的版本將字典數據存儲在元數據文件和非事務系統表中，MySQL8.0將包含數據庫對象的全局數據字典存儲在事務表中。在使用上如果設置了innodb_read_only 參數會導致所有表的創建、刪除、analyze、修改表引擎操作無法執行。CREATE TABLE dst_tbl LIKE src_tbl 要求src_tbl必須是base table。mysqldump和mysqlpump不會導出information_schema，不會導出MySQL Schema中數據字典表，需導出存儲過程和事件需指定–routines和–events選項，并且用戶需要全局selet權限。

• Authentication Plugin

MySQL8.0將默認身份驗證插件從mysql_native_password變更為caching_sha2_password，客戶端需要驗證現有版本是否支持。

• Configuration Changes

• MySQL8.0開始只有InnoDB和NDB引擎支持分區表，升級前需確保不存在非InnoDB引擎的分區表。
• 部分error code被啟用，詳見https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
• 默認字符集從latin1變更為utf8mb4，默認排序規則為utf8mb4_0900_ai_ci。注意這可能會導致新舊數據庫對象的字符集不一致，造成隱式類型轉換的問題。
• 8.0.11版本開始,如使用與初始化配置不同的lower_case_table_names值啟動數據庫時會報錯。

• [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (‘0’) and data dictionary (‘1’).
  [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.[ERROR] [MY-010119] [Server] Aborting
• Server Changes

• 8.0.11開始部分賬戶管理功能被刪除，如Grant命令修改用戶非權限特性，NO_AUTO_CREATE_USER模式，PASSWORD() 函數和 old_passwords 系統變量。
• 8.0.11開始刪除了部分兼容 SQL 模式：DB2、MAXDB、MSSQL、MYSQL323、MYSQL40、ORACLE、POSTGRESQL、NO_FIELD_OPTIONS、NO_KEY_OPTIONS、NO_TABLE_OPTIONS。
• 從 MySQL 8.0.3 開始，空間數據類型允許 SRID 屬性，以明確指示存儲在列中的值的空間參考系統 (SRS)。并刪除了部分非ST_前綴的空間函數。詳見https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals在線切換binlog format增加了更多限制。

• InnoDB Changes
  • information_Schema中，innodb_sys_% 改名為 innodb_%
  • zlib庫版本從1.2.3升級到1.2.11。
  • 只讀變量innodb_directories應該包含file-per-table和絕對路徑創建的通用表空間文件的路徑。
  • undo日志從系統表空間移出。默認會在innodb_undo_directory指定位置（未指定則在data dir）創建兩個undo表空間。從5.7升級到8.0時innodb-fast-shutdown需設置成0。
  • 8.0.17開始在創建表空間時，路徑不允許含 circular directory reference(/../)；升級前可以查詢INFORMATION_SCHEMA.INNODB_DATAFILES表檢查表空間路徑。mysql> CREATE TABLESPACE ts11 ADD DATAFILE ‘/data/mysql/log/test/../ts11.ibd’;
    ERROR 3121 (HY000): The ADD DATAFILE filepath cannot contain circular directory references.mysql> CREATE TABLESPACE ts11 ADD DATAFILE ‘/data/mysql/log/ts11.ibd’; Query OK, 0 rows affected (10.02 sec)
• SQL change
  • group by 不再支持DESC和ASC，GROUP BY {col_name | expr | position}, … [WITH ROLLUP]]
  • 保留字變更。https://dev.mysql.com/doc/refman/8.0/en/keywords.html
  • 升級后建議測試optimizer hints，在8.0中部分hint可能不再適用。
• 部分默認配置的變化。

升級過程包含哪些操作

升級MySQL時需要對現版本MySQL中的數據字典和元數據等進行更新。在MySQL Schema中需更新數據字典表和系統表，在其他Schema中需要更新一些內置的MySQL持有的表，如performance_Schema、information_schema和sys schema等。

升級過程大致分為兩個部分，升級數據字典和升級服務。

• 升級數據字典包括升級MySQL Schema中的數據字典表，以及performance schema，information_schema。升級數據字典表時，如當前版本低于期望版本，則服務器將創建新版本的數據字典表并將持久化的元數據拷貝到新表，在新舊表做原子性替換后重新初始化數據字典。服務器啟動后會按需執行，可以使用 –upgrade=NONE（8.0.16以后）或–no-dd-upgrade （8.0.16之前）參數啟動MySQL，阻止數據字典表的升級。
• 升級服務及其余升級任務，包括MySQL Schema中的非數據字典表，sys schema和用戶schema。

在8.0.16之前需使用mysql_upgrade執行除數據字典表外的其余升級步驟，在8.0.16以后該步驟由MySQL服務在啟動后執行。MySQL服務會根據升級到的版本以及in-place或logical升級的指示確定是否執行所有的升級步驟。

8.0.16開始啟動參數–upgrade= 控制MySQL服務在啟動時執行自動升級的動作。

--upgrade=AUTO MySQL升級所有過時的內容

--upgrade=NONE MySQL跳過升級步驟，可能會導致報錯

--upgrade=MINIMAL MySQL在必要時升級數據字典表，information_schema和information_schema。這可能會導致部分功能不能正常使用，例如MGR。

--upgrade=FORCE MySQL會升級所有的內容，這會檢查所有schema的所有對象，導致MySQL需要更長的時間啟動。此模式下MySQL會重新創建系統表 if they are missing。

升級前的檢查

在執行升級操作前需要做一些檢查工作，確認準備工作是否就緒，避免升級過程中出現異常。

• 可以使用MySQL Shell使用util.checkForServerUpgrade進行檢查，返回內容包括不符合遷移要求的問題，error的問題需要遷移前修改。

下面的例子中就存在一個不兼容的問題，ymh.t1表是一個MyISAM引擎的分區表，需將該表引擎調整為innodb后方可升級。

  MySQL  JS > util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"})
The MySQL server at 127.0.0.1:3307, version 5.7.23-log - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.26...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  Error: In MySQL 8.0 storage engine is responsible for providing its own
    partitioning handler, and the MySQL server no longer provides generic
    partitioning support. InnoDB and NDB are the only storage engines that
    provide a native partitioning handler that is supported in MySQL 8.0. A
    partitioned table using any other storage engine must be altered—either to
    convert it to InnoDB or NDB, or to remove its partitioning—before upgrading
    the server, else it cannot be used afterwards.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-configuration-changes

  ymh.t1 - MyISAM engine does not support native partitioning

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  No issues found

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  No issues found

15) Removed system variables
  No issues found

16) System variables with new default values
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

17) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates
  session.sql_mode -  of 1 session(s) does not contain either NO_ZERO_DATE or
    NO_ZERO_IN_DATE which allows insertion of zero dates

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations. 
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:
    
    [mysqld]
    default_authentication_plugin=mysql_native_password
    
    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   1
Warnings: 17
Notices:  0

1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.


#修改t1表引擎為InnoDB
mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL Shell的
util.checkForServerUpgrade工具主要做了以下檢查，當然我們也可以手動進行相關的檢查。

• 不應存在以下問題
  • 使用過時的數據類型和函數的表。如5.6.4之前的不支持小數點的時間格式。需在執行In-place升級前執行repair table修復。
  • 沒有孤立的.frm文件。
  • 觸發器不能缺失或空的definer或無效的creation context。
• 可執行以下命令檢查上述問題：mysqlcheck -u root -p –all-databases –check-upgrade
• 不應存在非InnoDB或NDB引擎的分區表，如存在需變更引擎或轉換成非分區表?？赏ㄟ^以下SQL檢查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

• 檢查是否使用了8.0新增的保留字。
• MySQL Schema中不應存在8.0數據字典表同名的表，可用以下SQL檢查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' and LOWER(TABLE_NAME) IN ( 'catalogs', 'character_sets', 'check_constraints', 'collations', 'column_statistics', 'column_type_elements', 'columns', 'dd_properties', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'resource_groups', 'routines', 'schemata', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'view_routine_usage', 'view_table_usage' );

• 不得有外鍵約束名稱長度超過 64 個字符的表，如存在則刪除后重建?？捎靡韵耂QL檢查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN   (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),                INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)    FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN    WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

• sql_mode系統參數不能含前面提到棄用模式。
• 視圖中不能有顯式定義超過64個字符的列名。可查看INFORMATION_SCHEMA.VIEWS檢查。
• 表和存儲過程中單個枚舉和SET列元素不能超過255個字符或1020個字節。
• 升級到8.0.13版本前，包括系統表空間和通用表空間在內的共享表空間中不能存在表分區。

#5.7版本以下SQL檢查
SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES   WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';

#8.0早期版本以下SQL檢查
SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES   WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single'; 

#如存在，可使用reorganize partition命令將共享表空間中的分區遷移到 file-per-table表空間
 ALTER TABLE $table_name REORGANIZE PARTITION $partition_name   INTO (partition_definition TABLESPACE=innodb_file_per_table);

• 查詢和存儲過程中不能在group by子句中使用ASC或者DESC。
• 不能使用8.0中不支持的功能和配置參數。https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
• 從 MySQL 8.0.19 開始，如果lower_case_table_names=1，升級過程會檢查表和模式名稱以確保所有字符都是小寫。如果發現表或架構名稱包含大寫字符，升級過程將失敗并顯示錯誤。

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);

如果出現上述問題導致的升級失敗，MySQL會將變更還原，這時刪除redo log并重啟5.7版本實例即可。注意關閉前一定設置innodb_fast_shutdown=0。

Linux系統升級Binary或Package-based安裝的MySQL

在這種場景下可以選擇in-place或者logical方式進行升級。

• in-place升級

關閉現有版本MySQL，將二進制或包替換成新版本并在現有數據目錄上啟動MySQL并執行升級任務的方式，稱為in-place升級。升級過程分為以下幾步：

1. 完成升級前檢查，并處理不合規問題。
2. 如果使用了XA事務，升級前通過命令xa recover未提交XA事務，并使用xa commit或xa rollback命令提交或回滾。
3. 如當前版本低于5.7.11且使用了加密表空間，升級前輪換keyring的master keyALTER INSTANCE ROTATE INNODB MASTER KEY。
4. 將innodb_fast_shutdown改為0或1。
5. 關閉現版本MySQL。
6. 升級MySQL二進制文件或軟件包。
7. 在現有數據目錄上啟動新版本MySQL。如果有加密的 InnoDB 表空間，請使用 –early-plugin-load選項加載keyring插件。如升級失敗請刪除redolog，啟動5.7版本并修復錯誤，設置innodb_fast_shutdown為0后關閉MySQL。再使用8.0版本MySQL啟動。
8. 如目標版本小于8.0.16，MySQL啟動后還需執行mysql_upgrade后重啟MySQL。

如下所示：

#當前版本為5.7.23
mysql> select @@global.version;
+------------------+
| @@global.version |
+------------------+
| 5.7.23-log       |
+------------------+
1 row in set (0.01 sec)

#使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 檢查升級到目標版本8.0.26，確認沒有error級別的問題
Errors:   0
Warnings: 17
Notices:  0

No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

#檢查沒有未提交的xa事務
mysql> xa recover;
Empty set (0.00 sec)

#將innodb_fast_shutdown改為0或1
mysql> set global innodb_fast_shutdown=0;select @@global.innodb_fast_shutdown;
Query OK, 0 rows affected (0.00 sec)

+-------------------------------+
| @@global.innodb_fast_shutdown |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

#關閉MySQL
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

#因目標版本8.0.26，直接在現有數據目錄上啟動新版本MySQL。由MySQL服務執行升級任務，可指定--upgrade=FORCE參數
[root@node1 ~]# cd /usr/local/mysql-8.0.26/bin/
[root@node1 bin]# ./mysqld_safe  --defaults-file=/etc/my3307.cnf --user=mysql --upgrade=FORCE &
[1] 106547
[root@node1 bin]#  mysqld_safe Adding '/usr/lib/libtcmalloc.so' to LD_PRELOAD for mysqld
2021-10-15T03:24:11.019020Z mysqld_safe Logging to '/data/mysql3307/log/mysqld.err'.
2021-10-15T03:24:11.073416Z mysqld_safe Starting mysqld daemon with databases from /data/mysql3307/data

#啟動后查看當前服務版本，確認已升級到目標版本
mysql> \s
--------------
mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)

Connection id:          11
Current database:
Current user:           root@127.0.0.1
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26-debug Source distribution
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3307
Binary data as:         Hexadecimal
Uptime:                 2 min 39 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 656  Flush tables: 4  Open tables: 35  Queries per second avg: 0.069
--------------

• logical升級

邏輯升級是指使用邏輯備份從舊版本MySQL中導出數據，安裝新版本MySQL并導入數據的升級方式。由于可能存在的不兼容問題會導致導入失敗，導出前需要做升級前檢查，導入前可能還需要對備份文件進行修改。

升級步驟如下：

1. 對舊版本數據做全量導出。mysqldump -u root -p –add-drop-table –routines –events –all-databases –force > data-for-upgrade.sql
2. 關閉當前版本數據庫。
3. 安裝8.0版本數據庫并初始化（從error log中獲取’root’@’localhost’用戶初始密碼）。
4. 在新的數據目錄中啟動MySQL8.0，并重置初始密碼。
5. 將備份文件導入mysql -u root -p –force < data-for-upgrade.sql。如導出文件包含系統表，則不建議導入時開啟GTID（gtid_mode=ON）。
6. 執行剩余的升級操作。目標版本大于8.0.16時需重啟服務器，啟動時使用–upgrade=FORCE參數。8.0.16之前的版本先執行mysql_upgrade再重啟MySQL。

#8.0.16以后的版本
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &

#8.0.16之前的版本
mysql_upgrade -u root -p
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &

#確認升級成功后，mysql schema中兩張不再使用的表可以自行清理
DROP TABLE mysql.event;
DROP TABLE mysql.proc;

墨天輪原文鏈接：https://www.modb.pro/db/135456?sjhy（復制到瀏覽器或者點擊“閱讀原文”立即查看）

關于作者

楊明翰，云和恩墨服務總監。擁有MySQL、TDSQL、TiDB、openGauss等認證。長期從事MySQL、PG、Redis、MongoDB的數據庫技術服務?，F負責云和恩墨西區開源數據庫交付運維工作；熱衷于開源數據庫產品的研究。