ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

时间:2021-11-10 19:44:38

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

背景:

mysql -uroot -p123456
提示:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

原因:

mysql.sock文件是服务器与本地客户端进行通信的Unix套接字文件,默认位置是/tmp/MySQL.sock。可能此文件不存在。

解决:

四种方案

  1. 在使用mysql 连接的本地数据库时, 命令行添加参数指定,sock 文件的地址
  2. 配置my.cnf 的 [client] 指定 sock 文件的地址
  3. 修改MYSQL_UNIX_PORT环境变量, 将其设置为 unix sock 文件的路径
  4. 重新编译指定 --with-unix-socket-path 选项

方法一

查看当前机器已启动的mysqld使用的socket文件的位置如下:

[user_00@localhost ~]$ ps -ef | grep mysqld
root 5905 1 0 21:08 pts/17 00:00:00 /bin/sh /usr/local/services/mysql-5.6.24/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysqld.pid
mysql 6144 5905 0 21:08 pts/17 00:00:02 /usr/local/services/mysql-5.6.24/bin/mysqld --basedir=/usr/local/services/mysql-5.6.24 --datadir=/data/mysql --plugin-dir=/usr/local/services/mysql-5.6.24/lib/plugin --user=mysql --log-error=/data/mysql/mysql.err --pid-file=/data/mysql/mysqld.pid --socket=/data/mysql/mysql.sock --port=3306
user_00 6430 5575 0 21:14 pts/19 00:00:00 grep --color=auto mysqld

当前机器监听的本地socket 文件是:

--socket=/data/mysql/mysql.sock

或者,可以通过如下查询:

[user_00@localhost ~]$ netstat -xl | grep mysql
unix 2 [ ACC ] STREAM LISTENING 115130474 /data/mysql/mysql.sock

故可以使用如下方式进行连接:

[user_00@localhost ~]$ mysql -uroot -p123456 -S /data/mysql/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 Source distribution Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>

方法二

在mysql的配置文件 /etc/my.cnf 中指定链接的socket 的地址

[client]
socket=/data/mysql/mysql.sock

再连接,成功

[user_00@localhost ~]$ mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.24 Source distribution Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>

方法三

将MYSQL_UNIX_PORT环境变量设置为Unix套接字文件的路径

[user_00@localhost ~]$ echo $MYSQL_UNIX_PORT

[user_00@localhost ~]$ export MYSQL_UNIX_PORT=/data/mysql/mysql.sock
[user_00@localhost ~]$ mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24 Source distribution Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>

方法四

源码编译MySQL,不用默认Unix套接字文件位置。使用 --with-unix-socket-path 选项指定路径。

检查

用 mysqladmin 连接服务器,测试套接字位置是否正确

[user_00@localhost ~]$ mysqladmin -uroot -p123456 --socket=/data/mysql/mysql.sock version
mysqladmin Ver 9.0 Distrib 5.5.41-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Server version 5.6.24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /data/mysql/mysql.sock
Uptime: 29 min 35 sec Threads: 1 Questions: 6 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.003

用错误的 socket 文件,提示如下

[user_00@localhost ~]$ mysqladmin -uroot -p123456 --socket=/data/mysql/mysqle.sock version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/data/mysql/mysqle.sock' (2)'
Check that mysqld is running and that the socket: '/data/mysql/mysqle.sock' exists!