7

不可不知的 MySQL 升级利器及 5.7 升级到 8.0 的注意事项 - iVictor

 1 year ago
source link: https://www.cnblogs.com/ivictor/p/16275919.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

不可不知的 MySQL 升级利器及 5.7 升级到 8.0 的注意事项

数据库升级，是一项让人喜忧参半的工程。喜的是，通过升级，可以享受新版本带来的新特性及性能提升。忧的是，新版本可能与老的版本不兼容，不兼容主要体现在以下三方面：

  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。

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


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK