Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

mysqldump error 1044 'Access denied … when using LOCK TABLES' and the PROCESS privilege error

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

mysqldump: Got error: 1044: Access denied for user 'x'@'y' to database 'z' when using LOCK TABLES means the account running the dump does not hold the LOCK TABLES privilege on that database. A second, newer failure, an error about the PROCESS privilege, appears from MySQL 8.0.21 because mysqldump now reads tablespace information. Both are privilege gaps in the backup account, not damage to the data, and both can be fixed without handing the account full administrative rights.

What the error means

Error 1044 is ER_DBACCESS_DENIED_ERROR, SQLSTATE 42000, with the message template Access denied for user '%s'@'%s' to database '%s' (MySQL 8.4 Error Reference). The when using LOCK TABLES suffix is added by mysqldump itself. It appears in a 2006 bug report as mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES (MySQL bug #21527). That bug is closed, with a fix noted for 5.0.25 and 5.1.12, so on a current server, check the account's grants first.

mysqldump locks tables by default in some option combinations. The manual says --lock-tables locks all tables of each dumped database before dumping, using READ LOCAL so MyISAM tables still accept concurrent inserts, and that "some options, such as --opt, automatically enable --lock-tables". It adds that --skip-lock-tables at the end of the option list overrides this (mysqldump, MySQL 8.4).

The privilege requirements are listed in one paragraph of that page: SELECT for dumped tables, SHOW VIEW for views, TRIGGER for triggers, LOCK TABLES if --single-transaction is not used, PROCESS if --no-tablespaces is not used, and RELOAD or FLUSH_TABLES with --single-transaction if both gtid_mode=ON and gtid_purged=ON|AUTO.

The PROCESS error from 8.0.21

The MySQL 8.0.21 release notes (13 July 2020) record an incompatible change: access to INFORMATION_SCHEMA.FILES now requires the PROCESS privilege, and because mysqldump reads tablespace information from that table it now needs PROCESS too. Users who do not need tablespace information can use --no-tablespaces (Changes in MySQL 8.0.21). The server-side error for a missing privilege of this kind is 1227, ER_SPECIFIC_ACCESS_DENIED_ERROR: Access denied; you need (at least one of) the %s privilege(s) for this operation.

Likely causes

Cause What you see How to confirm
Account has no LOCK TABLES on the database Error 1044 naming the database, "when using LOCK TABLES" SHOW GRANTS for the account shows no LOCK TABLES for that database
Account has no PROCESS (8.0.21 or later) An error 1227 style message about the PROCESS privilege SHOW GRANTS shows no global PROCESS; the dump succeeds with --no-tablespaces
Missing SHOW VIEW, TRIGGER or EVENT Access-denied errors on views, triggers or events Grants lack the privilege; error 1142 uses the form %s command denied to user '%s'@'%s' for table '%s'
--routines without enough rights Routines not dumped, or error 1370 (%s command denied ... for routine '%s') The manual says --routines requires the global SELECT privilege
--source-data or GTID dump without RELOAD Access denied on a flush or lock step The manual lists RELOAD for --source-data and, with --single-transaction under GTIDs, RELOAD or FLUSH_TABLES
Grants made for a different host part of the account The error names user@host that differs from the account you granted The error message shows the account actually connecting

Prove the cause before changing anything

Everything here is read-only.

  1. Read the account named in the error. The message shows 'user'@'host'. That exact pair is what needs the privilege.
  2. Show its grants. SHOW GRANTS FOR 'dumpuser'@'localhost'; lists them. The manual notes the statement requires the SELECT privilege on the mysql system schema, except to display your own privileges (SHOW GRANTS, MySQL 8.4).
  3. Compare with the options in your dump command. Which of --single-transaction, --lock-tables, --opt, --routines, --events, --source-data and --no-tablespaces are in the script or wrapper (cron job, panel, backup agent)? Each one changes what is needed.
  4. Check the privilege scope. LOCK TABLES, EVENT, TRIGGER and SHOW VIEW can be granted per database; PROCESS and RELOAD are global-only administrative privileges (Privileges Provided by MySQL, 8.4). A grant on shopdb.* will never cure a missing PROCESS.

Fix

These statements are taken from the documentation and have not been run. Run them as an administrative account, and keep a record of the account's grants before you change them.

Choose the consistency method first

--single-transaction and --lock-tables are mutually exclusive, because LOCK TABLES implicitly commits any pending transaction. They give different results:

Option Consistency Needs LOCK TABLES
--single-transaction Sets REPEATABLE READ and issues START TRANSACTION; consistent for InnoDB tables without blocking applications. MyISAM and MEMORY tables dumped this way "may still change state" No
--lock-tables Locks each database's tables in turn; the manual says tables in different databases may be dumped in completely different states Yes

For InnoDB, the manual calls --single-transaction "a much better option" than --lock-tables because it does not lock the tables at all. While it runs, no other connection should issue ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE, or the dump can be wrong or fail. For large tables the manual suggests adding --quick. If your schema mixes in MyISAM tables, --single-transaction does not make them consistent; that is a reason to keep LOCK TABLES instead, or to convert them.

Option A: add the missing privileges (least privilege)

For a dump of one database:

GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON shopdb.* TO 'dumpuser'@'localhost';
GRANT PROCESS ON *.* TO 'dumpuser'@'localhost';

GRANT ... ON db_name.* is the database-level form in the manual's examples, and LOCK TABLES can be granted at database level. If the dump uses --source-data, add RELOAD and REPLICATION CLIENT: --source-data runs SHOW BINARY LOG STATUS (mysqldump page), which needs REPLICATION CLIENT (privileges page). For --routines the manual requires global SELECT; the privileges page describes SHOW_ROUTINE as a narrower alternative that lets an account back up stored routines without a broad privilege.

Option B: change the dump command

  • If you have InnoDB only, add --single-transaction and drop --lock-tables. The account then does not need LOCK TABLES.
  • If you do not need tablespace definitions in the dump, add --no-tablespaces; the account then does not need PROCESS. That option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements, so confirm you are not relying on them.
  • --skip-lock-tables removes the locking step but gives no consistency across MyISAM tables. Treat it as a diagnostic, not a standing setting.

Do not respond to this error by giving the backup account ALL PRIVILEGES or SUPER. The manual notes that any static global privilege lets a user see all database names, and a broad account is a larger target if its password leaks.

Example (illustrative): a nightly job on SQL01 stops after the 8.0.21 upgrade with an error about the PROCESS privilege, then, after that is fixed, with error 1044 on one reporting database that was added later. The administrator finds the account holds SELECT only on shopdb, grants the five database-level privileges above, and grants global PROCESS. The names are invented.

Confirm the fix

  1. Run the dump to a scratch location and check that mysqldump exits without an error message.
  2. Open the output and confirm it contains what you expect: CREATE EVENT, CREATE PROCEDURE and CREATE FUNCTION statements if you used --events and --routines, and trigger definitions (triggers are dumped by default).
  3. Restore the dump into a separate test server and compare row counts and a few business totals with the source.
  4. If you use --source-data, confirm the CHANGE REPLICATION SOURCE TO statement with a file name and position is present, because that is what a point-in-time restore needs.

A dump that finishes is not proof the account sees everything. A restore into a scratch server is the proof.

What to watch afterwards

  • Add a check after every MySQL upgrade or new database: does the backup account still hold the privileges? The 8.0.21 release notes list the PROCESS requirement as an incompatible change, so a dump that worked before an upgrade can fail after it.
  • Alert on a non-zero exit from the backup job and on a dump file that is suspiciously small.
  • When a new schema is added, repeat the SHOW GRANTS comparison before the first scheduled run.

Risks and limits

  • None of these statements has been run or lab-tested. They come from the MySQL 8.4 manual and the 8.0.21 release notes.
  • This article covers MySQL; MariaDB's mysqldump and privilege model differ. Treat the privilege list as MySQL-specific.
  • Bug #21527 is from 2006 and is included only to show the message wording.
  • The REPLICATION CLIENT requirement for --source-data comes from combining two pages: mysqldump says --source-data issues SHOW BINARY LOG STATUS, and the privileges page ties that statement to REPLICATION CLIENT.

Sources