Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

mysqldump says access denied even though the user has ALL on the database

ErrorAccess denied; you need (at least one of) the PROCESS privilege(s) for this operation

Short answer

GRANT ALL ON db.* gives only database-level privileges. mysqldump may also need global ones: PROCESS to dump tablespaces, RELOAD for some options, and SHOW VIEW, TRIGGER or LOCK TABLES per database. Read which privilege the error names, then grant it or use --no-tablespaces or --single-transaction.

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

Fix it fast

  1. 1Find out which cause you have

    List what the account really has, and read the privilege the error names. Compare it with what mysqldump needs: SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS.

    SHOW GRANTS FOR 'backup'@'localhost';
  2. 2Match what you see, then jump to its fix

    1. 1Access denied; you need (at least one of) the PROCESS privilege(s) for this operationPROCESS is missing, and mysqldump dumps tablespaces by defaultFix
    2. 2Error 1044 or 1142 appears part-way through the dump, at a table, a view or a trigger.LOCK TABLES, SHOW VIEW or TRIGGER is not granted on that databaseFix
    3. 3The dump only fails when you add --flush-logs or --source-data, or the error names RELOAD.An option needs RELOAD or another global privilegeFix

What does the error look like?

  • Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
  • ERROR 1044 (42000): Access denied for user 'backup'@'localhost' to database 'shop'

Cause 1 of 3

PROCESS is missing, and mysqldump dumps tablespaces by default

1Check

mysqldump needs PROCESS unless --no-tablespaces is used. PROCESS is a global (server administration) privilege, so GRANT ALL ON shop.* never includes it.

SHOW GRANTS FOR 'backup'@'localhost';

2Fix

If you do not need tablespace information in the dump (most schemas do not), add --no-tablespaces. Otherwise grant PROCESS globally.

mysqldump --no-tablespaces -u backup -p shop > shop.sql

GRANT PROCESS ON *.* TO 'backup'@'localhost';

3Verify

The dump finishes and the file contains the CREATE TABLE statements.

grep -c 'CREATE TABLE' shop.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

LOCK TABLES, SHOW VIEW or TRIGGER is not granted on that database

1Check

mysqldump needs SELECT for tables, SHOW VIEW for views, TRIGGER for triggers, and LOCK TABLES unless --single-transaction is used. A user created with only SELECT and INSERT lacks them.

SHOW GRANTS FOR 'backup'@'localhost';

2Fix

Grant the missing database-level privileges, or use --single-transaction so LOCK TABLES is not needed.

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON shop.* TO 'backup'@'localhost';
-- or, for InnoDB tables:
-- mysqldump --single-transaction --no-tablespaces -u backup -p shop > shop.sql

3Verify

Run the dump again; no access error appears and the file is complete.

tail -n 3 shop.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

An option needs RELOAD or another global privilege

1Check

RELOAD is required for the --flush-logs and --source-data options. --source-data also needs the binary log enabled, and --tab needs the FILE privilege. GRANT ALL on a database never gives global privileges.

SHOW GRANTS FOR 'backup'@'localhost';

2Fix

Grant the global privilege on *.*, or drop the option if you do not need it.

GRANT RELOAD ON *.* TO 'backup'@'localhost';

3Verify

Repeat the dump with the same options.

tail -n 3 shop.sql

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create a dedicated backup account with exactly the privileges mysqldump lists, instead of reusing an application user.
  • Remember that GRANT ALL ON db.* is database-level only: it does not include global privileges such as PROCESS, RELOAD or FILE.
  • Test the backup command as that account after every privilege change.

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.