Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

mysqldump says access denied (1045) with a password that works elsewhere

ErrorAccess denied for user 'root'@'localhost' (using password: YES)

Short answer

Error 1045 means the server matched your connection to an account and rejected it. With mysqldump the usual reasons are a different user@host match than you expect, a password typed into the command line in the wrong form, or an option file that supplies other credentials. Compare CURRENT_USER() and run mysqldump with --print-defaults.

  • Applies to MySQL 8.0, MySQL 8.4 LTS, mysqldump 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print what mysqldump reads from option files. A [client] or [mysqldump] entry can override the user or password you think you passed.

    mysqldump --print-defaults
  2. 2Match what you see, then jump to its fix

    1. 1The same user and password work in one place but not another, or the user@host in the error is not the account you created.The server matches a different account than the one you grantedFix
    2. 2The message says (using password: NO), or the password is only wrong in scripts.The password is not reaching mysqldump as you typed itFix
    3. 3--print-defaults shows a user or password you did not pass, or the error names a different user than your command.An option file supplies other credentialsFix

What does the error look like?

  • Access denied for user 'root'@'localhost' (using password: YES)
  • Access denied for user 'backup'@'localhost' (using password: NO)

Cause 1 of 3

The server matches a different account than the one you granted

1Check

An account is a user name plus a client host. Socket connections count as localhost, and a TCP connection to 127.0.0.1 can match a different row. A blank-user (anonymous) row for the host sorts before a named one and can capture your login. Log in with the mysql client the same way mysqldump does and print the matched account.

mysql -u backup -p -h 127.0.0.1 -e "SELECT CURRENT_USER();"
mysql -u root -p -e "SELECT User, Host FROM mysql.user ORDER BY User, Host;"

2Fix

Make the transport explicit and create the account for the host that the server sees. Use --protocol to choose socket or TCP, or --host and --port, so both tools connect the same way.

mysqldump --protocol=TCP -h 127.0.0.1 -P 3306 -u backup -p mydb > mydb.sql

3Verify

The dump file is written and is not empty.

ls -lh mydb.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The password is not reaching mysqldump as you typed it

1Check

A password given with -p must follow the option with no space (-ppassword), and a bare -p makes the client prompt instead. The manual also warns that a password on the command line is insecure.

mysqldump -u backup -p mydb > mydb.sql

2Fix

Let the client prompt for the password, or keep it in the [client] group of a private option file with access mode 600.

# ~/.my.cnf, then: chmod 600 ~/.my.cnf
[client]
user=backup
password=your-password

mysqldump mydb > mydb.sql

3Verify

mysqldump runs with no password on the command line.

ls -lh mydb.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

An option file supplies other credentials

1Check

mysqldump reads the [client] and [mysqldump] groups of the option files, so a stale entry wins over what you forgot to type.

mysqldump --print-defaults

2Fix

Correct or remove the entry, or run once with --no-defaults to ignore option files, or point at one file with --defaults-file.

mysqldump --no-defaults -u backup -p mydb > mydb.sql

3Verify

The dump succeeds with --no-defaults, which proves the option file was the cause.

ls -lh mydb.sql

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep backup credentials in a protected option file, not in shell scripts.
  • Create the backup account for the exact host the server sees.
  • Run the backup with --single-transaction --no-tablespaces so the account needs fewer privileges.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.