The short version
mysqldump error 1045 means the server refused the login itself: wrong or missing password, or no account matching that user name from the host you are connecting from. Error 1044 comes after a successful login and means the account lacks a privilege the dump needs, most often LOCK TABLES, which mysqldump uses by default. For InnoDB tables, add --single-transaction and the lock is never taken; add --no-tablespaces and the PROCESS privilege is no longer needed. A full dump of one database needs SELECT, SHOW VIEW, TRIGGER, EVENT on that database, plus LOCK TABLES and global PROCESS if you keep the defaults.
How I tested this
Everything below ran against MySQL 8.4.11 in a local container on 127.0.0.1:13306, with the Oracle mysqldump Ver 9.3.0 client from Homebrew. Where I also tried the MariaDB 11.8 build of mysqldump, I say so. The test database held an InnoDB table, a MyISAM table, a view, a trigger, an event and a stored procedure, and I created one account per scenario. Docker Desktop forwards the port, so the server saw every TCP connection as coming from 192.168.65.1, which shows up in the messages. On a real server you will see your client's address instead.
mysqldump error 1045: access denied for user
The server error reference gives 1045 as ER_ACCESS_DENIED_ERROR, Access denied for user '%s'@'%s' (using password: %s). The last part tells you whether a password was sent at all. The access-denied troubleshooting page says the same: using password: NO means you tried to log in without one.
A wrong password and no password, as mysqldump prints them:
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_any -pwrong mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'md1045_any'@'192.168.65.1' (using password: YES) when trying to connect
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_any mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: Got error: 1045: Access denied for user 'md1045_any'@'192.168.65.1' (using password: NO) when trying to connect
A user name that does not exist on the server produced exactly the same line, so the message does not tell you which half is wrong. Check the user table as an administrator, as the troubleshooting page suggests.
Two ways to send the wrong password without noticing:
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_dollar -p"Pa$$word1" mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'md1045_dollar'@'192.168.65.1' (using password: YES) when trying to connect
The real password was Pa$$word1. Inside double quotes the shell replaced $$ with its process ID before mysqldump ever saw it. The same command with single quotes logged in fine.
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_lean -p Dump-pass-3 mysqldump_errors_1044_and_1045_access_denied
Enter password:
mysqldump: Got error: 1045: Access denied for user 'md1045_lean'@'192.168.65.1' (using password: NO) when trying to connect
With a space after -p, mysqldump prompts, and Dump-pass-3 is taken as a database name. MySQL's connection docs state that there must be no space between -p and the password. I pressed Enter at the prompt, hence using password: NO.
One more: if 1045 appears even when you typed no password, a stale password in an option file such as ~/.my.cnf is being picked up. The troubleshooting page suggests running with --no-defaults to rule that out.
1045 because the account is for another host
MySQL accounts are 'user'@'host' pairs. Connection verification accepts a login only if the client host and user name match a row in the user table, and uses the first matching row after sorting, most specific host first. I created 'md1045_local'@'localhost' with a correct password and connected over TCP:
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_local -pDump-pass-1 mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'md1045_local'@'192.168.65.1' (using password: YES) when trying to connect
$ mysqldump -h localhost --protocol=TCP -P 13306 -u md1045_local -pDump-pass-1 mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'md1045_local'@'192.168.65.1' (using password: YES) when trying to connect
The password was right; the host was not. An account for localhost matches connections over the Unix socket. Per the same page, socket connections "match a host part of localhost", while TCP connections are matched by the client's address, so 'u'@'localhost' does not cover 127.0.0.1 and 'u'@'127.0.0.1' does not cover the socket.
On Unix, -h localhost does not even use TCP. The connecting page says the client connects through the socket file for localhost, and that a port option is then ignored. My run confirms it, since my server was in a container with no local socket:
$ mysqldump -h localhost -P 13306 -u md1045_local -pDump-pass-1 mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
That is error 2002, covered in mysqldump Error 2002: Can't Connect Through Socket. The fix for the host mismatch is an account for the host you really come from. If you are unsure, log in with any working account and run SELECT USER(), CURRENT_USER();. USER() is what you asked for, CURRENT_USER() is the account the server matched. Mine printed root@192.168.65.1 and root@%.
Authentication plugin errors that look like 1045
MySQL 8.4 accounts default to caching_sha2_password. The plugin page says the first login after account creation, a password change or FLUSH PRIVILEGES must use TLS, a socket, or RSA key exchange. With TLS turned off I got:
$ mysqldump -h 127.0.0.1 -P 13306 -u md1045_any -pDump-pass-1 --ssl-mode=DISABLED --no-tablespaces --single-transaction mysqldump_errors_1044_and_1045_access_denied > /dev/null
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 2061: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. when trying to connect
Adding --get-server-public-key, the option that page documents, let the login through. Leaving --ssl-mode at its default works too.
Old guides that say "create the user with mysql_native_password" fail on 8.4:
mysql> CREATE USER 'md1045_native'@'%' IDENTIFIED WITH mysql_native_password BY 'x';
ERROR 1524 (HY000) at line 1: Plugin 'mysql_native_password' is not loaded
The native authentication page says the plugin is "disabled by default in MySQL 8.4, and removed as of MySQL 9.0.0". A server admin can turn it back on with --mysql-native-password=ON, per the same page, but new accounts are better left on the default plugin.
mysqldump error 1044 when using LOCK TABLES
Once the login works, the account still needs privileges. I gave md1045_any only SELECT on the database and ran a default dump:
$ mysqldump --defaults-extra-file=any.cnf mysqldump_errors_1044_and_1045_access_denied > d1.sql
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
mysqldump: Got error: 1044: Access denied for user 'md1045_any'@'%' to database 'mysqldump_errors_1044_and_1045_access_denied' when using LOCK TABLES
Note the account in a 1044 message is the matched one, 'md1045_any'@'%', where a 1045 shows the client address. That is a sign the login itself worked.
The mysqldump privileges paragraph lists LOCK TABLES "if the --single-transaction option is not used". --lock-tables is part of --opt, which is on by default. For InnoDB tables the same page calls --single-transaction "a much better option than --lock-tables because it does not need to lock the tables at all": it starts a REPEATABLE READ transaction and reads a consistent snapshot.
$ mysqldump --defaults-extra-file=any.cnf --no-tablespaces mysqldump_errors_1044_and_1045_access_denied > d2.sql
mysqldump: Got error: 1044: Access denied for user 'md1045_any'@'%' to database 'mysqldump_errors_1044_and_1045_access_denied' when using LOCK TABLES
$ mysqldump --defaults-extra-file=any.cnf --no-tablespaces --single-transaction mysqldump_errors_1044_and_1045_access_denied > d3.sql
mysqldump: Couldn't execute 'show create table `big_orders`': SHOW VIEW command denied to user 'md1045_any'@'192.168.65.1' for table 'big_orders' (1142)
The lock error is gone (the next one is the view, below). The catch: the docs say only InnoDB tables are dumped consistently with --single-transaction, and MyISAM or MEMORY tables "may still change state". My MyISAM audit_log table dumped without complaint, which is exactly why you would not notice. If MyISAM tables still take writes during the dump, either grant LOCK TABLES or convert them first.
Error 1227: the PROCESS privilege and --no-tablespaces
The first line in the default run is the text of error 1227, ER_SPECIFIC_ACCESS_DENIED_ERROR, in the error reference. mysqldump prints it without the number and carries on to the next step. The privilege list in the mysqldump docs requires PROCESS only when --no-tablespaces is not used, and that option "suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements", which a dump of ordinary InnoDB tables for a migration does not need. The privileges page says PROCESS gives access to information about all threads, "even those belonging to other users", so I add the option rather than grant it.
Views, triggers, events and routines
Each object type has its own privilege, and they do not all fail the same way.
| Object | Privilege (per the mysqldump docs) | What I saw without it |
|---|---|---|
| View | SHOW VIEW |
error 1142, dump stops |
| Trigger | TRIGGER on the table |
exit 0, trigger missing from the file |
Event (--events) |
EVENT on the database |
error 1044 on show events |
Routine (--routines) |
global SELECT |
MariaDB client: exit 0, procedure missing |
The trigger row is the dangerous one. After granting SHOW VIEW, the dump finished with exit code 0, and grep -c TRIGGER on the file returned 0. Nothing on stderr. A target that relies on that trigger would just behave differently.
The event failure, a second kind of 1044:
$ mysqldump --defaults-extra-file=any.cnf --no-tablespaces --single-transaction --events mysqldump_errors_1044_and_1045_access_denied > d5.sql
mysqldump: Couldn't execute 'show events': Access denied for user 'md1045_any'@'%' to database 'mysqldump_errors_1044_and_1045_access_denied' (1044)
For routines, the mysqldump page asks for global SELECT. The privileges page describes SHOW_ROUTINE as a narrower grant that "enables an account to back up stored routines without requiring a broad privilege". With the MariaDB client, the procedure was silently absent without it and present after GRANT SHOW_ROUTINE ON *.*.
I could not test routines with the Oracle 9.3 client. Against the 8.4 server it failed for root as well, so this has nothing to do with privileges:
mysqldump: Couldn't execute 'SELECT LIBRARY_NAME FROM INFORMATION_SCHEMA.LIBRARIES WHERE LIBRARY_SCHEMA = 'mysqldump_errors_1044_and_1045_access_denied' ORDER BY LIBRARY_NAME': Unknown table 'LIBRARIES' in information_schema (1109)
For routines I would use a mysqldump from the server's own release series; mine was a 9.3 client against an 8.4 server.
The minimal GRANT for a full mysqldump
For the default options plus --events, this account dumped everything, exit 0, with both tables, the view, the trigger and the event in the file:
CREATE USER 'md1045_dump'@'%' IDENTIFIED BY '...';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON mysqldump_errors_1044_and_1045_access_denied.* TO 'md1045_dump'@'%';
GRANT PROCESS ON *.* TO 'md1045_dump'@'%';
-- only if you dump routines:
GRANT SHOW_ROUTINE ON *.* TO 'md1045_dump'@'%';
The leaner version, which is what I use for InnoDB databases:
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON mysqldump_errors_1044_and_1045_access_denied.* TO 'md1045_lean'@'%';
$ mysqldump --defaults-extra-file=lean.cnf --single-transaction --no-tablespaces --events mysqldump_errors_1044_and_1045_access_denied > l1.sql; echo "exit $?"
exit 0
The docs add one more case: RELOAD or FLUSH_TABLES with --single-transaction when both gtid_mode=ON and gtid_purged=ON|AUTO. My server had gtid_mode OFF, so I did not hit it. Replace % with the host you dump from. The other options worth setting for a migration are in mysqldump Options for a Clean Migration, and if the dump starts but dies halfway, see mysqldump Error 2013: Lost Connection During Query.
Keep the password off the command line
Every -p run above printed Using a password on the command line interface can be insecure. The password security guidelines explain that the password can be visible to ps for other users, and recommend an option file readable only by you (mode 400 or 600). This is the file I used:
$ cat any.cnf
[client]
host=127.0.0.1
port=13306
user=md1045_any
password=Dump-pass-1
$ chmod 600 any.cnf
$ mysqldump --defaults-extra-file=any.cnf mysqldump_errors_1044_and_1045_access_denied > mysqldump_errors_1044_and_1045_access_denied.sql
--defaults-extra-file must come before other options on the command line, and a relative path is read from the current directory.
The other way is mysql_config_editor, which stores credentials in an obfuscated .mylogin.cnf. The page warns the obfuscation "is not likely to deter a determined attacker". I pointed MYSQL_TEST_LOGIN_FILE at a scratch file so my real home directory was untouched:
$ mysql_config_editor set --login-path=qa84 --host=127.0.0.1 --port=13306 --user=md1045_lean --password
Enter password:
$ mysql_config_editor print --login-path=qa84
[qa84]
user = "md1045_lean"
password = *****
host = "127.0.0.1"
port = 13306
$ mysqldump --login-path=qa84 --single-transaction --no-tablespaces --events mysqldump_errors_1044_and_1045_access_denied > lp.sql; echo "exit $?"
exit 0
Sources
- MySQL 8.4: mysqldump, privileges and options
- MySQL 8.4 server error message reference
- MySQL 8.4: Causes of access-denied errors
- MySQL 8.4: Access control, stage 1: connection verification
- MySQL 8.4: Connecting to the MySQL server
- MySQL 8.4: Privileges provided by MySQL
- MySQL 8.4: Caching SHA-2 pluggable authentication
- MySQL 8.4: Native pluggable authentication
- MySQL 8.4: End-user guidelines for password security
- MySQL 8.4: Command-line options that affect option-file handling
- MySQL 8.4: mysql_config_editor