MySQL8.0变更影响业务侧的检查调整

请注意此文档非实践内容 仅供参考

JDBC驱动连接器

权威官档说明文档连接地址

https://dev.mysql.com/doc/refman/8.0/en/connectors-apis.html

说明：强烈建议将 MySQL Connector/J 8.0 与 MySQL Server 8.0、5.7 和 5.6 一起使用。请升级到 MySQL Connector/J 8.0。

Connector/J 版本，以及它们所需的 MySQL 和 Java 版本

 

连接器/J 版	JDBC 版本	MySQL 服务器版本	需要 JRE	编译所需的 JDK	地位
5.1	3.0、4.0、4.1、4.2	5.6 1 , 5.7 1 , 8.0 1	JRE 5 或更高1	JDK 5.0 和 JDK 8.0 或更高版本2、3	一般可用性
8.0	4.2	5.6、5.7、8.0	JRE 8 或更高	JDK 8.0 或更高版本2	一般可用性。推荐版本。

一、检查所有库innodb_read_only状态 并根据实际情况开启或关闭
二、升级8.0之后统计信息从缓存表获取，如需要始终直接从存储引擎检索最新统计信息，需设置 information_schema_stats_expiry 为0.要随时更新给定表的缓存值，请使用ANALYZE TABLE xxx
三、INFORMATION_SCHEMA 查询结果的行顺序可能与之前的结果不同,找出INFORMATION_SCHEMA排序ASC、DESC、GROUP BY相关的SQL,如有影响请包含一个ORDER BY子句来纠正
四、对INFORMATION_SCHEMA表的查询可能会以与早期 MySQL 系列不同的字母大小写返回列名。应用程序应该以不区分大小写的方式测试结果集列名。
找出对大小写敏感的SQL并以别名调整避免大小写问题
五、create .. like ..建表规则变化 CREATE TABLE dst_tbl LIKE src_tbl要求它 src_tbl是一个基表，如果它是一个INFORMATION_SCHEMA 表是数据字典表的视图，则失败
找出以数据字典建表的SQL 避免此写法
六、MySQL8.0将使用cache_sha2_password 作为首选身份验证插件，提前检查更新最新支持MySQL8.0的连接器,
caching_sha2_password-兼容的客户端和连接器
MySQL 8.0（8.0.4 或更高版本）中的libmysqlclient客户端库。
MySQL 5.7（5.7.23 或更高版本）中的libmysqlclient客户端库。
MySQL 连接器/C++ 1.1.11 或更高版本或 8.0.7 或更高版本。
MySQL 连接器/J 8.0.9 或更高版本。
MySQL Connector/NET 8.0.10 或更高版本（通过经典 MySQL 协议）。
MySQL 连接器/Node.js 8.0.9 或更高版本。
PHP：X DevAPI PHP 扩展 (mysql_xdevapi) 支持 caching_sha2_password.
PHP：PDO_MySQL 和 ext/mysqli 扩展不支持 caching_sha2_password. 另外，与7.1.16之前的PHP版本和7.2.4之前的PHP 7.2一起使用时， default_authentication_plugin=caching_sha2_password 即使caching_sha2_password不使用也无法连接。
七、cache_sha2_password兼容性解决办法
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
八、MySQL 存储引擎现在负责提供自己的分区处理程序，并且 MySQL 服务器不再提供通用分区支持。
InnoDB并且 NDB是唯一提供 MySQL 8.0 支持的本机分区处理程序的存储引擎。必须 在升级服务器之前InnoDB更改使用任何其他存储引擎的分区表 - 将其转换为或NDB，或删除其分区 - 否则之后无法使用。
将MyISAM 表转换为 InnoDB 基本语法
ALTER TABLE table_name ENGINE=InnoDB;
找出其他分区表转换为InnoDB
九、升级8.0后默认字符集已从 更改 latin1为utf8mb4；请提前做好防范
十、lower_case_table_names
如果设置为 0，则按指定存储表名，并且比较区分大小写。如果设置为 1，则表名以小写形式存储在磁盘上，并且比较不区分大小写。如果设置为 2，则表名按给定方式存储，但以小写形式进行比较。
十一、弃用 GRANT语句修改用户帐户的非特权特性、 NO_AUTO_CREATE_USERSQL 模式、 PASSWORD()函数和 old_passwords系统变量
十二、8.0中已添加和删除的功能必知必看（内容过多请参考官方连接）
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
十三、重命名的 InnoDB 信息模式视图
旧名称 新名字
INNODB_SYS_COLUMNS INNODB_COLUMNS
INNODB_SYS_DATAFILES INNODB_DATAFILES
INNODB_SYS_FIELDS INNODB_FIELDS
INNODB_SYS_FOREIGN INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES INNODB_INDEXES
INNODB_SYS_TABLES INNODB_TABLES
INNODB_SYS_TABLESPACES INNODB_TABLESPACES
INNODB_SYS_TABLESTATS INNODB_TABLESTATS
INNODB_SYS_VIRTUAL INNODB_VIRTUAL
十四、从 MySQL 8.0.17 开始，该 CREATE TABLESPACE ... ADD DATAFILE子句不允许循环目录引用。例如，/../以下语句中的循环目录引用 ( ) 是不允许的：
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';
十五、InnoDB 信息模式视图名称变更 找出引用以下相关的业务 提前做好变更防范
旧名称 新名字
INNODB_SYS_COLUMNS INNODB_COLUMNS
INNODB_SYS_DATAFILES INNODB_DATAFILES
INNODB_SYS_FIELDS INNODB_FIELDS
INNODB_SYS_FOREIGN INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES INNODB_INDEXES
INNODB_SYS_TABLES INNODB_TABLES
INNODB_SYS_TABLESPACES INNODB_TABLESPACES
INNODB_SYS_TABLESTATS INNODB_TABLESTATS
INNODB_SYS_VIRTUAL INNODB_VIRTUAL
✳升级到 MySQL 8.0.3 或更高版本后，更新任何引用先前InnoDB INFORMATION_SCHEMA视图名称的脚本。
十六、zlib库版本从版本 1.2.3 提升到版本 1.2.11
找出引用了zlibcompressBound()函数的返回测试
zlib 1.2.11 中的 zlibcompressBound()函数返回的对压缩给定字节长度所需的缓冲区大小的估计值比 zlib 1.2.3 版中的稍高。该函数由确定创建压缩表或在压缩表中插入和更新行时允许的最大行大小的compressBound() 函数调用 。因此， 在 早期版本中成功的行大小非常​​接近最大行大小的操作现在可能会失败。
十七、禁止循环目录引用 例如：CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';
查询 INFORMATION_SCHEMA.INNODB_DATAFILES 表修改为绝对值
十八、FOREIGN KEY 定义需要指定关键字

1.1. MySQL升级前检查

1.1.1. 安装mysql-shell

上传mysql-shell安装包，并安装

#tar -xvf mysql-shell-8.0.27-linux-el7-x86-64bit.tar.gz

#cd /mysql/app/

#mv mysql-shell-8.0.27-linux-el7-x86-64bit/ mysql_shell

#echo "PATH=/mysql/app/mysql_shell/bin:\$PATH" >> /etc/profile

#mysqlsh –version

 

1.1.2. MySQLSH检查MySQL升级是否有异常提示

#mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"

 

 

1.1.3. 过时的函数等检查

mysqlcheck -u root -p --all-databases --check-upgrade

 

1.1.4. 分区表检查

不能有使用不支持本机分区的存储引擎的分区表。

 

ALTER TABLE table_name ENGINE = INNODB;

ALTER TABLE table_name REMOVE PARTITIONING;

 

1.1.5. 外键检查

不能有外键约束名称超过 64 个字符的表

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE

TABLE_NAME IN (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),

INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1) FROM

INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE

LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

 

1.1.6. 其他检查项参考

https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html