ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect… Connection id: 125 Current database: *** NONE ***

新创建的普通用户登陆系统提示如下错误

root@deepfos:~# mysql -uhbtest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114
Server version: 5.7.36-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    125
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 125 to db: 'unconnected' user: 'test' host: 'localhost' (init_connect command failed)

使用Navicat工具提示2013 - Lost connection to MySQL server during query

排查思路，首页我们查看mysql错误日志

2022-12-01T10:49:39.383377Z 114 [Warning] Aborted connection 114 to db: 'unconnected' user: 'hbtest' host: 'localhost' (init_connect command failed)
2022-12-01T10:49:39.383428Z 114 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘SET NAMES utf8mb4‘' at line 1
2022-12-01T10:49:50.778555Z 125 [Warning] Aborted connection 125 to db: 'unconnected' user: 'hbtest' host: 'localhost' (init_connect command failed)
2022-12-01T10:49:50.778588Z 125 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘SET NAMES utf8mb4‘' at line 1

发现错误日志中显示用户登陆sql初始化异常，语法错误

init_connect作用

• init_connect 是用户登录到数据库上之后，在执行第一次查询之前执行 里面的内容的。
• 如果init_connect 的内容有语法错误，导致执行失败，会导致用户无法执行查询，从mysql 退出
• init_connect 对具有super(root)权限的用户是无效的

接下来查看init_connect变量

mysql> SHOW GLOBAL VARIABLES LIKE 'init_connect';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| init_connect  | ‘SET NAMES utf8mb4‘     |
+---------------+-------------------------+
1 row in set (0.00 sec)

我们新创建的用户只有制度权限，但是这个新连接初始化的值字符集异常

检查my.cnf配置文件

root@abcdocker:~# cat /etc/my.cnf |grep SET
init_connect=‘SET NAMES utf8mb4‘
root@abcdocker:~# 

此时已经发现问题，反引号中文了，接下来修改配置文件，重启mysql

root@abcdocker:~# cat /etc/my.cnf |grep SET
init_connect='SET NAMES utf8mb4'

root@abcdocker:~# /etc/init.d/mysqld restart
[ ok ] Restarting mysqld (via systemctl): mysqld.service.

重启后配置连接正常