迁移网站至新服务器，提示错误：PHP message: SQLSTATE[HY000] [2006] MySQL server has gone away

迁移网站至新服务器，提示：PHP message: SQLSTATE[HY000] [2006] MySQL server has gone away

迁移用户和数据

创建新数据库

mysql> CREATE DATABASE `blog` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.09 sec)

将导出的数据导入至新库

mysql> use blog;
Database changed
mysql> source /root/blog.sql;

通过使用 show create user 命令查看旧服务器上的用户创建命令

mysql> show create user jeffrey@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for blog@localhost                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*D470CCBABEF01AD4EF8BA4E70E357B51B04AD7D1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

直接使用旧服务器上的创建用户命令（密码和旧服务器上的一模一样）

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*D470CCBABEF01AD4EF8BA4E70E357B51B04AD7D1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
Query OK, 0 rows affected (0.08 sec)

赋予该用户的新库所有权限

mysql> GRANT ALL ON blog.* TO 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.04 sec)

发现问题

以为就此完成了网站迁移，打开网站发现打不开，查看log发现提示错误：

PHP message: SQLSTATE[HY000] [2006] MySQL server has gone away

查找原因

经过一番网上冲浪发现了原因，原来是mysql 8 更改了默认的认证方式，由原来 5.x 版本的 mysql_native_password 更改为 caching_sha2_password，到旧服务器（从 5.x 版本一路升级到的 8.x 版本）上一看：

mysql> select user,host,plugin from mysql.user; 
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| jeffrey          | localhost | mysql_native_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

再到新服务器上一看：

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| jeffrey          | localhost | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

解决方法

1. 修改认证方式并重设密码：

   ALTER USER 'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'auth_string';

2. 修改 mysql 的配置，添加以下行前的注释号（#）去掉，重启 mysql：

   # default-authentication-plugin=mysql_native_password

参考链接

https://dev.mysql.com/doc/refman/8.0/en/password-management.html