Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Loosie94 · Oct 14, 2021

Hi guys,

I've got a bit of an issue here.
I have a VPS that's running on CentOS Linux release 7.9.2009 (Core) and DirectAdmin and CustomBuild 2.
Everything was working fine until yesterday when I started updating some packages. Now I keep getting the error: "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)"

This error is a common one, because I found it in the docs and on StackOverflow.

Troubleshooting | Directadmin Docs

DirectAdmin Knowledge Base

docs.directadmin.com

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I am getting the following error when I try to connect to mysql: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Is there a solution for this error? What might...

stackoverflow.com

Thing is, these solutions don't seem to work.
I can login on MySQL from the terminal with SSH, but in DirectAdmin I can't access it without keep getting the error.
I've added: socket=/var/lib/mysql/mysql.sock to the /etc/my.cnf file, but this also does not seem to work.

I spend a lot of hours on this issue already, but am stuck at the moment.
Hope on of you guys knows the answer what to do.

Thanks in advance.

Zhenyapan · Oct 14, 2021

do you have /var/lib/mysql/mysql.sock after mysql restart?
directadmin mysql management allows you to work with DBs? only websites don't see mysql?

MaXi32 · Oct 14, 2021

Probably try this location of socket:

Code:

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

and make sure your restart mysqld process:

Code:

systemctl restart mysqld

Since you can login into mysql terminal, you can verify the socket location like this:

Code:

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

If that does not work.. there might be another solution.

Loosie94 · Oct 14, 2021

MaXi32 said:
Probably try this location of socket:

Code:

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

and make sure your restart mysqld process:

Code:

systemctl restart mysqld

Since you can login into mysql terminal, you can verify the socket location like this:

Code:

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

If that does not work.. there might be another solution.

Unfortunally that didn't work.

DirectAmin says MySQL is working, but now I can't connect in the terminal either.
`mysql -u brandbytes -p` outputs the same message now: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)

My credentials are good, because I checked them with: `cat /usr/local/directadmin/conf/mysql.conf`
But yet, I get the same error. I have no idea what I'm doing wrong.

Zhenyapan · Oct 14, 2021

check if mysql really started via ssh, not at DA panel,
also from console you can check current socket path, maybe there wrong permissions\owner for mysql socket dir and mysql not able to create this socket file

MaXi32 · Oct 14, 2021

Zhenyapan said:
check if mysql really started via ssh, not at DA panel,
also from console you can check current socket path, maybe there wrong permissions\owner for mysql socket dir and mysql not able to create this socket file

yeah show the status of mysqld here like

Code:

systemctl status mysqld

ikkeben · Oct 14, 2021

Can't help

But you forgot to write which packages and versions as OS used and so on...

Everything was working fine until yesterday when I started updating some packages.

So be so complete as possible (fake)example updated package mqsql 5.5.43.444 to mysql 5.7.4.55

After updates failing or failing service you have normally log files of that updates did you saved those for support...

DanielP · Oct 14, 2021

login to phpmyadmin as da and see where socket is

it could be at /tmp/mysql.sock if it is there you can workaround it as /usr/local/mysql/data/mysql.sock to be sim link to it

Loosie94 · Oct 15, 2021

MaXi32 said:
yeah show the status of mysqld here like

Code:

systemctl status mysqld

Is outputing this...

mysqld.service - MySQL database server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since vr 2021-10-15 13:41:31 CEST; 55s ago
Process: 19358 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
Main PID: 19358 (code=exited, status=0/SUCCESS)

okt 15 13:41:30 vps0356.zxcs.nl systemd[1]: Started MySQL database server.
okt 15 13:41:30 vps0356.zxcs.nl mysqld_safe[19358]: my_print_defaults: [ERROR] unknown option '--mysqld'.
okt 15 13:41:30 vps0356.zxcs.nl mysqld_safe[19358]: 211015 13:41:30 mysqld_safe Logging to '/var/lib/mysql/vps0356.zxcs.nl.err'.
okt 15 13:41:30 vps0356.zxcs.nl mysqld_safe[19358]: 211015 13:41:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Loosie94 · Oct 15, 2021

Also, when trying to build from Custom Build 2.0. I get this error:

