下载安卓APP箭头
箭头给我发消息

客服QQ:3315713922

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

作者:匿名     来源: 数据库点击数:1483发布时间: 2022-06-20 21:59:18

标签: MySQL数据库升级

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

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

  语法不兼容。

  语义不兼容。同一个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 在两个实例中的执行效果。​

  复制

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

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

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

  复制

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

  2. # pt-upgrade host1_results1/ h=host2

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

  三、Demo

  看下面这个 Demo。

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

  复制

  1. # cat /tmp/pt_upgrade_test.sql

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

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

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

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

  6. select * from employees.dept_emp group by dept_no;

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

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

  复制

  1. # 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

      2.

  3. #-----------------------------------------------------------------------

  4. # Logs

  5. #-----------------------------------------------------------------------

      6.

  7. File: /tmp/pt_upgrade_test.sql

  8. Size: 311

      9.

  10. #-----------------------------------------------------------------------

  11. # Hosts

  12. #-----------------------------------------------------------------------

      13.

  14. host1:

      15.

  16. DSN: h=127.0.0.1,P=3307

  17. hostname: slowtech

  18. MySQL: MySQL Community Server (GPL) 5.7.36

      19.

  20. host2:

      21.

  22. DSN: h=127.0.0.1,P=3306

  23. hostname: slowtech

  24. MySQL: MySQL Community Server - GPL 8.0.27

      25. 

  26. ########################################################################

  27. # Query class 00A13DD81BF65D41

  28. ########################################################################

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

      30.

  31. Total queries 1

  32. Unique queries 1

  33. Discarded queries 0

      34.

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

      36.

  37. ##

  38. ## Query errors diffs: 1

  39. ##

      40.

  41. -- 1.

      42.

  43. No error

     44.

  45. vs.

      46.

  47. 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';"]

      48.

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

      50.

  51. ########################################################################

  52. # Query class 296E46FE3AEE9B6C

  53. ########################################################################

      54.

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

      56.

  57. Total queries 1

  58. Unique queries 1

  59. Discarded queries 0

      60.

  61. select * from employees.dept_emp group by dept_no;

      62.

  63. ##

  64. ## SQL errors: 1

  65. ##

      66.

  67. -- 1.

     68.

  69. On both hosts:

      70.

  71. 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;"]

      72. 

  73. select * from employees.dept_emp group by dept_no;

      74.

  75. ########################################################################

  76. # Query class 8B81ACF1E68DE066

  77. ########################################################################

      78.

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

      80.

  81. Total queries 1

  82. Unique queries 1

  83. Discarded queries 0

      84.

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

      86.

  87. ##

  88. ## Query errors diffs: 1

  89. ##

     90.

  91. -- 1.

      92.

  93. 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 ('')); "]

      94. 

  95. vs.

      96.

  97. No error

      98.

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

      100.

  101. ########################################################################

  102. # Query class 92E8E91AB47593A5

  103. ########################################################################

      104.

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

      106.

  107. Total queries 1

  108. Unique queries 1

  109. Discarded queries 0

      110.

  111. select ? regexp ?;

  112.

      113. ##

  114. ## Query errors diffs: 1

  115. ##

      116.

  117. -- 1.

      118.

  119. No error

      120.

  121. vs.

      122.

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

      124. 

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

      126.

  127. ########################################################################

  128. # Query class D3F390B1B46CF9EA

  129. ########################################################################

      130.

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

      132.

  133. Total queries 1

  134. Unique queries 1

  135. Discarded queries 0

      136.

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

      138.

  139. ##

  140. ## Query errors diffs: 1

  141. ##

      142. 

  143. -- 1.

      144.

  145. No error

      146.

  147. vs.

      148.

  149. 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;"]

      150.

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

      152.

  153. #-----------------------------------------------------------------------

  154. # Stats

  155. #-----------------------------------------------------------------------

      156.

  157. failed_queries 1

  158. not_select 0

  159. queries_filtered 0

  160. queries_no_diffs 0

  161. queries_read 5

  162. queries_with_diffs 0

  163. queries_with_errors 

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

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

  四、分析输出结果

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

  SQL 3

  复制

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

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

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

  复制

  1. create user 'u1'@'%' identified by '123456';

  2. grant select on employees.* to 'u1'@'%';

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

  SQL 5

  复制

  1. 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

  复制

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

  从 MySQL 8.0.13 开始,允许对 BLOB,TEXT,GEOMETRY 和 JSON 字段设置默认值。之前版本,则不允许。

  SQL 1

  复制

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

  复制

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

  复制

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

  五、常用参数

  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=?

  复制

  1. select c1 from db1.t1 where id=1;

  2. select c1 from db1.t1 where id=1;

  3. select c1 from db1.t1 where id=2;

  4. select c2 from db1.t1 where id=3;

  5. select c3 from db1.t2 where id=4;

  6. select c4 from db2.t3 where id=5;

  7. select c5 from db2.t4 where id=6;

  Percona Toolkit 中的提取规则如下:

  将数字替换为占位符 (?) 。

  删除注释。

  将 IN() 和 VALUES() 中的多个值合并为一个占位符。

  将多个空格合并为一个空格。

  查询小写。

  将多个相同的 UNION 查询合并为一个。

  4)--save-results

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

  复制

  1. # 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

  2. # 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 并且是多字节安全的。

  复制

  1.

      2. mysql 5.7> select 'č' regexp '^.$';

  3. +-------------------+

  4. | 'č' regexp '^.$' |

  5. +-------------------+

  6. | 0 |

  7. +-------------------+

  8. 1 row in set (0.00 sec)

      9.

  10. mysql 8.0> select 'č' regexp '^.$';

  11. +-------------------+

  12. | 'č' regexp '^.$' |

  13. +-------------------+

  14. | 1 |

  15. +-------------------+

  16. 1 row in set (0.00 sec)

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

  复制

      1.

  2. mysql 5.7> select 'new\\nline' regexp 'new.line';

  3. +-------------------------------+

  4. | 'new\\nline' regexp 'new.line' |

  5. +-------------------------------+

  6. | 1 |

  7. +-------------------------------+

  8. 1 row in set (0.00 sec)

      9.

  10. mysql 8.0> select 'new\\nline' regexp 'new.line';

  11. +-------------------------------+

  12. | 'new\\nline' regexp 'new.line' |

  13. +-------------------------------+

  14. | 0 |

  15. +-------------------------------+

  16. 1 row in set (0.00 sec)

      17. 

  18. mysql 8.0> select regexp_like('new\\nline','new.line','n');

  19. +-----------------------------------------+

  20. | regexp_like('new\\nline','new.line','n') |

  21. +-----------------------------------------+

  22. | 1 |

  23. +-----------------------------------------+

  24. 1 row in set (0.00 sec)

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

  复制

  1. mysql 5.7> select 'a word a' regexp '[[:<:]]word[[:>:]]';

  2. +----------------------------------------+

  3. | 'a word a' regexp '[[:<:]]word[[:>:]]' |

  4. +----------------------------------------+

  5. | 1 |

  6. +----------------------------------------+

  7. 1 row in set (0.00 sec)

      8.

  9. mysql 8.0> select 'a word a' regexp '[[:<:]]word[[:>:]]';

  10. ERROR 3685 (HY000): Illegal argument to a regular expression.

      11.

  12. mysql 8.0> select 'a word a' regexp '\\\\bword\\\\b';

  13. +--------------------------------+

  14. | 'a word a' regexp '\\\\bword\\\\b' |

  15. +--------------------------------+

  16. | 1 |

  17. +--------------------------------+

  18. 1 row in set (0.00 sec)

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

  复制

  1. mysql 5.7> select ':' regexp '[[.:.]]';

  2. +----------------------+

  3. | ':' regexp '[[.:.]]' |

  4. +----------------------+

  5. | 1 |

  6. +----------------------+

  7. 1 row in set (0.00 sec)

      8.

  9. mysql 5.7> select ':' regexp '[[.colon.]]';

  10. +--------------------------+

  11. | ':' regexp '[[.colon.]]' |

  12. +--------------------------+

  13. | 1 |

  14. +--------------------------+

  15. 1 row in set (0.01 sec)

      16.

  17. mysql 8.0> select ':' regexp '[[.:.]]';

  18. +----------------------+

  19. | ':' regexp '[[.:.]]' |

  20. +----------------------+

  21. | 1 |

  22. +----------------------+

  23. 1 row in set (0.00 sec)

      24.

  25. mysql 8.0> select ':' regexp '[[.colon.]]';

  26. +--------------------------+

  27. | ':' regexp '[[.colon.]]' |

  28. +--------------------------+

  29. | 0 |

  30. +--------------------------+

  31. 1 row in set (0.00 sec)

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

  复制

  1. mysql 5.7> select ')' regexp (')');

  2. +------------------+

  3. | ')' regexp (')') |

  4. +------------------+

  5. | 1 |

  6. +------------------+

  7. 1 row in set (0.00 sec)

      8.

  9. mysql 8.0> select ')' regexp (')');

  10. ERROR 3691 (HY000): Mismatched parenthesis in regular expression.

      11.

  12. mysql 8.0> select ')' regexp ('\\\\)');

  13. +--------------------+

  14. | ')' regexp ('\\\\)') |

  15. +--------------------+

  16. | 1 |

  17. +--------------------+

  18. 1 row in set (0.00 sec)

  八、总结

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

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

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

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

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

  来源: dbaplus社群

  >>>>>>点击进入数据库专题

赞(7)
踩(0)
分享到:
华为认证网络工程师 HCIE直播课视频教程