[SOLVED] Can't connect to MySQL after changing data and socket dirs

DarkSlayer · 08-25-2011, 02:00 AM

Hello

Centos 6, mysql 5.1.52-1.el6_0.1

I wanted to change my datadir from /var/lib/mysql to /home/mysql
as well as to put socket file to /var/run/mysqld
Here my configs and error
where should i change path else to get it working ?

Code:

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

Code:

# netstat -tulpn | grep mysql*
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1576/mysqld

my.cnf

Code:

[mysqld]
datadir=/home/mysql
socket=/var/run/mysqld/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
collation-server=utf8_general_ci

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysql.sock

edited daemon script as well /etc/rc.d/init.d/mysqld

Code:

get_mysql_option mysqld datadir "/home/mysql"
datadir="$result"
get_mysql_option mysqld socket "/var/run/mysqld/mysql.sock"
socketfile="$result"

New socket file is being created in proper dir i want to.
Yes i have restarted service as well as whole machine.
Why does it still trying to read sock file in old location ? Where should i change this setting ?

DarkSlayer · 08-25-2011, 02:13 AM

Solved.
Had to change client part of mysql as well.

Code:

[client]
socket=/var/run/mysqld/mysqld.sock

tquang · 04-24-2012, 11:49 PM

Quote:

Originally Posted by DarkSlayer

Solved.
Had to change client part of mysql as well.

Code:

[client]
socket=/var/run/mysqld/mysqld.sock

Thanks DarkSlayer very much, I finding that!

DarkSlayer · 04-26-2012, 08:08 AM

After that for proper work of PHP with mysql mysqli you have to change some strings in php.ini:

Code:

mysql.default_socket =
and
mysqli.default_socket =

also i haven't figure out how to configure mysql socket for already installed PDO extension. so if in some app especially if they are relay upon PDO you have to write 127.0.0.1 instead of "localhost"

Quote:

Originally Posted by tquang

Thanks DarkSlayer very much, I finding that!

cartier247 · 05-05-2012, 09:15 AM

Could I hire you to fix my error?

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

sinichi33 · 04-02-2013, 03:23 AM

Hi, just wanna share, today I fixed the problem with this error message : "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)"

the problem was with the permission of mysql user , the new datadir path I set is /new/data

first I only set the permission to the designated folder only : chown mysql:mysql /new/data, then error occurred.

I fixed the issue by set the correct user (mysql) to the folder /new , I set chown mysql:mysql /new, please try. Thanks

Adinath_Kamode · 07-31-2018, 05:11 AM

Hi,
I encountered this while setting up percona 5.7 cluster. So after updating mysqld.cnf, client section issue was not fixed.
Ultimately it resolved after updating mysqld_safe.cnf file with new socket path.

Thanks,