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!
Another possible solution to the >can't connect to local mysql server through socket> error message is to try and connect to the MySQL using the 127.0. 0.1 ip address instead of localhost. When you use localhost to connect to MySQL, the operating system uses the socket connector. However, if you use 127.0.
MySQL manages connections to the database server through the use of a socket file, a special kind of file that facilitates communications between different processes. The MySQL server's socket file is named mysqld. sock and on Ubuntu systems it's usually stored in the /var/run/mysqld/ directory.
Have you tried completely removing the software (from your drive, Firewall, the relevant ProgramData files, the registry), and then reinstalling it? Please do so, reboot your system and try starting the software.
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 !!!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With