数据库升级是一项苦乐参半的工程。好消息是，通过升级，你可以享受到新版本带来的新功能和性能提升。令人担忧的是，新版本可能与旧版本不兼容，这主要体现在以下三个方面：
[En]
Database upgrade is a bittersweet project. The good news is that through the upgrade, you can enjoy the new features and performance improvements brought by the new version. The worry is that the new version may not be compatible with the old version, which is mainly reflected in the following three aspects:
因此，在升级在线数据库之前，通常会在测试环境中进行大量测试，包括功能测试和性能测试。
[En]
Therefore, before upgrading the online database, a large number of tests are generally carried out in the test environment, including functional testing and performance testing.
很多人可能会觉得麻烦，所以对升级抱着一种不主动、也拒绝的态度，新版本怎么会有更好的性能、更多的新功能，而老版本在产品维护周期结束后也存在安全隐患。
[En]
Many people may find it troublesome, so they take a “do not take the initiative, also refuse” attitude towards the upgrade, how can the new version have better performance and more new features, and the old version also has security risks after the end of the product maintenance cycle.
升还是不升呢？that is a question。
下面我们介绍一个 MySQL 升级利器，可极大减轻 DBA 包括开发童鞋在升级数据库时的心智负担和工作负担。
这个利器就是 pt-upgrade。
pt-upgrade 是 Percona Toolkit 中的一个工具，可帮忙我们从业务 SQL 层面检查新老版本的兼容性。
它的检测思路很简单，给定一个 SQL，分别在两个不同版本的实例上执行，看看是否一致。
具体地说，它检查以下内容：
[En]
Specifically, it examines the following:
- Row count：查询返回的行数是否一致。
- Row data：查询的结果是否一致。
- Warnings：是否提示 warning。正常来说，要么都提示 warning，要么都不提示 warning。
- Query time：查询时间是否在同一个量级，或者新版本的执行时间是否更短。
- Query errors：查询如果在一个实例中出现语法错误，会提示 Query errors。
- SQL errors：查询如果在两个实例中同时出现语法错误，会提示 SQL errors。
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<br># pt-upgrade host1_results1/ h=host2
第二种情况适用于无法同时访问两个实例，或者需要根据基准测试结果进行多个测试的情况。
[En]
The second usage applies when two instances cannot be accessed at the same time, or multiple tests are required based on a benchmark result.
看下面这个 Demo。
pt_upgrade_test.sql 包含了若干条测试语句。
# cat /tmp/pt_upgrade_test.sql<br>select "a word a" REGEXP "[[:<:]]word[[:>:]]";<br>select dept_no,count(*) from employees.dept_emp group by dept_no desc;<br>grant select on employees.* to 'u1'@'%' identified by '123456';<br>create table employees.t1(id int primary key,c1 text not null default ('')); <br>select * from employees.dept_emp group by dept_no;<br></:]]word[[:>
这里给出的几条测试语句都极具代表性，都是升级过程中需要注意的 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<br><br>#-----------------------------------------------------------------------<br># Logs<br>#-----------------------------------------------------------------------<br><br>File: /tmp/pt_upgrade_test.sql<br>Size: 311<br><br>#-----------------------------------------------------------------------<br># Hosts<br>#-----------------------------------------------------------------------<br><br>host1:<br><br>  DSN:       h=127.0.0.1,P=3307<br>  hostname:  slowtech<br>  MySQL:     MySQL Community Server (GPL) 5.7.36<br><br>host2:<br><br>  DSN:       h=127.0.0.1,P=3306<br>  hostname:  slowtech<br>  MySQL:     MySQL Community Server - GPL 8.0.27<br><br>########################################################################<br># Query class 00A13DD81BF65D41<br>########################################################################<br><br>Reporting class because it has diffs, but hasn't been reported yet.<br><br>Total queries      1<br>Unique queries     1<br>Discarded queries  0<br><br>grant select on employees.* to ?@? identified by ?;<br><br>##<br>## Query errors diffs: 1<br>##<br><br>-- 1.<br><br>No error<br><br>vs.<br><br>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';"]<br><br>grant select on employees.* to 'u1'@'%' identified by '123456';<br><br>########################################################################<br># Query class 296E46FE3AEE9B6C<br>########################################################################<br><br>Reporting class because it has SQL errors, but hasn't been reported yet.<br><br>Total queries      1<br>Unique queries     1<br>Discarded queries  0<br><br>select * from employees.dept_emp group by dept_no;<br><br>##<br>## SQL errors: 1<br>##<br><br>-- 1.<br><br>On both hosts:<br><br>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;"]<br><br>select * from employees.dept_emp group by dept_no;<br><br>########################################################################<br># Query class 8B81ACF1E68DE066<br>########################################################################<br><br>Reporting class because it has diffs, but hasn't been reported yet.<br><br>Total queries      1<br>Unique queries     1<br>Discarded queries  0<br><br>create table employees.t?(id int primary key,c? text not ? default (?));<br><br>##<br>## Query errors diffs: 1<br>##<br><br>-- 1.<br><br>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 ('')); "]<br><br>vs.<br><br>No error<br><br>create table employees.t1(id int primary key,c1 text not null default (''));<br><br>########################################################################<br># Query class 92E8E91AB47593A5<br>########################################################################<br><br>Reporting class because it has diffs, but hasn't been reported yet.<br><br>Total queries      1<br>Unique queries     1<br>Discarded queries  0<br><br>select ? regexp ?;<br><br>##<br>## Query errors diffs: 1<br>##<br><br>-- 1.<br><br>No error<br><br>vs.<br><br>DBD::mysql::st execute failed: Illegal argument to a regular expression. [for Statement "select "a word a" REGEXP "[[:<:]]word[[:>:]]";"]<br><br>select "a word a" REGEXP "[[:<:]]word[[:>:]]";<br><br>########################################################################<br># Query class D3F390B1B46CF9EA<br>########################################################################<br><br>Reporting class because it has diffs, but hasn't been reported yet.<br><br>Total queries      1<br>Unique queries     1<br>Discarded queries  0<br><br>select dept_no,count(*) from employees.dept_emp group by dept_no desc;<br><br>##<br>## Query errors diffs: 1<br>##<br><br>-- 1.<br><br>No error<br><br>vs.<br><br>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;"]<br><br>select dept_no,count(*) from employees.dept_emp group by dept_no desc;<br><br>#-----------------------------------------------------------------------<br># Stats<br>#-----------------------------------------------------------------------<br><br>failed_queries        1<br>not_select            0<br>queries_filtered      0<br>queries_no_diffs      0<br>queries_read          5<br>queries_with_diffs    0<br>queries_with_errors   4<br></:]]word[[:></:]]word[[:>
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';<br>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[[:>:]]";<br></:]]word[[:>
在 MySQL 8.0 中，正则表达式底层库由 Henry Spencer 调整为了 International Components for Unicode (ICU)。
在 Henry Spencer 库中，[[:
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;<br>select c1 from db1.t1 where id=1;<br>select c1 from db1.t1 where id=2;<br>select c2 from db1.t1 where id=3;<br>select c3 from db1.t2 where id=4;<br>select c4 from db2.t3 where id=5;<br>select c5 from db2.t4 where id=6;
Percona Toolkit 中的提取规则如下：
–save-results
将查询结果保存到目录。
[En]
Save the query results to the directory.
# 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 <br># pt-upgrade /tmp/pt_upgrade_result/ h=127.0.0.1,P=3306,u=pt_user,p=pt_pass
在执行 pt-upgrade 之前，必须确保两个实例中的数据完全一致，且不会发生变更，否则会产生误判。
基于此，pt-upgrade 更适合在测试环境或开发环境使用，不建议在生产环境上使用。
MySQL 5.7 升级到 MySQL 8.0，目前已知的，需要注意的点主要有以下两个：
一、不再支持 GROUP BY col_name ASC/DESC。如果要排序，需显式指定排序列。
二、MySQL 8.0 的正则表达式底层库由 Henry Spencer 调整为了 International Components for Unicode (ICU)，Spencer 库的部分语法不再支持。具体来说：
- Spencer 库是以字节方式工作的，不是多字节安全的，在碰到多字节字符时有可能不会得到预期效果。而 ICU 支持完整的 Unicode 并且是多字节安全的。
mysql 5.7> select 'č' regexp '^.$';<br>+-------------------+<br>| 'č' regexp '^.$'  |<br>+-------------------+<br>|                 0 |<br>+-------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select 'č' regexp '^.$';<br>+-------------------+<br>| 'č' regexp '^.$'  |<br>+-------------------+<br>|                 1 |<br>+-------------------+<br>1 row in set (0.00 sec)
- 在 Spencer 库中，
.可用来匹配任何字符，包括回车符（\r）和换行符（\n）。而在 ICU 中，.默认不会匹配回车符和换行符。如果要匹配，需指定正则修饰符n。
mysql 5.7> select 'new\nline' regexp 'new.line';<br>+-------------------------------+<br>| 'new\nline' regexp 'new.line' |<br>+-------------------------------+<br>|                             1 |<br>+-------------------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select 'new\nline' regexp 'new.line';<br>+-------------------------------+<br>| 'new\nline' regexp 'new.line' |<br>+-------------------------------+<br>|                             0 |<br>+-------------------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select regexp_like('new\nline','new.line','n');<br>+-----------------------------------------+<br>| regexp_like('new\nline','new.line','n') |<br>+-----------------------------------------+<br>|                                       1 |<br>+-----------------------------------------+<br>1 row in set (0.00 sec)
- Spencer 库支持通过 [[:
mysql 5.7> select 'a word a' regexp '[[:<:]]word[[:>:]]';<br>+----------------------------------------+<br>| 'a word a' regexp '[[:<:]]word[[:>:]]' |<br>+----------------------------------------+<br>|                                      1 |<br>+----------------------------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select 'a word a' regexp '[[:<:]]word[[:>:]]';<br>ERROR 3685 (HY000): Illegal argument to a regular expression.<br><br>mysql 8.0> select 'a word a' regexp '\\bword\\b';<br>+--------------------------------+<br>| 'a word a' regexp '\\bword\\b' |<br>+--------------------------------+<br>|                              1 |<br>+--------------------------------+<br>1 row in set (0.00 sec)<br></:]]word[[:></:]]word[[:></:]]word[[:>
- Spencer 库支持 [.characters.]，这里的 characters 既可以是字符，又可以是字符名称，譬如字符
:对应的字符名称是colon。 ICU 中不支持字符名称。
mysql 5.7> select ':' regexp '[[.:.]]';<br>+----------------------+<br>| ':' regexp '[[.:.]]' |<br>+----------------------+<br>|                    1 |<br>+----------------------+<br>1 row in set (0.00 sec)<br><br>mysql 5.7> select ':' regexp '[[.colon.]]';<br>+--------------------------+<br>| ':' regexp '[[.colon.]]' |<br>+--------------------------+<br>|                        1 |<br>+--------------------------+<br>1 row in set (0.01 sec)<br><br>mysql 8.0> select ':' regexp '[[.:.]]';<br>+----------------------+<br>| ':' regexp '[[.:.]]' |<br>+----------------------+<br>|                    1 |<br>+----------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select ':' regexp '[[.colon.]]';<br>+--------------------------+<br>| ':' regexp '[[.colon.]]' |<br>+--------------------------+<br>|                        0 |<br>+--------------------------+<br>1 row in set (0.00 sec)
- ICU 中如果要匹配右括号
)，需使用转义符。
mysql 5.7> select ')' regexp (')');<br>+------------------+<br>| ')' regexp (')') |<br>+------------------+<br>|                1 |<br>+------------------+<br>1 row in set (0.00 sec)<br><br>mysql 8.0> select ')' regexp (')');<br>ERROR 3691 (HY000): Mismatched parenthesis in regular expression.<br><br>mysql 8.0> select ')' regexp ('\\)');<br>+--------------------+<br>| ')' regexp ('\\)') |<br>+--------------------+<br>|                  1 |<br>+--------------------+<br>1 row in set (0.00 sec)
相信有了 pt-upgrade 的加持，后续我们再进行数据库升级时心里会有底很多。
MySQL 8.0 虽然引入了很多新特性，但升级时需要注意的点其实也不多。
除了上面提到的两点外，如果发现其他需要注意的地方，也会及时更新到留言中。欢迎您继续关注我们。
[En]
In addition to the two points mentioned above, if you find other points that need to be paid attention to later, they will also be updated to the message in time. You are welcome to follow us continuously.
除了 pt-upgrade，另外一个推荐的数据库升级工具是 MySQL Shell 中的 util.checkForServerUpgrade()。
与 pt-upgrade 不一样的是，util.checkForServerUpgrade() 更多的是从实例的基础数据本身来判定实例是否满足升级条件，譬如是否使用了移除的函数、表名是否存在冲突等，一共有 21 个检查项，这个工具我们后面也会介绍，敬请期待。
Original: https://www.cnblogs.com/ivictor/p/16275919.html
Author: iVictor
Title: 不可不知的 MySQL 升级利器及 5.7 升级到 8.0 的注意事项
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505377/
转载文章受原作者版权保护。转载请注明原作者出处！