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

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
14,091
Location
Maastricht
Fresh install, VPS with Debian 11.

It seemed all went fine during installation and configuration.

Until I wanted to restore an account from another server using the backup/transfer method, and then I got the above error. Which should'nt be /tmp/mysql.sock because I changed that in the my.cnf which looks like this now:
Code: 
[mysqld]
max_allowed_packet=64M
local-infile=0
bind-address=127.0.0.1
max_connections = 300
sql_mode=
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

So I tried to rebuild
Code: 
[root@srv100: /usr/local/directadmin/custombuild]# ./build mariadb
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
using downloaded binary package
download_with_cache: using cached '/usr/local/directadmin/custombuild/cache/mariadb-10.5.21-linux-systemd-x86_64.tar.gz' file
Stopping mysqld ...
Ensuring local-infile is disabled for security reasons in MySQL configuration file...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
Waiting for mariadb to start up...
etc.
Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
FATAL ERROR: Upgrade failed
failed to running '/usr/local/mysql/bin/mysql_upgrade --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --skip-version-check' system tables might be malformed
Restarting MySQL.
Installation completed.

How can I fix this? Since rebuilding does not fix the problem.
 
Hello.
Yes it's all the same. Exactly the same issue is occuring with Ubuntu as I have in Debian 11, I've see a thread here on the forums.

Error connecting to MySQL: Can't connect to local MySQL server through socket

Hi all. I have 3 VPS servers that i upgraded to Debian 8. 2 of them work like a charm but one is not feeling well. I get this when accessing Mysql Management Error connecting to MySQL: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I have looked into /etc/my.cnf and...
forum.directadmin.com forum.directadmin.com

I refuse to use that workaround by making the symlink, However that works if made.

So I have been looking further and it must be some bug in Debian or in the mariadb build.
Everything is working fine until I change the my.cnf and add this line:

bind-address=127.0.0.1
or remove that line totally.

Once that is in there, the error occurs.
Now as soon as I remove that line, I can connect to mysql via 127.0.0.1.
mysql -h 127.0.0.1 -u root -p

But via localhost it will throw the error:
Code: 
[root@srv100: /etc]# mysql -h localhost -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Which shouldn't happen at all. One should be able to connect via localhost.

And yes I have a hosts file containing the localhost line just as on all other servers.
Code: 
127.0.0.1       localhost.localdomain localhost
::1     localhost ip6-localhost ip6-loopback 
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

This issue just does not exist on any RH alike server, only on this Debian thing, getting real tired of Debian already, lol.
 
I just installed a fresh Debian 12, and MariaDB is working fine. In your case, I would like to remove /etc/my.cnf first, just to use default. And "ps aux" to find if there is any other sql process which is still running. If there is any, kill it first.
 
gate2vn said:
and MariaDB is working fine.
Click to expand...
It was working fine here too until I changed the my.cnf. After a reinstall of mariadb it's working too again. Didn't try to import then because I want the security options active in Mariadb.

Zhenyapan said:
maybe you need to add skip-networking ?
Click to expand...
I will try that this afternoon. At this moment the link is giving an error 500 and was loading extreamly slow. So I will try again later.
 
I think I found the problem. I have to either remove the bind-address statement or set it to 127.0.0.1 instead of localhost (which I always did).

Now see what happens:
Code: 
[root@srv100: /etc]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Which is exactly the same issue as is happening during account restore.

But lets add a commandline option now and see:
Code: 
[root@srv100: /etc]# mysql -u root -p --protocol=tcp                
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.21-MariaDB-log MariaDB Server

So when you define TCP in the commandline, then connection will work without issue.

However, why is that happening as TCP should already used by default??? To me this seems a bug.
Code: 
[root@srv100: /etc]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  44146 mysql   20u  IPv6 104442      0t0  TCP *:mysql (LISTEN)
@fln @DirectAdmin Support ?

Debian 11 latest version
MariaDB 10.5.21
 
OMG Debian is a bitch. Learning curve, this is why I rather use RHEL alikes. :D

Finally found the solution by again installing, now MariaDB 10.6 and suddenly I see it.

On RH alike systems, the [client] tag is empty and the socket is defined via de [mysql] part.
So I used the default my.cnf and there suddenly I see this:

Code: 
[client]
socket=/usr/local/mysql/data/mysql.sock
however, /var/lib/mysql is used. So I removed that line and added the socket to the [mysqld] part where it normally always is. And that was my mistake.

So now I thought "lets see and wait". So I kept the original my.cnf like this, then used the mysql -u root -p command again and was able to login.
Then when logged in I checked and see suddenly there is some odd symlink:
lrwxrwxrwx 1 root root 20 2023-08-08 18:24 mysql -> mariadb-10.6.14-iyYp
so this is only created when logged in as a client.

Now I do cd mysql and:
Code: 
[root@srv100: /usr/local/mysql]# l
total 192K
drwxr-xr-x 11 root root 4.0K 2023-08-08 18:24 .
drwxr-xr-x 20 root root 4.0K 2023-08-08 18:24 ..
drwxr-xr-x  2 root root 4.0K 2023-01-27 06:55 bin
-rw-r--r--  1 root root  18K 2023-06-05 19:02 COPYING
-rw-r--r--  1 root root 2.1K 2023-06-05 19:02 CREDITS
lrwxrwxrwx  1 root root   14 2023-08-08 18:24 data -> /var/lib/mysql
drwxrwxr-x  3 root root 4.0K 2023-06-05 20:04 include
-rw-r--r--  1 root root 8.6K 2023-06-05 19:02 INSTALL-BINARY
drwxr-xr-x  5 root root 4.0K 2023-01-27 06:55 lib
drwxrwxr-x  5 root root 4.0K 2023-06-05 20:04 man
drwxrwxr-x  9 root root 4.0K 2023-06-05 20:04 mysql-test
-rw-r--r--  1 root root 2.7K 2023-06-05 19:02 README.md
-rw-r--r--  1 root root  20K 2023-06-05 19:02 README-wsrep
drwxrwxr-x  2 root root 4.0K 2023-06-05 20:04 scripts
drwxrwxr-x 33 root root 4.0K 2023-06-05 20:04 share
drwxrwxr-x  4 root root 4.0K 2023-06-05 20:04 sql-bench
drwxrwxr-x  4 root root 4.0K 2023-06-05 20:04 support-files
-rw-r--r--  1 root root  85K 2023-06-05 19:02 THIRDPARTY

As you can see the data directory, which contains the socket, has a symlink to the /var/lib/mysql directory where the socket should be (and really is) present.

So seems things in Debian work very different and this [client] line seems needed for some reason.
Tried the restore again and tadaaa... working.

Pffffff....
 
Last edited:
You must log in or register to reply here.
Back
Top