Results 1 to 6 of 6

Thread: Mysqldump Can't Connect to Mysql

  1. #1
    Join Date
    Dec 2018
    Posts
    6

    Question Mysqldump Can't Connect to Mysql

    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?

  2. #2
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,569
    Hello,

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

  3. #3
    Join Date
    Dec 2018
    Posts
    6
    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?

  4. #4
    Join Date
    Dec 2018
    Posts
    6
    Quote Originally Posted by zEitEr View Post
    Hello,

    Is that a Debian server?
    Yes, it's Debian 8.6.

    Quote Originally Posted by zEitEr View Post
    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?

  5. #5
    Join Date
    Dec 2018
    Posts
    6
    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?

  6. #6
    Join Date
    Oct 2003
    Location
    Scottsdale, AZ
    Posts
    1,169
    Quote Originally Posted by libre View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •