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.

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.

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