I was trying to back up the database using the MySQL dump command, however, the command failed with an error message ‘Error: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’. Here’s the complete error message.
$ mysqldump -u dbuser -p tg_db > tg_db.sql Enter password: mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I have been dumping the database like this for many years and the command failed all of a sudden. How did I solve this error? Here we go.
Fix Error: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
All I did was add ‘
--no-tablespaces‘ option to the command as shown below:
$mysqldump -u dbuser -p tg_db --no-tablespaces > tg_db.sql
And that worked. So what is
--no-tablespaces option and why should I add that?
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the –single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the –no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions.
Have a look at the –no-tablespaces option as well. This option removes the
CREATE LOGFILE GROUP and
CREATE TABLESPACE statements from the output.
PROCESS privilege is required for
mysqldump command? Because
mysqldump tries to access the
INFORMATION_SCHEMA.FILES table which requires PROCESS privilege. Well, the privileges can be added via
GRANT on a global level, or a single database or table. But the
PROCESS privilege needs to be added on a global level as shown below.
GRANT PROCESS ON *.* TO user@localhost;
Why doesn’t this error shown for all users? The MySQL root user is not affected, obviously. The behavior might be different for other users based on their privilege levels.