PHP连接MySQL 8.0报错的解决办法

浅浅的花香味﹌ 2023-07-22 09:04 17阅读 0赞

--------------------

> MySQL升级到8.0之后，PHP连接报错怎么解决？

作为资深的MySQL票友，最近我总在给8.0吹水，我们知数堂的MySQL课程去年也早就升级到8.0版本了。我的VPS老早就想升级了，无奈PHP版本太低，担心升级后有问题，所以一直没去折腾。这两天稍微得空，就开始折腾升级MySQL版本。

为了解锁最香新功能，我直接升级到了最新的MySQL 8.0.19版本：

[yejr@imysql.com]> \s
    ..
    Using delimiter:    ;
    Server version:        8.0.19 MySQL Community Server - GPL)
    Protocol version:    10
    ...
    Binary data as:        Hexadecimal

注意到上面的 **Binary data as: Hexadecimal** 了吗，我前几天的文章 [**MySQL 8.0.19客户端的一个小变化**][MySQL 8.0.19]** **中介绍了这个新特性。

由于我的PHP还是相对较低的版本，MySQL 8.0相对于5.7版本，对PHP这些应用程序影响最大的就是账号认证方式，由此前的 **mysqlnativepassword** 变成了 **cachingsha2password**。除了账号授权方式要保持使用 native 认证插件外，还有个要注意的默认使用的字符集还得保持用 **utf8**，否则可能会报告下面两种错误：

SQLSTATE[HY000] [2054] Server sent charset unknown to the client
    
    或者是
    
    SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

看到上面的报错不用慌，只需要修改下面两个MySQL参数即可：

#MySQL 8.0起默认字符集改成了utf8mb4，这造成PHP PDO连接报错
    #只需要重新指定默认字符集为utf8即可，但这并不影响在MySQL里创建utf8mb4字符集的表
    character-set-server = utf8
    
    #指定账号认证plugin还使用native模式，否则PHP PDO连接握手时会报错
    default_authentication_plugin = mysql_native_password

在MySQL 8.0下创建新账号时，需要先 **CREATE USER** 并指定密码，然后再 **GRANT** 授权，不能再像以前那样，可以直接用一条 GRANT 语句搞定 **创建账号+指定密码+授权** 这三件事了，例如：

[yejr@imysql.com]> CREATE USER yejr@localhost identified with mysql_native_password by 'c97721c63c9fc077';
    [yejr@imysql.com]> GRANT ALL ON yejr.* to yejr@localhost;

把MySQL从5.7到8.0的升级方案，建议如下：

> 1.  先将当前的MySQL 5.7升级到最新子版本，例如5.7.29。
> 2.  检查错误日志，确认没有任何表在升级过程中报错。
> 3.  设置innodb\_fast\_shutdown=0，关闭5.7.29版本实例。
> 4.  修改my.cnf参数，使之适应8.0版本，例如去掉 **internal\_tmp\_disk\_storage\_engine** 参数。
> 5.  在my.cnf中增加参数 **upgrade = AUTO**，使得MySQL 8.0启动后能自动升级系统表和用户表。
> 6.  检查确认MySQL 8.0实例启动过程中无其他报错。

在升级过程中，我还发现如果使用drupal系统的话，可能还会有个报错：

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of xx/includes/lock.inc).

那是因为在drupal代码中，连接mysql时顺便设置了SQLMODE，其中 **NO****AUTOCREATEUSER** 这个模式在8.0之后不复存在了，只需要微调下面的代码：

[root@yejr.me]# vim includes/database/mysql/database.inc
    
        $connection_options['init_commands'] += array(
          #注释掉本行代码，或者删除 NO_AUTO_CREATE_USER 模式即可
          #'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
          'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'",
        );

好了，现在全站都升级到MySQL 8.0了，yeah✌️

enjoy MySQL 8.0。

全文完。

--------------------

由叶老师主讲的知数堂「MySQL优化课」第17期已发车，课程从第15期就升级成MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。

![format_png][]

--------------------

另外，叶老师在腾讯课堂的短课程《**MySQL性能优化**》已开课，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。

下面是报名小程序码

![format_png 1][]

**晴澜茶坊**

**安溪****铁观音、闽南乌龙，做人人喝得起的好茶。**

**点击小程序选购**

**满1斤送还优质羊脂玉瓷茶具+茶巾**

点“在看”给我一朵小黄花![format_png 2][]

![format_png 3][]

[MySQL 8.0.19]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b55f28a4cdce4b021b41457a188cbfa53544117de74cd7876b9ddd244f98e1bf9b4ab8be4&idx=1&mid=2653933464&scene=21&sn=82bd2d6ddbd103efe46a51a357e872de#wechat_redirect
[format_png]: /images/20230528/6e7fddbaa71f451d9db02dc2c4e416ae.png
[format_png 1]: /images/20230528/dc742ef33c874785b66761aba727423f.png
[format_png 2]: /images/20230528/7b0d6d0bf0964f3e822d12f8ca9700b8.png
[format_png 3]: /images/20230528/9cf036c843174780b8e0798c47be5739.png