ERROR 2002 (HY000): Can’t connect to local MySQL server through socket
I need to login mysql
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
I found that there is no mysql.sock in path /var/lib/mysql/.
check path of mysql.sock
[root@localhost ~]# ps -ef | grep mysql
root 23408 1 0 06:01 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/sctsapp/data/mysql–pid-file
=/data/mysql/localhost.localdomain.pid
mysql 24029 23408 0 06:01 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/data/mysql
–plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/log/mysqld.log
–pid-file=/data/mysql/localhost.localdomain.pid –socket=/data/mysql/mysql.sock
root 24784 8954 0 06:07 pts/0 00:00:00 grep mysql
You have new mail in /var/spool/mail/root
but client search mysql.sock in path /var/lob/mysql when login mysql, thus the client can’t find it.
solution
1. specify mysq.sock path when login mysql.
[root@localhost ~]# mysql -uroot -px9uLCqlkAfez9p6m -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 128
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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>
2. add [client] domain in my.cnf, and specify the real path of mysql.sock
vim /etc/my.cnf
[client]
port = 3306
socket=/data/mysql/mysql.sock
save and exit,restarting mysql, login mysql again
[root@localhost ~]# service mysql restart
Shutting down MySQL…. SUCCESS!
Starting MySQL. SUCCESS!
[root@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 1
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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>
3. make link for mysql.sock, you can login mysql without specifing mysql.sock path.
ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock
Attachment:
The fountion of mysql.sock
mysql will generate a sock file in default path /tmp/ when mysqld_safe startup.
this file is used to connection between client and server.suppose we delete mysql.sock
by mistake.
[root@10-186-20-46 local]# cd /tmp
[root@10-186-20-46 tmp]# ll total 8
-rw-r–r– 1 root root 1201 Jun 25 09:59 my.cnf srwxrwxrwx 1 mysql mysql 0 Jun 25 09:46 mysql.sock
-rw-rw-r– 1 zabbix zabbix 4 Jun 16 15:43 zabbix_agentd.pid
[root@10-186-20-46 tmp]# rm mysql.sock rm: remove socket
`mysql.sock’? y
[root@10-186-20-46 tmp]# mysql -uroot -p Enter
password: ERROR 2002 (HY000): Can’t connect to local MySQL server
through socket ‘/tmp/mysql.sock’ (2)
[root@10-186-20-46 tmp]#
you can find that you can’t login mysql from localhost.
If you have installed multiple mysql in one server, so there many mysqlxxxx.sock will be created
in path /tmp/ when different mysqld_safe started. but the path of mysqlxxxx.sock you can specify
in my.cnf.but you have only installed one client, so if you want to login different mysql services, you
must specify different path of mysqlxxxx.sock.
Note:
If many mysql services use the same user and password, and you want login only one mysql
service.You must be care of the path of mysqlxxxx.sock to avoid you logining the wrong service.
All in all, you’d better keep the password different when many mysql services runing in a same server.