mysqldump Error 1045 Access denied despite correct

Question

This is a tricky one, I have the following output:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

When attempting to export my database with mysqldump on Windows XP. The username is root, the password is correct and contains only alphanumeric characters. I have tried different cases, with/without quotes, specifying using -u and -p, specifying using --user= and --password= and other methods of specifying user/passwords etc, specifying the host (it's all local) and even specifying the database using --databases instead of just blank. The error is always the same when using a password and always the same except the "NO" message when without. I have tried many fixes found through searches with no success. One fix suggested inspecting mysql.conf, but the Windows build doesn't seem to have one. The credentials (and indeed commandline parameters) work perfectly with mysql.exe - this problem only seems to be affecting mysqldump.exe.

Many thanks for your help.

Answer 1

If you're able to connect to the database using mysql, but you get an error for mysqldump, then the problem may be that you lack privileges to lock the table.

Try the --single-transaction option in that case.

mysqldump -h database.example.com -u mydbuser -p mydatabase --single-transaction  > /home/mylinuxuser/mydatabase.sql

Answer 2

You need to put backslashes in your password that contain shell metacharacters, such as !#'"`&;

Answer 3

In Past same problem occurred to me after I copied the mysqldump statement from a MS Word file.

But When typing the statement directly, everything worked fine.

In hex editor the "-" of the not working statement was represented by the unicode char e2 80 93 (http://www.fileformat.info/info/unicode/char/2013/index.htm)

In sort, type password directly and check the copy paste code as the uni-code (or other encoding) strings might cause an issue..

Answer 4

The access being denied is probably to the Windows file system not to the MySQL database; try redirecting the output file to a location where your account is allowed to create files.

Answer 5

Doing without the -u and -p worked for me (when I was logged in as root):

mysqldump --opt mydbname > mydbname.sql

Answer 6

Go to Start-> All Programs -> Accessories right click on Command Prompt click on Run as administrator

In the command prompt using CD command Go to MySQL bin folder and run the below command

mysqldump --user root --password=root --all-databases>dumps.sql

it will create dumps.sql file in the bin folder itself.