Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

说明:
编译了2个mysql,mysql5.5的默认不是默认的(/tmp/mysql.sock3307),默认的为/tmp/mysql.sock。导致我mysql5.5 -uroot不指定sock位置,会出现Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’。有确认mysql5.5的my.cnf加载正确,my.cnf的client也已经指定sock还是也不行。错误如下:

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

 
环境:
mysql5.1,配置文件为/etc/my.cnf,sock指向/tmp/mysql.sock

root      7673     1  0 05:14 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/lnmp1.pid
mysql     7836  7673  1 05:14 pts/0    00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --log-error=/usr/local/mysql/var/lnmp1.err --pid-file=/usr/local/mysql/var/lnmp1.pid --socket=/tmp/mysql.sock --port=3306

 
mysql5.5,配置文件为/data/mysql/3307/my.cnf,scok指向/tmp/mysql3307.sock

# ps -ef|grep mysql
root      4603  3438  0 01:29 pts/1    00:00:00 tail -20f /data/mysql/3307/error.log
root      6277     1  0 03:46 pts/0    00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --datadir=/data/mysql/3307 --pid-file=/data/mysql/3307/mysql.pid
mysql     6603  6277  0 03:46 pts/0    00:00:00 /usr/local/mysql5.5/bin/mysqld --basedir=/usr/local/mysql5.5 --datadir=/data/mysql/3307 --plugin-dir=/usr/local/mysql5.5/lib/plugin --user=mysql --log-error=/data/mysql/3307/error.log --pid-file=/data/mysql/3307/mysql.pid --socket=/tmp/mysql3307.sock --port=3307

 
排查:
1. 当我把mysql5.1关闭,只留下mysql5.5,mysql登陆里,就会出现以下情况

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

 
2. 指定mysql5.5 的sock位置就正常

# mysql -uroot -S /tmp/mysql3307.sock 
...
mysql> 

 
3. 查看mysql5.5的my.cnf,可以看到client已经有指定sock位置了,但连接还是会出现Can’t connect to local …. ‘/tmp/mysql.sock’。是加载到了mysql5.1的/etc/my.cnf嘛

# cat /data/mysql/3307/my.cnf
[client]
#password       = your_password
port            = 3307
socket          = /tmp/mysql3307.sock

 
4. 重命名/etc/my.cnf,怕/etc/my.cnf影响到

# mv /etc/my.cnf /etc/my.cnf.bak
# /etc/init.d/mysql5.5 restart
# mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
连接还是失败。。。。
# mv /etc/my.cnf.bak /etc/my.cnf  //改名回去

 
5. 后面无意中试修改了下/etc/my.cnf,将/etc/my.cnf中的client修改成/tmp/mysql3307.sock,原来是/tmp/mysql.sock

[client]
#password       = your_password
port            = 3307
socket          = /tmp/mysql3307.sock

mysql5.5不指定sock位置也能正常连接了。。。。

# mysql -uroot
...
mysql>

 
猜测:
mysql -uroot做为客户端,默认会加载/etc/my.cnf配置里的client sock路径。如果/etc/my.cnf不存在,则会按默认编译里指定的sock来连接,亦即/tmp/mysql.sock。

总结:
其实碰到这个,我都是先检查mysql是否有在运行,sock位置是否正确,是否加载了其它的my.cnf,my.cnf里的client配置是否存在

发表评论

邮箱地址不会被公开。 必填项已用*标注