Thursday, March 22, 2012

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

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'


Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock


I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

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

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

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

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

1 comment: