LinuxQuestions.org - [SOLVED] Can't connect to MySQL after changing data and socket dirs

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - Can't connect to MySQL after changing data and socket dirs (https://www.linuxquestions.org/questions/linux-software-2/cant-connect-to-mysql-after-changing-data-and-socket-dirs-899321/)

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

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 ?

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

Code:

[client]

socket=/var/run/mysqld/mysqld.sock

Quote:

Originally Posted by DarkSlayer (Post 4452835)

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!

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 (Post 4662330)

Thanks DarkSlayer very much, I finding that!

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)"

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

Encountered this while setting up percona cluster

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,