mysqldump cannot connect using socket

Question

This issue has been racking my brain for a few hours. I have been trying to use mysqldump to dump a database, using:

mysqldump --protocol=socket -S /var/run/mysqld/mysqld.sock database`

However, I keep getting:

1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

I am on localhost and running under root (sudo su).
Root@localhost is allowed in the mysql user table.
I can use > mysql to view all of the databases, but mysqldump will not work.
I do not know the root password (system generated).
I have tried adding the socket to the my.conf like so and restarting the mysql server:

[mysqldump]
socket = /var/run/mysqld/mysqld.sock

Any help would be appreciated!

Answer 1

Even though you are connecting via the socket, you must still give the user root

If root@localhost has no password then do this:

mysqldump -uroot --protocol=socket -S /var/run/mysqld/mysqld.sock database

If root@localhost has a password then do this:

mysqldump -uroot -p --protocol=socket -S /var/run/mysqld/mysqld.sock database

If running

mysql

lets you login with specifying -uroot, try not specifying the socket either

mysqldump database

I just noticed that the socket you specified for mysqldump is

[mysqldump]
socket = /var/run/mysqld/mysqld.sock

You need to make sure the socket is defined under the [mysqld] section of my.cnf as well

If this does not exist

[mysqld]
socket = /var/run/mysqld/mysqld.sock

then run this query

SHOW VARIABLES LIKE 'socket';

and make sure of the socket file's name and path.

You could have you System DBA add a custom user for you

GRANT ALL PRIVILEGES ON *.* TO tyler@localhost;

Then, you can run

mysqldump -utyler --protocol=socket -S /var/run/mysqld/mysqld.sock database

This is not secure. tyler should have a password. So, run this:

SET SQL_LOG_BIN=0;
GRANT ALL PRIVILEGES ON *.* TO tyler@localhost IDENTIFIED BY 'tylerspasssword';

then you can do

mysqldump -utyler -p --protocol=socket -S /var/run/mysqld/mysqld.sock database

Give it a Try !!!

Answer 2

I found the solution! The socket does not hold the credentials itself. They are stored in the /root/.my.cnf configuration file instead. Mine only had the username and password for the mysql command. I needed to add [mysqldump] to it as well. Here is what my /root/.my.cnf file looks like now:

[mysql]
user=root
pass=myawesomepass

[mysqldump]
user=root
pass=myawesomepass