Executing /usr/local/directadmin/plugins/custombuild/admin/build mysql...
/usr/bin/mysql: unknown variable 'passwd=MYPASSWORD'
Found /usr/local/directadmin/custombuild/mysql/MySQL-client-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-devel-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-server-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-shared-5.6.51-1.el7.x86_64.rpm
Stopping mysqld ...
Updating MySQL 5.6.51 to 5.6.51
warning: MySQL-client-5.6.51-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################
installing package MySQL-server-5.6.51-1.el7.x86_64 needs 265MB on the / filesystem
Ensuring local-infile is disabled for security reasons in MySQL configuration file...
/var/lib/mysql/mysql does not exist, running clean mysql data installation...
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/usr/local/mysql/data/mysql.sock' exists!
Error setting root pass using /usr/bin/mysqladmin. Trying SET PASSWORD.
Setting password: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MYPASSWORD');
*********************************
We seem to have an error when trying to SET PASSWORD FOR 'root'@'localhost'
*********************************
Done!

MYPASSWORD = My current MySQL password.

ikkeben · Oct 15, 2021

Loosie94 said:
We seem to have an error when trying to SET PASSWORD FOR 'root'@'localhost'
*********************************
Done!

MYPASSWORD = My current MySQL password.

Was in between u update or change in MYSQL versions or even to and back from Mariadb?

While between versions the password "thing" could be handle different.

You can try if important and no backup howto change and set mqsl root password on CLI with SSH.

BAckups backups

You have to put this then also in some files that it is reconized by DA i think

Sorry have no experience with DA , but with other then set a password on command line mysql... did help, so please do search or this is possible and howto for DA boXES

I believe i have seen about mysql pasowrd in custombuild gui settings but also don't remember where exact

IS this a migrated box from CPANEL?

MaXi32 · Oct 15, 2021

Loosie94 said:
Also, when trying to build from Custom Build 2.0. I get this error:

Executing /usr/local/directadmin/plugins/custombuild/admin/build mysql...
/usr/bin/mysql: unknown variable 'passwd=MYPASSWORD'

MYPASSWORD = My current MySQL password.

You got the socket error because mysqld is not running. It looks like you also have syntax error in /etc/my.cnf based on the above, it should be password=MYPASSWORD. Example this is mine.

Code:

[mysqld]
bind-address=127.0.0.1
max_allowed_packet=64M
local-infile=0
innodb_file_per_table

[client]
user=root
password=PASSWORD
socket=/usr/local/mysql/data/mysql.sock

Note that simply running ./build mysql will not reinstall mysql to original state.

If you want to reinstall mysql, you can try rename this /etc/my.cnf to /etc/my.cnf.backup first.

If you plan to restore data, then you can backup mysql with the following method:

Code:

cd /usr/local/directadmin/custombuild

./build set mysql_backup yes

./build mysql_backup

Then follow this step to fully reinstall mysql. Try the step 1-4, follow the step that says New method here https://help.directadmin.com/item.php?id=563.

The step 4 is to restore the backup that you have done above. To restore sql data:

Code:

cd /usr/local/directadmin/custombuild/mysql_backups

wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh

chmod 755 restore_sql_files.sh

./restore_sql_files.sh

Of if you don't care about sql backup just follow the step 1-3

Now you can recreate back your /etc/my.cnf (make sure no syntax error).

Loosie94 · Oct 16, 2021

MaXi32 said:
You got the socket error because mysqld is not running. It looks like you also have syntax error in /etc/my.cnf based on the above, it should be password=MYPASSWORD. Example this is mine.

Code:

[mysqld] bind-address=127.0.0.1 max_allowed_packet=64M local-infile=0 innodb_file_per_table [client] user=root password=PASSWORD socket=/usr/local/mysql/data/mysql.sock

Note that simply running ./build mysql will not reinstall mysql to original state.

If you want to reinstall mysql, you can try rename this /etc/my.cnf to /etc/my.cnf.backup first.

If you plan to restore data, then you can backup mysql with the following method:

Code:

cd /usr/local/directadmin/custombuild ./build set mysql_backup yes ./build mysql_backup

Then follow this step to fully reinstall mysql. Try the step 1-4, follow the step that says New method here https://help.directadmin.com/item.php?id=563.

The step 4 is to restore the backup that you have done above. To restore sql data:

Code:

cd /usr/local/directadmin/custombuild/mysql_backups wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh chmod 755 restore_sql_files.sh ./restore_sql_files.sh

Of if you don't care about sql backup just follow the step 1-3

Now you can recreate back your /etc/my.cnf (make sure no syntax error).

Thank you for your detailed answer.
I managed to get back a MySQL installation with all my databases.
I used the same details as you described in /etc/my.cnf, because I did not change any default path on my VPS.
But unfortunately when trying to build (and enable) MySQL I still get the stocket error.

The terminal says:

[root@vps0356 custombuild]# ./build mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)
Found /usr/local/directadmin/custombuild/mysql/MySQL-client-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-devel-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-server-5.6.51-1.el7.x86_64.rpm
Found /usr/local/directadmin/custombuild/mysql/MySQL-shared-5.6.51-1.el7.x86_64.rpm
Stopping mysqld ...
Updating MySQL 5.6.51 to 5.6.51
warning: MySQL-client-5.6.51-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
installing package MySQL-server-5.6.51-1.el7.x86_64 needs 274MB on the / filesystem
Ensuring local-infile is disabled for security reasons in MySQL configuration file...
Giving mysqld a few seconds to start up...
Giving mysqld a another few seconds to start up...
Giving mysqld last 20 seconds to start up...
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
/usr/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2) when trying to connect
Restarting MySQL.
Installation completed.

Now I also have a my.cnf in /usr/loca/directadmin/conf. Do I have to delete this one? I've renamed it to: "old-my.cnf" to make sure the one in /etc/my.cnf is beiing used, but as said. Still get a socket error. ?

ikkeben · Oct 16, 2021

As written somewhere there must be updated / changed things while that mysql version is and very very old and mariadb is kind of default in centos , don't know how this is handled in DA

BUT ARE YOU REALY SURE THAT IS THE VERSION YOU ALWAYS USED AND WORKED before and whith?
And in between not a change and or backwards then...

MaXi32 · Oct 16, 2021

Make sure to kill all mysqld processes before installation

Code:

pkill -9 mysqld

and you can follow this step to remove leftover:

Code:

rm -rf /var/lib/mysql
rm /usr/bin/mysql
rm /usr/sbin/msyqld
rm /usr/local/directadmin/custombuild/mysql
yum remove MariaDB*
rpm -e MariaDB*

This is taken from:

Fresh install mysql 5.7

Hello there Mariadb 5.5 comes by default in directadmin installation, i want to use mysql 5.7. I see errors when I update after installation. How to install mysql 5.7 instead of mariadb on first install? Although I chose "mysql=5.7" and "mysql_inst mysql" in options.conf before installing...

forum.directadmin.com

Then follow the previous step 1-3 to fully reinstall mysql. I guess the installation did not go through because you had leftover files.

MaXi32 · Oct 18, 2021

@Loosie94

Did you solve the issue? I just notice from your installation log that the installation actually has been completed as it reads

Code:

Installation completed.

The step that I had given to you in post #12 was to address the issue on your post #10 because you had misconfigured authentication of /etc/my.cnf when you want to rebuild mysql, it was not mean to solve the error message about the socket error. Also, you should not reinstall mysql just because of the socket error. Socket errors happen for many reasons and reinstallation will not fix it. Also, I guess you did not reinstall mysql properly in post #10, so I provided the correct way to reinstall mysql.

Probably you could PM me so we could live chat (or reply PM that I have sent last friday so we could have long discussion there). I will not charge anything.

Loosie94 · Oct 18, 2021

MaXi32 said:
@Loosie94

Did you solve the issue? I just notice from your installation log that the installation actually has been completed as it reads

Code:

Installation completed.

The step that I had given to you in post #12 was to address the issue on your post #10 because you had misconfigured authentication of /etc/my.cnf when you want to rebuild mysql, it was not mean to solve the error message about the socket error. Also, you should not reinstall mysql just because of the socket error. Socket errors happen for many reasons and reinstallation will not fix it. Also, I guess you did not reinstall mysql properly in post #10, so I provided the correct way to reinstall mysql.

Probably you could PM me so we could live chat (or reply PM that I have sent last friday so we could have long discussion there). I will not charge anything.

Have send you a reply in the conversation. Thanks

MaXi32 · Oct 19, 2021

Loosie94 said:
Have send you a reply in the conversation. Thanks

Hopefully, to help other users who are looking into this post, if you have solved this issue, you can update this post to SOLVED and whether removing temporary files fixed the issue. The main issue that was found yesterday was related to disk space full.

Code:

Could not set the file size of './ibtmp1'. Probably out of disk space

and this might be one of the reasons why you got a socket error message like you posted. You can see from the stackoverflow link that you posted in #1 https://stackoverflow.com/questions...erver-through-socket-var-lib-mysql-mysql-sock. Some people there are complaining about the space issue.

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Loosie94

Verified User

Troubleshooting | Directadmin Docs

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Zhenyapan

Verified User

MaXi32

Verified User

Loosie94

Verified User

Zhenyapan

Verified User

MaXi32

Verified User

ikkeben

Verified User

DanielP

Verified User

Loosie94

Verified User

Loosie94

Verified User

ikkeben

Verified User

MaXi32

Verified User

Loosie94

Verified User

ikkeben

Verified User

MaXi32

Verified User

Fresh install mysql 5.7

MaXi32

Verified User

Loosie94

Verified User

MaXi32

Verified User