MySQL: Socket file wrong location

All we need is an easy explanation of the problem, so here it is.

I have gone over dozens of articles related to such issues without solving mine.

MySQL 8 over CentOS 8, my.cnf file indicate the socket as following:

[mysqld]

datadir=/data/mysql8/mysql
socket=/data/mysql8/mysql/mysql.sock

But when I try to connect, mysql detect the old location of datadir of socket file:

[[email protected] ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

While the running is mysql-server not mysql-client and the only config file used is the one mentioned in the question (only /etc/my.cnf exists and all other files are not).

[[email protected] ~]# /sbin/mysqld  --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

Also when I check the mysql config, I can see the wrong dir of socket file being selected.

[[email protected] ~]# mysqladmin variables
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You have defined where the socket file is for the server in the mysqld option group, but you haven’t told the mysql client where to look, so it’s just using the default.

It’s better to put the socket setting in the client-server option group than in the mysqld option group. This is read by both the client and the server, so then you only have to define it once.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply