MySQL5.7 ERROR 2002 (HY000): Can't connect to local MySQL server through socket 解决方案

背景

mysql5.7数据库服务已经启动，但无法连接

[root@dba ~]# cd /opt/mysql/mysql-5.7/
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2023-04-15 11:06:23 CST; 3h 38min ago
 Main PID: 1592 (mysqld)
    Tasks: 28
   Memory: 137.4M
   CGroup: /system.slice/mysqld.service
           └─1592 /opt/mysql/mysql-5.7/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/opt/mysql/mysql-5.7 --datadir=/opt/mysql/mysql-5.7/data --plugin-dir=/opt/mysql/mysql-5.7/lib/plugin...
Apr 15 11:06:23 dba systemd[1]: Started MySQL Server.
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# bin/mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# 

问题原因

编译的时候没有指定socket，所以mysql 命令连接的时候还是使用的默认值（/tmp/mysql.sock）,因为socket位置变了，而mysql 命令不知道，所以就出现了这样的错误

解决方案

查找mysql.sock文件位置

[root@dba mysql-5.7]# ll /temp/mysql.sock
ls: cannot access /temp/mysql.sock: No such file or directory
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# find / -name 'mysql.sock'
/var/lib/mysql/mysql.sock
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# 		

登录测试

[root@dba mysql-5.7]# /opt/mysql/mysql-5.7/bin/mysql -uroot -proot --socket=/var/lib/mysql/mysql.sock
mysql: [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 5
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.37    |
+-----------+
1 row in set (0.01 sec)

mysql> 

设置软链接（也可以修改my.cnf文件）

[root@dba mysql-5.7]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
[root@dba mysql-5.7]# 
[root@dba mysql-5.7]# bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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>