不可不知的 MySQL 升級利器及 5.7 升級到 8.0 的注意事項

iVictor發表於2022-05-16
MySql

資料庫升級，是一項讓人喜憂參半的工程。喜的是，通過升級，可以享受新版本帶來的新特性及效能提升。憂的是，新版本可能與老的版本不相容，不相容主要體現在以下三方面：

  1. 語法不相容。
  2. 語義不相容。同一個SQL，在新老版本執行結果不一致。
  3. 新版本的查詢效能更差。

所以，在對線上資料庫進行升級之前，一般都會在測試環境進行大量的測試，包括功能測試和效能測試。

很多人可能會覺得麻煩，於是對待升級就秉持著一種“不主動，也拒絕”的態度，怎奈何新版本效能更好，新特性更多，而且老版本在產品維護週期結束後，也存在安全風險。

升還是不升呢？that is a question。

下面我們介紹一個 MySQL 升級利器，可極大減輕 DBA 包括開發童鞋在升級資料庫時的心智負擔和工作負擔。

這個利器就是 pt-upgrade。

pt-upgrade 是 Percona Toolkit 中的一個工具，可幫忙我們從業務 SQL 層面檢查新老版本的相容性。

如何安裝 Percona Toolkit，可參考：MySQL 中如何歸檔資料

pt-upgrade 的實現原理

它的檢測思路很簡單，給定一個 SQL，分別在兩個不同版本的例項上執行，看看是否一致。

具體來說，它會檢查以下幾項：

  • Row count：查詢返回的行數是否一致。
  • Row data：查詢的結果是否一致。
  • Warnings：是否提示 warning。正常來說，要麼都提示 warning，要麼都不提示 warning。
  • Query time：查詢時間是否在同一個量級，或者新版本的執行時間是否更短。
  • Query errors：查詢如果在一個例項中出現語法錯誤，會提示 Query errors。
  • SQL errors：查詢如果在兩個例項中同時出現語法錯誤，會提示 SQL errors。

pt-upgrade 的常見用法

pt-upgrade 的使用比較簡單，只需提供兩個例項的 DSN （例項連線資訊）和檔名。

常見用法有以下兩種：

（1）直接比較一個檔案中的 SQL 在兩個例項中的執行效果。

# pt-upgrade h=host1 h=host2 slow.log

可通過 --type 指定檔案的型別，支援 slowlog（慢日誌），genlog（General Log），binlog（通過 mysqlbinlog 解析後的文字檔案），rawlog（ SQL語句 ），tcpdump。不指定，則預設是慢日誌。

（2）先生成一個基準測試結果，然後再基於這個結果測試其它環境的相容性。

# pt-upgrade h=host1 --save-results host1_results/ slow.log
# pt-upgrade host1_results1/ h=host2

第二種用法適用於兩個例項不能同時訪問，或者需要基於一個基準測試結果進行多次測試。

Demo

看下面這個 Demo。

pt_upgrade_test.sql 包含了若干條測試語句。

# cat /tmp/pt_upgrade_test.sql
select "a word a" REGEXP "[[:<:]]word[[:>:]]";
select dept_no,count(*) from employees.dept_emp group by dept_no desc;
grant select on employees.* to 'u1'@'%' identified by '123456';
create table employees.t1(id int primary key,c1 text not null default ('')); 
select * from employees.dept_emp group by dept_no;

這裡給出的幾條測試語句都極具代表性，都是升級過程中需要注意的 SQL。

下面我們看看這些語句在 MySQL 5.7 和 MySQL 8.0 中的執行情況。

# pt-upgrade h=127.0.0.1,P=3307,u=pt_user,p=pt_pass h=127.0.0.1,P=3306,u=pt_user,p=pt_pass --type rawlog /tmp/pt_upgrade_test.sql --no-read-only

#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------

File: /tmp/pt_upgrade_test.sql
Size: 311

#-----------------------------------------------------------------------
# Hosts
#-----------------------------------------------------------------------

host1:

  DSN:       h=127.0.0.1,P=3307
  hostname:  slowtech
  MySQL:     MySQL Community Server (GPL) 5.7.36

host2:

  DSN:       h=127.0.0.1,P=3306
  hostname:  slowtech
  MySQL:     MySQL Community Server - GPL 8.0.27

########################################################################
# Query class 00A13DD81BF65D41
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

grant select on employees.* to ?@? identified by ?;

##
## Query errors diffs: 1
##

-- 1.

No error

vs.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456'' at line 1 [for Statement "grant select on employees.* to 'u1'@'%' identified by '123456';"]

grant select on employees.* to 'u1'@'%' identified by '123456';

########################################################################
# Query class 296E46FE3AEE9B6C
########################################################################

Reporting class because it has SQL errors, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

select * from employees.dept_emp group by dept_no;

##
## SQL errors: 1
##

-- 1.

On both hosts:

DBD::mysql::st execute failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.dept_emp.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [for Statement "select * from employees.dept_emp group by dept_no;"]

select * from employees.dept_emp group by dept_no;

########################################################################
# Query class 8B81ACF1E68DE066
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

create table employees.t?(id int primary key,c? text not ? default (?));

##
## Query errors diffs: 1
##

-- 1.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(''))' at line 1 [for Statement "create table employees.t1(id int primary key,c1 text not null default ('')); "]

vs.

No error

create table employees.t1(id int primary key,c1 text not null default (''));

########################################################################
# Query class 92E8E91AB47593A5
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

select ? regexp ?;

##
## Query errors diffs: 1
##

-- 1.

No error

vs.

DBD::mysql::st execute failed: Illegal argument to a regular expression. [for Statement "select "a word a" REGEXP "[[:<:]]word[[:>:]]";"]

select "a word a" REGEXP "[[:<:]]word[[:>:]]";

########################################################################
# Query class D3F390B1B46CF9EA
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

select dept_no,count(*) from employees.dept_emp group by dept_no desc;

##
## Query errors diffs: 1
##

-- 1.

No error

vs.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1 [for Statement "select dept_no,count(*) from employees.dept_emp group by dept_no desc;"]

select dept_no,count(*) from employees.dept_emp group by dept_no desc;

#-----------------------------------------------------------------------
# Stats
#-----------------------------------------------------------------------

failed_queries        1
not_select            0
queries_filtered      0
queries_no_diffs      0
queries_read          5
queries_with_diffs    0
queries_with_errors   4

3307，3306 埠分別對應 MySQL 5.7、MySQL 8.0 例項。

對於檔案中的每一個 SQL ，都會在這兩個例項中執行。如果每個差異 SQL 的結果都列印出來的話，最後的輸出將十分龐雜。為了簡化最後的輸出結果，pt-upgrade 會對 SQL 進行分類，同一類 SQL 的輸出次數受到 --max-class-size 和 --max-examples 的限制。

分析輸出結果

結合執行的 SQL，我們分析下輸出結果。

SQL 3

grant select on employees.* to 'u1'@'%' identified by '123456';

在 MySQL 8.0 之前，對一個使用者進行授權（grant）操作，如果該使用者不存在，會隱式建立。而在 MySQL 8.0 中，該命令會直接報錯，必須先建立使用者，再授權。

所以，上面這條 SQL 需拆分為以下兩條 SQL 來執行。

create user 'u1'@'%' identified by '123456';
grant select on employees.* to 'u1'@'%';

這個查詢只在一個例項中出現語法錯誤，所以 pt-upgrade 會將其歸類為 Query errors 。

SQL 5

select * from employees.dept_emp group by dept_no;

從 MySQL 5.7 開始，SQL_MODE 的預設值發生了變化，包含了 ONLY_FULL_GROUP_BY 。

ONLY_FULL_GROUP_BY 要求，對於 GROUP BY 操作，SELECT 列表中只能出現分組列（即 GROUP BY 後面的列）和聚合函式（ SUM，AVG，MAX等 ），不允許出現其它非分組列。

很明顯，上面這條 SQL 違背了這一要求。所以，無論是在 MySQL 5.7 還是 8.0 中，該 SQL 都會報錯。

這個查詢在兩個例項中都出現了語法錯誤，所以 pt-upgrade 會將其歸類為 SQL errors 。

SQL 4

