mysqldump Error 1045 Access denied despite correct

问题:

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.

回答1:

This worked for me

mysqldump -u root -p mydbscheme > mydbscheme_dump.sql

after issuing the command it asks for a password:

Enter password:

entering the password will make the dump file.

回答2:

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

回答3:

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.

回答4:

Try to remove the space when using the -p-option. This works for my OSX and Linux mysqldump:

mysqldump -u user -ppassword ...

回答5:

Put The GRANT privileges:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%' IDENTIFIED BY 'password';

回答6:

Don't enter the password with command. Just enter,

mysqldump -u (username) -p (db_name) > (backup_file).sql

Then you will get a prompt to enter password.

回答7:

Access dined problem solved when I run command prompt in Administrator mode.

Go to Start-> All Programs -> Accessories right click on Command Prompt clickc on Run as.. Select The Following User select administrator username from select option enter password if any click OK button.

Example 1: For entire database backup in mysql using command prompt.

In Windows 7 and 8

C:\Program Files <x86>>\MySQL\MySQL Server 5.5\bin>mysqldump test -u root -p >testDB.sql
Enter Password: *********

In Windows xp

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump test -u root -p >testDB.sql
Enter Password: *********

It asks password for credentials enter password and click on Enter button.

Example 2: For specific table backup / dump in mysql using command prompt.

In Windows 7 and 8

C:\Program Files <x86>>\MySQL\MySQL Server 5.5\bin>mysqldump test -u root -p images>testDB_Images.sql
Enter Password: *********

In Windows xp

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump test -u root -p images>testDB_Images.sql
Enter Password: *********

Dumpt file will be created under folder

In windows xp

C:\Program Files\MySQL\MySQL Server 5.5\bin

In windows 7 and 8

C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin

Note: Check MySQL installation folder in Windows 7, 8 while run in command prompt. If MySQLWorkbench is 32 bit version it is installed in Program Files (x86) folder other wise Program Files folder.

回答8:

mysqldump -h hostname -u username -P port -B database --no-create-info -p > output.sql

I think you should specify the args

回答9:

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

mysqldump --opt mydbname > mydbname.sql

回答10:

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

回答11:

mysqldump -u (user) -p(passwd) -h (host_or_IP) database_to_backup > backup_file.sql

example:

mysqldump -u god -pheaven -h 10.0.10.10 accounting > accounting_20141209.sql

this would create sql backup file for the accounting database on server 10.0.10.10. Sometimes your error is seen when localhost is not in config. Designating ip of server may help.

回答12:

I just ran into this after a fresh install of MySQL 5.6.16.

Oddly, it works without the password specified or flagged:

mysqldump -u root myschema mytable > dump.sql

回答13:

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..

回答14:

I had to remove the single ticks after the password flag:

--password=mypassword

and NOT

--password='mypassword'

回答15:

I had the same error for last 2 days. Tried bunch of things. Nothing worked.
But this did work:
Create another user. Grant it everything.
mysqldump -u new_user db_name > db_name.sql //no error

回答16:

I discovered a running apache process acessing the MYSQL causing this error. So I suggest to ensure that all processes which might interact with the DB are shutdown beforehand.

回答17:

Mysql replies with Access Denied with correct credentials when the mysql account has REQUIRE SSL on

The ssl_ca file (at a minimum) had to be provided in the connection paramiters.

Additional ssl parameters might be required and are documented here: http://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html

---

Also posted here https://stackoverflow.com/a/39626932/1695680

回答18:

I had the problem that there were views that had a bad "DEFINER", which is the user that defined the view. The DEFINER used in the view had been removed some time ago as being "root from some random workstation".

Check whether there might be a problem by running:

USE information_schema; 
SELECT DEFINER, SECURITY_TYPE FROM views;

I modified the DEFINER (actually, set the DEFINER to root@localhost and the SQL SECURITY value to INVOKER so the view is executed with the permissions of the invoking user instead of the defining user, which actually makes more sense) using ALTER VIEW.

This is tricky as you have to construct the appropriate ALTER VIEW statement from information_schema.views, so check:

• Modify DEFINER on many views
• MySQL error 1449: The user specified as a definer does not exist

回答19:

For MAMP PRO users (or anyone who's mysql is in a weird location) be prepared to specify the mysql full path from the boonies and also specify full path to your user local folder where you want to dump the file or you'll get the "permission denied error"..

Following worked for me after 3 hours of research:

/Applications/MAMP/Library/bin/mysqldump  -u root -proot YOUR_DB > /Users/YOUR_USER/yourdump2.sql

回答20:

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.