MySQL Shell连接失败：错误查找/tmp/mysql.sock，实际socket在/var/run/mysqld

环境信息 #

• MySQL Server 5.7
• MySQL Shell 8.0.23
• Ubuntu 22.04
• Virtual Box 7.0.14

服务状态验证 #

确认MySQL服务进程 #

jes@ubuntu2:~$ ps -ef | grep mysqld | grep -v grep
mysql      55368       1  0 10:54 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

验证socket文件路径（mysqladmin） #

jes@ubuntu2:~$ mysqladmin version -uroot -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.42, for Linux on x86_64
Copyright (c) 2000, 2023, 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.
Server version      5.7.42
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         15 min 24 sec
Threads: 1  Questions: 6  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.006

确认socket文件存在 #

jes@ubuntu2:~$ ls -l /var/run/mysqld
total 8
-rw-r----- 1 mysql mysql 6 Feb  9 10:54 mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Feb  9 10:54 mysqld.sock
-rw------- 1 mysql mysql 6 Feb  9 10:54 mysqld.sock.lock

TCP连接验证 #

jes@ubuntu2:~$ mysqladmin -h `hostname` --port=3306 version -uroot -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.42, for Linux on x86_64
Copyright (c) 2000, 2023, 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.
Server version      5.7.42
Protocol version    10
Connection      ubuntu2 via TCP/IP
TCP port        3306
Uptime:         18 min 19 sec
Threads: 1  Questions: 8  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.007

常规mysql客户端连接正常 #

jes@ubuntu2:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, 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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

MySQL Shell连接失败现象 #

jes@ubuntu2:~$ mysqlsh
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
MySQL Shell 8.0.23
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
mysql-py> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> \connect root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': **************
MySQL Error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
mysql-sql> 

配置文件与日志信息 #

my.cnf配置 #

jes@ubuntu2:~$ cat /etc/mysql/my.cnf | grep -v '#'
!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[client]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

错误日志（/var/log/mysql/error.log） #

2024-02-09T09:54:29.806778Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2024-02-09T09:54:29.806804Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2024-02-09T09:54:29.806846Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-02-09T09:54:29.818352Z 0 [Note] Event Scheduler: Loaded 0 events
2024-02-09T09:54:29.818858Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.42'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
2024-02-09T09:58:25.984367Z 2 [Note] Access denied for user 'jes'@'localhost' (using password: NO)

问题原因与解决方法 #

原因 #

MySQL Shell 8.0.23未正确读取系统级my.cnf的[client]段配置，默认优先查找/tmp/mysql.sock（部分版本MySQL客户端的默认socket路径），而常规mysql客户端可正常识别配置中的socket路径。

解决方法 #

方法1：连接时显式指定socket路径

启动MySQL Shell时直接指定：

mysqlsh --socket=/var/run/mysqld/mysqld.sock -uroot -p

或在Shell内部连接时指定：

\connect root@localhost?socket=/var/run/mysqld/mysqld.sock

方法2：创建软链接

将实际socket文件链接到/tmp/mysql.sock：

sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
sudo chown mysql:mysql /tmp/mysql.sock

方法3：配置用户级MySQL Shell配置文件

创建~/.mysqlsh/config.json并添加默认socket配置：

{
  "mysql": {
    "socket": "/var/run/mysqld/mysqld.sock"
  }
}

方法4：检查并清理环境变量

确认是否存在覆盖socket路径的环境变量：

echo $MYSQL_UNIX_PORT

若输出为/tmp/mysql.sock，执行以下命令取消该变量：

unset MYSQL_UNIX_PORT

内容的提问来源于stack exchange，提问作者Jes Challand