create table employees.t1(id int primary key,c1 text not null default (''));

從 MySQL 8.0.13 開始，允許對 BLOB，TEXT，GEOMETRY 和 JSON 欄位設定預設值。之前版本，則不允許。

SQL 1

select "a word a" REGEXP "[[:<:]]word[[:>:]]";

在 MySQL 8.0 中，正規表示式底層庫由 Henry Spencer 調整為了 International Components for Unicode (ICU)。

在 Henry Spencer 庫中，[[:<:]]，[[:>:]] 用來表示一個單詞的開頭和結尾。但在 ICU 庫中，則不能，類似功能要通過 \b 來實現。所以，對於上面這個 SQL ，在 MySQL 8.0 中的寫法如下。

select "a word a" REGEXP "\\bword\\b";

SQL 2

select dept_no,count(*) from employees.dept_emp group by dept_no desc;

在 MySQL 8.0 之前，如果我們要對分組後的結果進行排序，可使用 GROUP BY col_name ASC/DESC ，沒有指定排序列，預設是對分組列進行排序。

在 MySQL 8.0 中，不再支援這一語法，如果要進行排序，需顯式指定排序列。所以，對於上面這個 SQL，在 MySQL 8.0 中的寫法如下。

select dept_no,count(*) from employees.dept_emp group by dept_no order by dept_no desc;

常用引數

--[no]read-only

預設情況下，pt-upgrade 只會執行 SELECT 和 SET 操作。如果要執行其它操作，必須指定 --no-read-only。


--[no]create-upgrade-table，--upgrade-table

預設情況下，pt-upgrade 會在目標例項上建立一張 percona_schema.pt_upgrade 表（由 --upgrade-table 引數指定），每執行完一個 SQL，都會執行一次 SELECT * FROM percona_schema.pt_upgrade LIMIT 1 以清除上一個 SQL 有可能出現的 warning 。


--max-class-size，--max-examples

pt-upgrade 會對 SQL 進行分類，這兩個引數可用來限制同一類 SQL 輸出的數量。其中，--max-class-size 用來限制不重複 SQL 的數量，預設是 1000。--max-examples 用來限制 SQL 的數量，包括重複 SQL，預設是 3。

pt-upgrade 基於什麼對 SQL 進行分類呢？fingerprint。

fingerprint 這個術語，我們在很多工具中都會看到，如 ProxySQL，pt-query-digest，可理解為基於某些規則，提取 SQL 的一般形式，類似於 JDBC 中的 PreparedStatement 。

譬如下面這幾條 SQL，就可歸為同一類 select c? from d?t? where id=?

select c1 from db1.t1 where id=1;
select c1 from db1.t1 where id=1;
select c1 from db1.t1 where id=2;
select c2 from db1.t1 where id=3;
select c3 from db1.t2 where id=4;
select c4 from db2.t3 where id=5;
select c5 from db2.t4 where id=6;

Percona Toolkit 中的提取規則如下：

  1. 將數字替換為佔位符 (?) 。

  2. 刪除註釋。

  3. 將 IN() 和 VALUES() 中的多個值合併為一個佔位符。

  4. 將多個空格合併為一個空格。

  5. 查詢小寫。

  6. 將多個相同的 UNION 查詢合併為一個。


--save-results

將查詢結果儲存到目錄中。

# pt-upgrade h=127.0.0.1,P=3307,u=pt_user,p=pt_pass --save-results /tmp/pt_upgrade_result --type rawlog /tmp/pt_upgrade_test.sql --no-read-only 
# pt-upgrade /tmp/pt_upgrade_result/ h=127.0.0.1,P=3306,u=pt_user,p=pt_pass

使用 pt-upgrade 時的注意事項

在執行 pt-upgrade 之前，必須確保兩個例項中的資料完全一致，且不會發生變更，否則會產生誤判。

基於此，pt-upgrade 更適合在測試環境或開發環境使用，不建議在生產環境上使用。

MySQL 5.7 升級 MySQL 8.0 的注意事項

MySQL 5.7 升級到 MySQL 8.0，目前已知的，需要注意的點主要有以下兩個：

