mysql works, mysqldump doesn't

Question

I have mysql 5.5 on my ubuntu 12.04 server. This command:

mysql -u root -p

works perfectly, but this gives me error:

mysqldump -u root -p mydb_name > a.sql
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

In my.cnf file, the socket is set to:

socket = /home/mysql/mysql.sock

so I have no idea where /var/run/mysqld/mysqld.sock is coming from. Thanks.

Ingold

Do you have more than one copy of the MySQL tools on your system? Is it possible that you're invoking a copy of mysqldump from a different installation (and therefore which is reading different configuration files/using different default settings) to mysql? – Laurice

Hi, no it is not possible. I just installed ubuntu server and installed mysql once after. – Ingold

Answer 1

NOTE: serverfault.com may arguably be a better place to ask this question as it is related to server configuration rather than programming.

First, try adding --socket=/home/mysql/mysql.sock to your mysqldump command.

If this works you can make this command line option more persistent and avoid typing it every time by adding it to an option file.

Running mysqldump --help will show you (starting at about line 15 of the output) which option files and groups from which mysqldump will attempt to read options. For example if it reports:

...

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqldump client

...

You could add:

[mysqldump]
socket = /home/mysql/mysql.sock

to either /etc/mysql/my.cnf or ~/.my.cnf or any one of the other files listed, and then you would not need the --socket=... option each time you run mysqldump.

NOTE: if mysqldump is not recognizing your socket option from the [client] group in your existing my.cnf file in the same way that your mysql command does, this may mean that your version of mysqldump may not be reading the same group & option file combination that mysql command is reading for whatever reason.

If the --socket option doesn't work, then it may be some other configuration issue.

1. Be sure you set socket = /home/mysql/mysql.sock in each of the applicable subsections of your my.cnf file:

   [client]
   socket = /home/mysql/mysql.sock
   
   [mysqld_safe]
   socket = /home/mysql/mysql.sock
   
   [mysqld]
   socket = /home/mysql/mysql.sock
   
   [mysqldump]
   socket = /home/mysql/mysql.sock
   

2. DO NOT CHANGE APPARMOR SETTINGS UNLESS ABSOLUTELY NECESSARY! Check to see if you are running apparmor using the command sudo apparmor_status and look for "apparmor module is loaded." near the beginning of the output this command produces. If so, add a line like the following to your /etc/apparmor.d/usr.sbin.mysqld file about 6 lines from the end of the file. The location should be pretty obvious based on similar existing entries for /var/run/mysql/mysqld.sock:

   /home/mysql/mysql.sock rw,
   

You want to restart apparmor and then mysqld at that point, or just reboot to get a clean restart of everything.

Note that these types of non-standard configuration options that you add to apparmor may open you up for security issues. This may also cause conflicts when you run Update Manager and you may need to hand-edit the changes again in the future if you update or re-install ubuntu.

Alboran

Hi, thanx for the answer. Adding "--socket=/home/mysql/mysql.sock" works. in my.cnf all of them are as the same as yours. I added the your second part but didn't work. Should it be /home/mysql/mysqld.sock rw. !? – Ingold

The 2nd part is only needed if the --socket=/home/mysql/mysql.sock didn't work. I would only recommend changing apparmor settings when absolutely necessary. – Alboran

I got my answer but really want to know how I can fix it that it works without --socket – Ingold

I updated my answer to include instructions for using mysqldump --help to determine which file to add [mysqldump] socket option so you don't need to add it to the command line every time. Interested to see if this works with your configuration. – Alboran

Answer 2

I fixed this issue by specifying the host and the port :

mysqldump -host=myserver.com.mysql --port=3306 -u root -p mydb_name > a.sql

Spidery

I couldn't edit because of the character limit but it should be --host instead of -host. Or just use -h – Pauli

Answer 3

You got this error because you didn't mentioned the socket Path

so you have to add the path of you Mysql socket to your command to resolve the error:

mysqldump hive --socket=`$PATH` > /tmp/hive_backup.sql

example:

mysqldump --socket /bigdisk/data/mysql/mysql.sock  > /tmp/hive_backup.sql

Rinna

Answer 4

You could also use it like this:

mysqldump -h localhost -P 3307 --socket /bigdisk/data/mysql/mysql.sock -u root -p databasename > databasename-backup20170902.sql

I use port 3307 because I use "mysqld_multi"

And it worked fine for me.

Hyams

Answer 5

Alternately, one can define the socket with the flag '-S'.

Example: mysqldump -S 'basedir/mysql.sock' test > test.mysql

How to retrieve the socket: (1) See your my.cnf for information on the socket. (2) It's actually located within the MySQL-base directory: 'basedir/mysql.sock'

You might have to look up the my.cnf file for your base directory when you don't remember where the base directory is located.

Grape