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

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

语法不兼容。
语义不兼容。同一个SQL，在新老版本执行结果不一致。
新版本的查询性能更差。

所以，在对线上数据库进行升级之前，一般都会在测试环境进行大量的测试，包括功能测试和性能测试。

很多人可能会觉得麻烦，于是对待升级就秉持着一种“不主动，也拒绝”的态度，怎奈何新版本性能更好，新特性更多，而且老版本在产品维护周期结束后，也存在安全风险。

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

下面我们介绍一个 MySQL 升级利器，可极大减轻 DBA 包括开发童鞋在升级数据库时的心智负担和工作负担。

这个利器就是 pt-upgrade。

pt-upgrade 是 Percona Toolkit 中的一个工具，可帮忙我们从业务 SQL 层面检查新老版本的兼容性。

一、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 （实例连接信息）和文件名。

常见用法有以下两种：

直接比较一个文件中的 SQL 在两个实例中的执行效果。

# pt-upgradeh=host1h=host2slow.log

可通过 –type 指定文件的类型，支持 slowlog（慢日志），genlog（General Log），binlog（通过 mysqlbinlog 解析后的文本文件），rawlog（ SQL语句），tcpdump。不指定，则默认是慢日志。

先生成一个基准测试结果，然后再基于这个结果测试其它环境的兼容性。

# pt-upgradeh=host1--save-resultshost1_results/slow.log
# pt-upgradehost1_results1/h=host2

第二种用法适用于两个实例不能同时访问，或者需要基于一个基准测试结果进行多次测试。

三、Demo

看下面这个 Demo。

pt_upgrade_test.sql 包含了若干条测试语句。

# cat/tmp/pt_upgrade_test.sql
select"a word a"REGEXP"[[:<:]]word[[:>:]]";
selectdept_no,count(*) fromemployees.dept_empgroupbydept_nodesc;
grantselectonemployees.*to'u1'@'%'identifiedby'123456';
createtableemployees.t1(idintprimarykey,c1textnotnulldefault ('')); 
select*fromemployees.dept_empgroupbydept_no;

这里给出的几条测试语句都极具代表性，都是升级过程中需要注意的 SQL。

下面我们看看这些语句在 MySQL 5.7 和 MySQL 8.0 中的执行情况。

# pt-upgradeh=127.0.0.1,P=3307,u=pt_user,p=pt_passh=127.0.0.1,P=3306,u=pt_user,p=pt_pass--typerawlog/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:     MySQLCommunityServer (GPL) 5.7.36

host2:

DSN:       h=127.0.0.1,P=3306
hostname:  slowtech
MySQL:     MySQLCommunityServer-GPL8.0.27

########################################################################
# Queryclass00A13DD81BF65D41
########################################################################
Reportingclassbecauseithasdiffs, buthasn't been reported yet.

Totalqueries1
Uniquequeries1
Discardedqueries0

grantselectonemployees.*to?@?identifiedby?;

##
## Queryerrorsdiffs: 1
##

--1.

Noerror

vs.

DBD::mysql::stexecutefailed: YouhaveanerrorinyourSQLsyntax; checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'identified by '123456''atline1 [forStatement"grant select on employees.* to 'u1'@'%' identified by '123456';"]

grantselectonemployees.*to'u1'@'%'identifiedby'123456';

########################################################################
# Queryclass296E46FE3AEE9B6C
########################################################################

ReportingclassbecauseithasSQLerrors, buthasn't been reported yet.

Totalqueries1
Uniquequeries1
Discardedqueries0

select*fromemployees.dept_empgroupbydept_no;

##
## SQLerrors: 1
##

--1.

Onbothhosts:

DBD::mysql::stexecutefailed: Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn'employees.dept_emp.emp_no'whichisnotfunctionallydependentoncolumnsinGROUPBYclause; thisisincompatiblewithsql_mode=only_full_group_by [forStatement"select * from employees.dept_emp group by dept_no;"]

select*fromemployees.dept_empgroupbydept_no;

########################################################################
# Queryclass8B81ACF1E68DE066
########################################################################

Reportingclassbecauseithasdiffs, buthasn't been reported yet.

Totalqueries1
Uniquequeries1
Discardedqueries0

createtableemployees.t?(idintprimarykey,c?textnot?default (?));

##
## Queryerrorsdiffs: 1
##

--1.

DBD::mysql::stexecutefailed: YouhaveanerrorinyourSQLsyntax; checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'(''))'atline1 [forStatement"create table employees.t1(id int primary key,c1 text not null default ('')); "]

vs.

Noerror

createtableemployees.t1(idintprimarykey,c1textnotnulldefault (''));

########################################################################
# Queryclass92E8E91AB47593A5
########################################################################

Reportingclassbecauseithasdiffs, buthasn't been reported yet.

Totalqueries1
Uniquequeries1
Discardedqueries0

select?regexp?;

##
## Queryerrorsdiffs: 1
##

--1.

Noerror

vs.

DBD::mysql::stexecutefailed: Illegalargumenttoaregularexpression. [forStatement"select "aworda" REGEXP "[[:<:]]word[[:>:]]";"]

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

########################################################################
# QueryclassD3F390B1B46CF9EA
########################################################################

Reportingclassbecauseithasdiffs, buthasn't been reported yet.

Totalqueries1
Uniquequeries1
Discardedqueries0

selectdept_no,count(*) fromemployees.dept_empgroupbydept_nodesc;

##
## Queryerrorsdiffs: 1
##

--1.

Noerror

vs.

DBD::mysql::stexecutefailed: YouhaveanerrorinyourSQLsyntax; checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'desc'atline1 [forStatement"select dept_no,count(*) from employees.dept_emp group by dept_no desc;"]

selectdept_no,count(*) fromemployees.dept_empgroupbydept_nodesc;

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

failed_queries1
not_select0
queries_filtered0
queries_no_diffs0
queries_read5
queries_with_diffs0
queries_with_errors4

3307，3306 端口分别对应 MySQL 5.7、MySQL 8.0 实例。

对于文件中的每一个 SQL ，都会在这两个实例中执行。如果每个差异 SQL 的结果都打印出来的话，最后的输出将十分庞杂。为了简化最后的输出结果，pt-upgrade 会对 SQL 进行分类，同一类 SQL 的输出次数受到 –max-class-size 和 –max-examples 的限制。

四、分析输出结果

结合执行的 SQL，我们分析下输出结果。

SQL 3

grantselectonemployees.*to'u1'@'%'identifiedby'123456';

在 MySQL 8.0 之前，对一个用户进行授权（grant）操作，如果该用户不存在，会隐式创建。而在 MySQL 8.0 中，该命令会直接报错，必须先创建用户，再授权。

所以，上面这条 SQL 需拆分为以下两条 SQL 来执行。

createuser'u1'@'%'identifiedby'123456';
grantselectonemployees.*to'u1'@'%';

这个查询只在一个实例中出现语法错误，所以 pt-upgrade 会将其归类为 Query errors 。

SQL 5

select*fromemployees.dept_empgroupbydept_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

createtableemployees.t1(idintprimarykey,c1textnotnulldefault (''));

从 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 中的写法如下。

selectdept_no,count(*) fromemployees.dept_empgroupbydept_noorderbydept_nodesc;

五、常用参数

1）–[no]read-only

默认情况下，pt-upgrade 只会执行 SELECT 和 SET 操作。如果要执行其它操作，必须指定 –no-read-only。