一、不再支援 GROUP BY col_name ASC/DESC。如果要排序，需顯式指定排序列。

二、MySQL 8.0 的正規表示式底層庫由 Henry Spencer 調整為了 International Components for Unicode (ICU)，Spencer 庫的部分語法不再支援。具體來說：

1. Spencer 庫是以位元組方式工作的，不是多位元組安全的，在碰到多位元組字元時有可能不會得到預期效果。而 ICU 支援完整的 Unicode 並且是多位元組安全的。

mysql 5.7> select 'č' regexp '^.$';
+-------------------+
| 'č' regexp '^.$'  |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql 8.0> select 'č' regexp '^.$';
+-------------------+
| 'č' regexp '^.$'  |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

2. 在 Spencer 庫中，.可用來匹配任何字元，包括回車符（\r）和換行符（\n）。而在 ICU 中，. 預設不會匹配回車符和換行符。如果要匹配，需指定正則修飾符 n。

mysql 5.7> select 'new\nline' regexp 'new.line';
+-------------------------------+
| 'new\nline' regexp 'new.line' |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

mysql 8.0> select 'new\nline' regexp 'new.line';
+-------------------------------+
| 'new\nline' regexp 'new.line' |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

mysql 8.0> select regexp_like('new\nline','new.line','n');
+-----------------------------------------+
| regexp_like('new\nline','new.line','n') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

3. Spencer 庫支援通過 [[:<:]] 和 [[:>:]] 來表示一個單詞的開頭和結尾。 類似的功能，ICU 中需通過 \b 來實現。

mysql 5.7> select 'a word a' regexp '[[:<:]]word[[:>:]]';
+----------------------------------------+
| 'a word a' regexp '[[:<:]]word[[:>:]]' |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql 8.0> select 'a word a' regexp '[[:<:]]word[[:>:]]';
ERROR 3685 (HY000): Illegal argument to a regular expression.

mysql 8.0> select 'a word a' regexp '\\bword\\b';
+--------------------------------+
| 'a word a' regexp '\\bword\\b' |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

4. Spencer 庫支援 [.characters.]，這裡的 characters 既可以是字元，又可以是字元名稱，譬如字元 : 對應的字元名稱是 colon 。  ICU 中不支援字元名稱。

mysql 5.7> select ':' regexp '[[.:.]]';
+----------------------+
| ':' regexp '[[.:.]]' |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql 5.7> select ':' regexp '[[.colon.]]';
+--------------------------+
| ':' regexp '[[.colon.]]' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.01 sec)

mysql 8.0> select ':' regexp '[[.:.]]';
+----------------------+
| ':' regexp '[[.:.]]' |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql 8.0> select ':' regexp '[[.colon.]]';
+--------------------------+
| ':' regexp '[[.colon.]]' |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

5. ICU 中如果要匹配右括號 ) ，需使用轉義符。

mysql 5.7> select ')' regexp (')');
+------------------+
| ')' regexp (')') |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql 8.0> select ')' regexp (')');
ERROR 3691 (HY000): Mismatched parenthesis in regular expression.

mysql 8.0> select ')' regexp ('\\)');
+--------------------+
| ')' regexp ('\\)') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

總結

相信有了 pt-upgrade 的加持，後續我們再進行資料庫升級時心裡會有底很多。

MySQL 8.0 雖然引入了很多新特性，但升級時需要注意的點其實也不多。

除了上面提到的兩點，後續如果發現了其它需要注意的點，也會及時更新到留言中，歡迎大家持續關注～

除了 pt-upgrade，另外一個推薦的資料庫升級工具是 MySQL Shell 中的 util.checkForServerUpgrade()。

與 pt-upgrade 不一樣的是，util.checkForServerUpgrade() 更多的是從例項的基礎資料本身來判定例項是否滿足升級條件，譬如是否使用了移除的函式、表名是否存在衝突等，一共有 21 個檢查項，這個工具我們後面也會介紹，敬請期待。

參考

[1] pt-upgrade

[2] Regular expression problems

[3] WL#353: Better REGEXP package

[4] Regular Expression Compatibility Considerations

[5] MySQL 5.7 Regular Expressions

相關文章