Mysqldump Can't Connect to Mysql

libre

New member
Joined
Dec 6, 2018
Messages
6
Hi guys,

I'm trying to backup all users on my server using the Backup tool but it's giving an error:

Error while backing up database admin_cs2015
Error while backing up database admin_cs2015: The sql file is 0 bytes in size: /home/admin/admin_backups/admin/backup/admin_cs2015.sql
mysqldump error output: mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)" when trying to connect

Here's what I already tried:

- Resetting the mysql passwords
- Running mysql with authentication disabled (--skip-grant-tables)
- rm -rf /root/.my.cnf (I didn't have the file to begin with but I tried the command anyway)

None of this worked, I get exactly the same error still.

Any clues?
 
Hello,

Is that a Debian server? Are you sure your MySQL socket is located under /tmp/mysql.sock?
 
Yes it's a Debian:

root@server ~ # uname -a
Linux server.secureserver01.net 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux

Indeed there's not much in /tmp:

root@server ~ # ls /tmp
pear
systemd-private-4ee6935719aa4a639543bbc2f018e1ce-exim.service-YyeNvD
systemd-private-4ee6935719aa4a639543bbc2f018e1ce-httpd.service-1rN1p0
systemd-private-4ee6935719aa4a639543bbc2f018e1ce-systemd-timesyncd.service-A5uNsJ

I don't know where Mysqldump is getting that path from, in /etc/my.cnf I have a different path and it seems to be working:

[mysql]

# CLIENT #
port = 3306
#socket = /usr/local/mysql/data/mysql.sock
socket = /home/mysql/mysql.sock

I've tried alternating between these 2 paths but it doesn't change the error message, perhaps Mysqldump is getting that path from somewhere else? But where?
 
Hello,

Is that a Debian server?

Yes, it's Debian 8.6.

Are you sure your MySQL socket is located under /tmp/mysql.sock?

Actually I don't know how to check where it is.

"locate mysql.sock" returns "/home/mysql/mysql.sock"

But in "/etc/my.cnf" it is set to "socket = /usr/local/mysql/data/mysql.sock"

Which one is correct?

And where could I set it for Mysqldump/Backup utility of Direct Admin?
 
Seems the real file is on "/home/mysql/mysql.sock"

So I simply created a link pointing there and it worked, at least temporarily:

cd /tmp
ls /home/mysql/mysql.sock mysql.sock

That worked, but is there a better solution?

Why is Mysqldump looking at the wrong location in the first place where as Mysql works fine in the server?
 
Why is Mysqldump looking at the wrong location in the first place where as Mysql works fine in the server?

mysqldump needs to read /etc/my.cnf to find the location of mysql.sock or what ever the mechanism it will use to communicate with the MySQL server. MySQL server on the other hand doesn't need to use that socket to communicate with itself, it merely needs to create that file and then wait for some client to connect to that socket and start sending commands. My guess is that there may be other my.cnf files that your MySQL server is picking up first and using the socket setting from there.

Issue the following command:
mysqld --verbose --help | grep -A 1 "Default options"
and
mysqldump --verbose --help | grep -A 1 "Default options"

It will list all the my.cnf files it will process and the order it will process them. One of them is pointing the socket at /home/mysql/mysql.sock. I am guessing that MySQL server and mysqldump may not agree on the order or location of paths.
 
Back
Top