mysql 8 的坑早知道

本文收集了升级到 MySQL 8 之后遇到的各种新问题，以供大家参考。

[TOC]

场景1：登陆报错 Authentication plugin 'caching_sha2_password' reported error

故障上报

• 2021.12.23.登录 mysql 8.0.22 报如下错误：

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

确认过程

• 出现这个原因是 mysql 8 将默认的登录密码加密规则由 mysql_native_password 改为 caching_sha2_password
• 登入 mysql，确认登录异常的账号其 plugin 为 caching_sha2_password，而 plugin 为 mysql_native_password 的账号则登录正常

(root@localhost) [(none)]> select user,host,plugin from mysql.user;
+--------------------+-----------+-----------------------+
| user               | host      | plugin                |
+--------------------+-----------+-----------------------+
| dba_admin_readonly | %         | caching_sha2_password |
| root               | %         | mysql_native_password |
+--------------------+-----------+-----------------------+

解决方案

• 参考如下sql 将登录异常账号的 plugin 改为 mysql_native_password

alter user 'dba_admin_readonly'@'%' identified with mysql_native_password by 'xxx';

场景2：导入dump文件报错 ERROR 3554: Access to system table 'mysql.innodb_index_stats' is rejected.

故障上报

• 2022.03.16.在 mysql 5.5.68 上执行 mysqldump，并将dump文件导入 mysql 8.0.30，报如下错误：

[root@mysql-new]# mysql -udba_admin -p -S /tmp/mysql_3306.sock < /data/alldb.20220316.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3554 (HY000) at line 5145: Access to system table 'mysql.innodb_index_stats' is rejected.

解决方案

• 方案1：在mysqldump的时候添加 --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats，然后再导入

mysqldump -h 127.0.0.1 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -uroot -p --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > /data/alldb.20220316.sql

• 方案2：强烈建议在 mysqldump 的时候排除掉 mysql 库，否则可能还会遇到其他问题

场景3：导入dump文件报错 ERROR 1231: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

故障上报

• 2022.03.16.在 mysql 5.5.68 上执行 mysqldump，并将dump文件导入 mysql 8.0.30，报如下错误：

[root@mysql-new]# mysql -udba_admin -p -S /tmp/mysql_3306.sock < /data/alldb.20220316.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1231 (42000) at line 7680: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

发生原因

• 从 mysql 8.0.11 开始，废弃了 sql_mode = NO_AUTO_CREATE_USER 这个模式

解决方案

• 修改 mysqldump 文件，将 NO_AUTO_CREATE_USER 替换为空，然后再导入

sed -i 's/NO_AUTO_CREATE_USER//' /data/alldb.20220316.sql

场景4：启动java服务报错 Public Key Retrieval is not allowed

故障上报

• 2022.03.16.升级到 mysql 8.0.30 之后，启动java服务报如下错误：

Caused by: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)

Caused by: com.mysql.cj.exceptions.UnableToConnectException: Public Key Retrieval is not allowed
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

解决方案

• 在db连接后面添加 allowPublicKeyRetrieval=true，比如下面

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/dbname?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true