mysql 8.0 近期使用问题汇总

古城微笑少年丶 2022-04-11 03:11 153阅读 0赞

公司服务器近期将mysql全部从之前的5.7 升级到了新的8.0版本，但是遇到了好多的坑，近期吭哧吭哧的改了两个礼拜的存储过程了，全部都是血泪啊。

下面说说几个大坑

1. mysql 8.0 使用其他客户端登陆的权限问题，登陆时一直提示

ERROR 2059 (HY000): Authentication plugin 'caching\_sha2\_password' cannot be loaded: ÕÒ²»µ½Ö¸¶¨µÄÄ£¿é¡£

mysql 8.0 默认使用 caching\_sha2\_password 身份验证机制 —— 从原来的 mysql\_native\_password 更改为 caching\_sha2\_password。   
从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching\_sha2\_password 。

客户端不支持新的加密方式。

方法之一，修改用户的密码和加密方式

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql\_native\_password BY 'root';

mysql8.\*的新特性 caching\_sha2\_password 密码加密方式

以前版本的mysql密码加密使用的是 mysql\_native\_password  
新添加的用户密码默认使用的 caching\_sha2\_password  
如果在以前mysql基础上升级的 就得用户使用的密码加密使用的是 mysql\_native\_password  
如果使用以前的密码加密方式，就修改文件 /etc/my.cnf

[mysqld]
    default_authentication_plugin=mysql_native_password

2. 老版本的存储过程在运行时会提示：

Access denied for user 'root'@'IP地址'

1、mysql -u root -p 登陆进MYSQL；
    
    2、执行以下命令：
    
    GRANT ALL PRIVILEGES ON *.* TO 'your name'@'%' IDENTIFIED BY 'your password' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    
    3、exit;

3. 在格式化日期插入数据时经常提示错误 “-”，在之前5.7版本如下的规范格式是没问题的， 但是8.0不行。

STR\_TO\_DATE(b.mdleqp\_prop,'%Y.%m')+1 as md\_date

最后的解决办法是如下

DATE_ADD(STR_TO_DATE((CONCAT(IF(b.mdleqp_prop="-",NULL,b.mdleqp_prop),'.01')),'%Y.%m.%d'),INTERVAL 0 DAY) as md_date

4. 在update A表 join 子查询B表时，5.7也是没问题的， 速度非常快，但是到了8.0版本上，速度简直如蜗牛一般，A表大概是180万数据，B表子查询出来的结果是5万的结果，两个互相匹配，一个小时还没有更新完成，

用explain  看了下  update 的时候type 是ALL， KEY是空，

![20181204155430641.png][]

最后的解决办法是把子查询做成了临时表，并加上了索引，之后在update这个临时表 速度瞬间快多了。

[20181204155430641.png]: /images/20220411/0083efd2ddee43b183006df223d95dd3.png