2）–[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 。

3）–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=?

selectc1fromdb1.t1whereid=1;
selectc1fromdb1.t1whereid=1;
selectc1fromdb1.t1whereid=2;
selectc2fromdb1.t1whereid=3;
selectc3fromdb1.t2whereid=4;
selectc4fromdb2.t3whereid=5;
selectc5fromdb2.t4whereid=6;

Percona Toolkit 中的提取规则如下：

将数字替换为占位符 (?) 。
删除注释。
将 IN() 和 VALUES() 中的多个值合并为一个占位符。
将多个空格合并为一个空格。
查询小写。
将多个相同的 UNION 查询合并为一个。

4）–save-results

将查询结果保存到目录中。

# pt-upgradeh=127.0.0.1,P=3307,u=pt_user,p=pt_pass--save-results/tmp/pt_upgrade_result--typerawlog/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，目前已知的，需要注意的点主要有以下两个：

1）不再支持 GROUP BY col_name ASC/DESC。如果要排序，需显式指定排序列。

2）MySQL 8.0 的正则表达式底层库由 Henry Spencer 调整为了 International Components for Unicode (ICU)，Spencer 库的部分语法不再支持。具体来说：

Spencer 库是以字节方式工作的，不是多字节安全的，在碰到多字节字符时有可能不会得到预期效果。而 ICU 支持完整的 Unicode 并且是多字节安全的。


mysql5.7>select'č'regexp'^.$';
+-------------------+
|'č'regexp'^.$'|
+-------------------+
|0|
+-------------------+
1rowinset (0.00sec)

mysql8.0>select'č'regexp'^.$';
+-------------------+
|'č'regexp'^.$'|
+-------------------+
|1|
+-------------------+
1rowinset (0.00sec)

在 Spencer 库中，.可用来匹配任何字符，包括回车符（\r）和换行符（\n）。而在 ICU 中，. 默认不会匹配回车符和换行符。如果要匹配，需指定正则修饰符 n。


mysql5.7>select'new\nline'regexp'new.line';
+-------------------------------+
|'new\nline'regexp'new.line'|
+-------------------------------+
|1|
+-------------------------------+
1rowinset (0.00sec)

mysql8.0>select'new\nline'regexp'new.line';
+-------------------------------+
|'new\nline'regexp'new.line'|
+-------------------------------+
|0|
+-------------------------------+
1rowinset (0.00sec)

mysql8.0>selectregexp_like('new\nline','new.line','n');
+-----------------------------------------+
|regexp_like('new\nline','new.line','n') |
+-----------------------------------------+
|1|
+-----------------------------------------+
1rowinset (0.00sec)

Spencer 库支持通过 [[:<:]] 和 [[:>:]] 来表示一个单词的开头和结尾。类似的功能，ICU 中需通过 \b 来实现。

mysql5.7>select'a word a'regexp'[[:<:]]word[[:>:]]';
+----------------------------------------+
|'a word a'regexp'[[:<:]]word[[:>:]]'|
+----------------------------------------+
|1|
+----------------------------------------+
1rowinset (0.00sec)

mysql8.0>select'a word a'regexp'[[:<:]]word[[:>:]]';
ERROR3685 (HY000): Illegalargumenttoaregularexpression.

mysql8.0>select'a word a'regexp'\\bword\\b';
+--------------------------------+
|'a word a'regexp'\\bword\\b'|
+--------------------------------+
|1|
+--------------------------------+
1rowinset (0.00sec)

Spencer 库支持 [.characters.]，这里的 characters 既可以是字符，又可以是字符名称，譬如字符 : 对应的字符名称是 colon 。 ICU 中不支持字符名称。

mysql5.7>select':'regexp'[[.:.]]';
+----------------------+
|':'regexp'[[.:.]]'|
+----------------------+
|1|
+----------------------+
1rowinset (0.00sec)

mysql5.7>select':'regexp'[[.colon.]]';
+--------------------------+
|':'regexp'[[.colon.]]'|
+--------------------------+
|1|
+--------------------------+
1rowinset (0.01sec)

mysql8.0>select':'regexp'[[.:.]]';
+----------------------+
|':'regexp'[[.:.]]'|
+----------------------+
|1|
+----------------------+
1rowinset (0.00sec)

mysql8.0>select':'regexp'[[.colon.]]';
+--------------------------+
|':'regexp'[[.colon.]]'|
+--------------------------+
|0|
+--------------------------+
1rowinset (0.00sec)

ICU 中如果要匹配右括号 ) ，需使用转义符。

mysql5.7>select')'regexp (')');
+------------------+
|')'regexp (')') |
+------------------+
|1|
+------------------+
1rowinset (0.00sec)

mysql8.0>select')'regexp (')');
ERROR3691 (HY000): Mismatchedparenthesisinregularexpression.

mysql8.0>select')'regexp ('\\)');
+--------------------+
|')'regexp ('\\)') |
+--------------------+
|1|
+--------------------+
1rowinset (0.00sec)

八、总结

相信有了 pt-upgrade 的加持，后续我们再进行数据库升级时心里会有底很多。

MySQL 8.0 虽然引入了很多新特性，但升级时需要注意的点其实也不多。

除了上面提到的两点，后续如果发现了其它需要注意的点，也会及时更新到留言中，欢迎大家持续关注～

除了 pt-upgrade，另外一个推荐的数据库升级工具是 MySQL Shell 中的 util.checkForServerUpgrade()。

与 pt-upgrade 不一样的是，util.checkForServerUpgrade() 更多的是从实例的基础数据本身来判定实例是否满足升级条件，譬如是否使用了移除的函数、表名是否存在冲突等，一共有 21 个检查项。

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/260854.html<

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

一、pt-upgrade 的实现原理

二、pt-upgrade 的常见用法

三、Demo

四、分析输出结果

五、常用参数

1）–[no]read-only

2）–[no]create-upgrade-table，–upgrade-table

3）–max-class-size，–max-examples

4）–save-results

六、使用 pt-upgrade 时的注意事项

七、MySQL 5.7 升级 MySQL 8.0 的注意事项

八、总结

相关推荐

个人主题怎么制作？

社群营销管理关键是什么？

香港公司网站备案需要什么材料？

如何企业上云推广

PS设计搜索框的实用技巧有哪些？

发表回复