How to fix the mysqldump process privilege error after applying a recent MySQL update.
You may receive a new ‘Access denied’ error when trying to dump your MySQL database:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
You were able to export database before so what caused this? Here’s the answer: updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change:
This change affects users of the mysqldump command, which accesses tablespace information in the
FILEStable, and thus now requires the
PROCESSprivilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the
--no-tablespacesoption. (Bug #30350829)
This error appears when running
mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s
In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.
Solutions for fixing the mysqldump process privilege error
mysqldump command requires at least the following privilege assigned to the user:
- SELECT privilege for dumped tables
- SHOW VIEW for dumped views
- TRIGGER for dumped triggers
- LOCK TABLES if you don’t use the
- PROCESS if you don’t use the
The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:
- Updating the privileges for your database user.
Solution 1: Update the user privileges
Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the
mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.
To grant the PROCESS privilege, log in as an administrator user and run the following query:
GRANT PROCESS ON *.* TO [email protected];
Node that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
To grant the privilege to all databases you must use the
ON *.* ... syntax.
Solution 2: Use the
If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the
--no-tablespaces option when dumping your database.
mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql
What are MySQL tablespaces?
We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.
The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.
You can therefore use the
--no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.
About the access mysqldump denied PROCESS privilege error
mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run
mysqldump without the PROCESS privilege. However, users running
mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running
mysqldump without PROCESS privilege ends up giving you an Access denied error.
Be careful with the PROCESS privilege
According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.
It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as
UPDATE user SET password=PASSWORD, may show secrets.
For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.