64

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.

radbyx
  • 8,605
  • 18
  • 75
  • 119
DdlyHeadshot
  • 641
  • 1
  • 5
  • 4
  • 13
    did you try with `-p` option without applying the password? then you should be prompted for the password. – rubo77 Nov 18 '13 at 23:06
  • Are you able to connect to the database with mysql client using the same credentials? – Kacer Nov 18 '13 at 23:06
  • Same credentials, identical login part of the command-line to that used with mysql.exe. password is specified correctly as it works with mysql.exe. – DdlyHeadshot Nov 18 '13 at 23:15
  • Check the password specified, and try to add --host option. – Devart Nov 19 '13 at 06:39
  • Tried using `-h`, that didn't work. The password is correct as it's the same as that used for MySQL.exe and that works perfectly! – DdlyHeadshot Nov 19 '13 at 08:25
  • Have you connected as 'root'@'localhost' and specified --host=localhost? Check carefully if this user with specified password exists. – Devart Nov 19 '13 at 10:27
  • Tried that and the root password definitely exists as I can use the same credentials to log into mysql.exe perfectly. – DdlyHeadshot Nov 19 '13 at 12:50
  • It is very strange. If you can connect using mysql.exe, then you can connect from mysqldump.exe. Try another MySQL tool/client. – Devart Nov 20 '13 at 13:39
  • I know that's how it should be but for some reason mysql.exe works and mysqldump.exe doesn't! I know I'm not the only person that has experienced something like this too, it's just that none of the fixes that worked for them work for me... – DdlyHeadshot Nov 20 '13 at 14:14
  • Anyone know if there's any way to create the dumpfile text from within MySQL itself? If so, I could probably just pipe the output from that instead... – DdlyHeadshot Nov 20 '13 at 15:00
  • @Devart Is there a 3rd-party MySQL Dump utility then? A quick Google doesn't seem to find any evidence of one. – DdlyHeadshot Nov 20 '13 at 15:06
  • Yes there is, for example you can use [dbForge Studio for MySQL](http://www.devart.com/dbforge/mysql/studio/) - Backup feature, command line is supported. – Devart Nov 20 '13 at 15:13
  • That works great, thanks! I'd still like to get this sorted but at least it's less desperate now. :D – DdlyHeadshot Nov 24 '13 at 20:16
  • I think it is -P option that gives port of mysql instance... so this answer is great: http://stackoverflow.com/a/27288057/11374 – spinodal Jan 09 '15 at 08:35
  • Got exec error message to display in PHP via: http://stackoverflow.com/a/21482379/1673876 mysqldump was giving error 1045 when using a username and password: 'user'@'localhost' denied even though 127.0.0.1 was specified as host name. The problem seems to be with the host specified in the phpMyAdmin user privileges table. Experiment with 127.0.0.1, localhost, % as user host privileges. When I use the user root with no password it works fine, so the issue seems to be with the way phpMyAdmin and mysqldump handle the host specified. – i_a Jan 14 '16 at 16:32
  • Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See [What topics can I ask about here](http://stackoverflow.com/help/on-topic) in the Help Center. Perhaps [Super User](http://superuser.com/) or [Unix & Linux Stack Exchange](http://unix.stackexchange.com/) would be a better place to ask. Also see [Where do I post questions about Dev Ops?](http://meta.stackexchange.com/q/134306). – jww Apr 02 '16 at 01:01
  • Also see [How to have MySQL entitle the root user?](http://superuser.com/q/1060360) on Super User. It attempts to avoid resetting passwords. – jww Apr 02 '16 at 11:27
  • @jww dba.stackexchange.com is the right place I reckon. – David Tonhofer Mar 12 '18 at 11:34

28 Answers28

71

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.

Boris Pavlović
  • 58,387
  • 26
  • 115
  • 142
32

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
Johnathan Elmore
  • 1,727
  • 1
  • 17
  • 25
  • 10
    In above don't use space between -p switch and actual db password e.g. `mysqldump -h database.example.com -u mydbuser -p'mydatabase' --single-transaction > /home/mylinuxuser/mydatabase.sql` Note: use the single quotes if the password contain special characters $()# etc. – Naveed Anwar Feb 11 '16 at 02:11
  • 5
    @NaveedAnwar mydatabase is the database being dumped, not the password. – Johnathan Elmore Jun 10 '16 at 00:01
  • 1
    This worked for me. The original error message said, "Access denied for user 'username'@'localhost' (using password: YES) when using LOCK TABLES." – Raffi Jun 15 '20 at 01:02
18

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.

user3761176
  • 181
  • 1
  • 2
18

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

mysqldump -u user -ppassword ...
Andree Wendel
  • 233
  • 2
  • 5
13

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

Someone
  • 131
  • 1
  • 2
  • 1
    This got me on the right track, I had a hash symbol in my password. Backslash didn't do it for me, but I put my password in single quotes and all good. – Sean Connolly Jul 25 '20 at 04:37
10

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.

hariK
  • 1,842
  • 9
  • 11
8

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.

UdayKiran Pulipati
  • 6,053
  • 7
  • 60
  • 84
7

Put The GRANT privileges:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%' IDENTIFIED BY 'password';
rink.attendant.6
  • 36,468
  • 57
  • 89
  • 143
Anand Mishra
  • 374
  • 2
  • 12
  • 2
    Thanks, This worked for me. I had to replace % with localhost. – Abhijeet Nagre Nov 23 '15 at 14:09
  • Worked for me. GRANT as, GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost' IDENTIFIED BY 'password'; followed by mysqldump as below, mysqldump --socket=$HOME/apps/mariadb/mysql.sock -u username -p mydb --single-transaction > db_backup.sql – DineshM Jan 11 '17 at 05:19
5

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

I think you should specify the args

wcc526
  • 3,157
  • 2
  • 28
  • 28
  • port is the key trick I think. because mysqldump is working directly on the default port... :) thanks – spinodal Jan 09 '15 at 08:33
  • There was another mysqld sneakily occypying port 3306 for me, and mysqldump was trying to access that one rather than the one from its own installation – Dylan Dec 21 '15 at 20:10
4

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

mysqldump --opt mydbname > mydbname.sql
zylstra
  • 656
  • 7
  • 20
1

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

Hal50000
  • 559
  • 1
  • 5
  • 16
1

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.

McLinux
  • 11
  • 1
1

In my case, I could access correctly with mysql.exe but not with mysqldump.exe.

The problem was the port for my connection was not the default one (3306) and I had to put the mysqldump port work with (-P3307)

mysqldump -u root -p -P3307 my_database > /path/backup_database

Raul
  • 56
  • 2
1

Putting -p as the first option worked for me on Windows Server 2012R2 (in cmd.exe as Admin).

mysqldump.exe –p --user=root  --databases DBname --result-file=C:\DBname.sql
Hank Killinger
  • 21
  • 2
0

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

MarmiK
  • 5,320
  • 6
  • 34
  • 44
Roland
  • 1
0

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

--password=mypassword

and NOT

--password='mypassword'
Max
  • 975
  • 3
  • 13
  • 21
0

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

CoR
  • 3,468
  • 5
  • 31
  • 40
0

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.

Florian Storck
  • 489
  • 7
  • 19
0

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

Community
  • 1
  • 1
ThorSummoner
  • 12,194
  • 11
  • 114
  • 129
0

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:

David Tonhofer
  • 12,954
  • 4
  • 44
  • 46
0

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
Robert Sinclair
  • 2,911
  • 25
  • 29
0

For me it worked when I omitted the password.

So mysqldump -u user dbname > dump.sql

refex
  • 195
  • 6
  • 10
0

Tried most of the above with no joy. Looking at my password, it had characters that might confuse a parser. I wrapped the password in quotes and the error was resolved. -p"a:@#$%^&+6>&FAEH"

Using 8.0

Mark
  • 41
  • 3
0

If you want to create a mysql data dump, you can use mysqldump command. Following command will create a sql file called xxx.sql at the same location from where this command is run. xxx.sql will have all the necessary sqls to replicate exactly same db schema in any other mysql database.

Command is : mysqldump -u root -ppassword --databases database Name you want to import > xxx.sql

Here root is the mysql root user and password is THIS root user's password.

EXAMPLE: If root user password is hello, database name to export is regdb and xxx.sql is the file where you want to export this regdb, command would be like:

mysqldump -u root -phello --databases regdb > xxx.sql

Note: xxx.sql is the file name where this db will get dumped.

vks
  • 11
  • 2
0

This solution might be one of the last to try/least likely to be the culprit, but this was my problem...

My problem was that the directory I was trying to dump to needed admin privileges to write to and that's what was causing the mysqldump command to return "Access Denied".

I set the dump file path to my desktop dir and then it worked.

This was on Windows.

Dan.
  • 377
  • 1
  • 3
  • 15
0

I was having the same issue, for 30min! I found that I was using _p instead of -p, the terminal font confused me!

Mohamed Elbahja
  • 472
  • 1
  • 7
  • 9
0

I had the same error. Only occurred after moving from my normal work PC to a PC at a different location.

I had to add my public IP ho address to Remote MySQL in my CPanel at my host site

DeShannon Shann McGee
  • 161
  • 1
  • 4
-2

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.

user4669696
  • 1