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

[jayw1]

Hi all,

Brand new installation on Ubuntu 20.04, using MariaDB 10.5
Trying to run mysql_secure_installation and this happens, what's going on?
MySQL is running but no password works.
Default my.cnf didn't work and neither does my customized my.cnf.

Any ideas?

 

[Zhenyapan]

just rename mysql dir and run mysql installation by custombuild again

 

[jayw1]

just rename mysql dir and run mysql installation by custombuild again

Won't it just do the same thing?

 

[jayw1]

Fixed by creating a symbolic link between /var/lib/mysql/mysql.sock and /tmp/mysql.sock

 

[alrnetwork]

Hi there @jayw1!

I've been having the exact same issues since upgrading DA to the latest version and / or Upgrading MariaDB. I noticed it first when backups began failing with that error message. Website functionality and phpMyAdmin etc are all fine.

Checking the path /var/lib/mysql/ shows a "mysql.sock" file, however, it's represented in my SFTP application as a "?" - meaning, I'm guessing that link has broken too.

Double-clicking the file or trying to edit it using Nano says "Error Reading mysql.sock: No such device or address"

Would you mind sharing your solution. I just don't know enough about DA yet, to figure out where the files are supposed to be and what should be linking to what.

Please be kind enough to include any commands used.

 

[jayw1]

The question mark likely means there is no icon available for .sock extension.

Your issue may be different to mine as well, but here was my solution. Run in SSH:

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

 

[alrnetwork]

@jayw1 - This actually did solve it. After running the command, it allowed me to login via CLI. However, I rebooted the server (to see what happens to the contents of the tmp folder) and the issue presented itself again.

It appears as though this file isn't being generated automatically, or? - Have you tried rebooting?

 

[Arieh]

It makes sense, linux deletes all stuff in the /tmp dir while rebooting. The original MySQL socket is only there when MySQL is starting, after booting.

I think it could be more beneficial to find out why the mysql cli commands here expect the socket to be in /tmp. It seems to be that is the odd one out that needs fixing.

Maybe try this in ssh

mysql
show variables like 'socket';

This shows me the following:

MariaDB [(none)]> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.002 sec)

Does this say /tmp/mysql.sock for you?

 

[jayw1]

@jayw1 - This actually did solve it. After running the command, it allowed me to login via CLI. However, I rebooted the server (to see what happens to the contents of the tmp folder) and the issue presented itself again.

It appears as though this file isn't being generated automatically, or? - Have you tried rebooting?

Haven't rebooted yet.
I'll run the command automatically on boot if needed.

 

[alrnetwork]

So, running "mysql" resulted in the same error. I created the symlink again and then was able to get the following output, which matches yours.

MariaDB [(none)]> show variables like 'socket';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| socket        | /usr/local/mysql/data/mysql.sock |
+---------------+----------------------------------+
1 row in set (0.001 sec)

 

[Arieh]

Okay that makes sense actually, since the show variable is showing the server, and the issue is with the client, my bad.

But this should be a really simple thing. I think there must be a wrong config somewhere.

I just went to my /etc/my.cnf and added this:

[mysql]
socket=/tmp/mysql.sock

That's all to recreate this issue for me

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

Then I change it to socket=/var/lib/mysql/mysql.sock

And it works (by default that config line is not there at all)

In your case by the way I see /usr/local/mysql/data/mysql.sock which is different than /var/lib/mysql/mysql.sock, but that's all right I suppose.

I would suggest this, add this to /etc/my.cnf

[mysql]
socket=/usr/local/mysql/data/mysql.sock

And see if that works without the symlink being there.

Still doesn't explain why the issue is there, could be another .cnf file somewhere.

 

[jayw1]

I thought that as well.
Mine is at /var/lib/mysql/mysql.sock which I have in my.cnf - I still get the error.
This is only an Ubuntu issue it seems. I do not get this with Alma.