110

I am trying to backup mysql using the command

mysqldump -u root -p  database_name > backup.sql

, but it is throwing an error:

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Also, I wanted to make a backup my database with all tables seperately stored as a file. How can I do it?

mysql version = 5.7.31

0
135

use --no-tablespaces

see mysqldump-Documentation

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.

and see documentation of param no-tablespaces

--no-tablespaces, -y

This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump.

2
  • 4
    In the 5.7 branch, this has changed in MySQL 5.7.31.
    – nwellnhof
    Aug 22 '20 at 13:08
  • Thanks! My backup script was failing on Lightsail and I didn't know why, but I see that they just upgraded to MySQL 8.0.21. All I had to do was add --no-tablespaces, which I don't really need for the type of simple backup I am trying to perform.
    – Richard
    Jan 30 at 17:58
67

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command.

I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users.

Why not all at once?

Turns out this is a breaking change introduced in MySQL minor updates 5.7.31 and 8.0.21 in 2020/07.

See 5.7.31 Release Notes / 8.0.21 Release Notes

Security Notes

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

The said issue number should be available here http://mybug.no.oracle.com/orabugs/bug.php?id=30350829. Oracles bug tracker seems to be down however. Another public issue is https://bugs.mysql.com/bug.php?id=100219.

So this issue has to happen on all machines having MySQL >= 5.7 and getting system updates eventually.

Breaking Changes in minor updates are unexpected. This is a security issue however. It's a pity that this issue and how to solve it is not communicated better. Imho a more practical solution for this breaking change would be to removed tablespaces in mysqldump by default and add a --add-tablespaces option instead.

Why not on all commands?

The said privilege PROCESS is not needed for most use cases. mysqldump however tries to access the INFORMATION_SCHEMA.FILES table, which - as said above - now requires the PROCESS privilege.

So mysqldump now needs to be started by a user with enough privileges or using the --no-tablespaces option.

See mysqldump documentation

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 5.7.31) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions.

See --no-tablespaces documentation

This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump.

See process documentation

The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). The PROCESS privilege also enables use of the SHOW ENGINE statement, access to the INFORMATION_SCHEMA InnoDB tables (tables with names that begin with INNODB_), and (as of MySQL 5.7.31) access to the INFORMATION_SCHEMA FILES table.

What a MySQL tablespace is why it is used is explained very well in another question on StackOverflow already.

General advice: Most users do not need the tablespace information and may therefore safely ignore it using the --no-tablespace option.

If tablespaces are configured knowingly and willingly, then the user doing the mysqldumps should get sufficient privileges.

Why not on all users?

The MySQL root user is not affected of course. Other users may or may not be affected depending on the way they are given privileges.

The GRANT option adds privileges on a global level, or a single database, a single table, or even columns or routines. Some privileges, like the said PROCESS privilege, need to be added on a global level.

See grant documentation

The following table summarizes the permissible privilege types that can be specified for the GRANT and REVOKE statements, and the levels at which each privilege can be granted.

| Privilege | Meaning and Grantable Levels |

| PROCESS | Enable the user to see all processes with SHOW PROCESSLIST. Level: Global. |

So the sql command GRANT ALL PRIVILEGES ON somedatabase.* TO someuser@localhost; will add all available privileges to the user on one database only. Global privileges are added using ON *.*.

In my case most databases users had privileges for selected databases only. Check the users privileges with SHOW GRANTS for someuser@localhost;.

To add the global privilege use the SQL command GRANT PROCESS ON *.* TO someuser@localhost;. Don't do this unless you know about the consequences of this privilege. To the revoke the privilege use REVOKE PROCESS ON *.* FROM someuser@localhost;.

5
  • 2
    Requiring an --add-tablespace option would mean that your backup is now incomplete, which may or may not be worse than not having a backup at all -- quite a lot of systems out there have a backup cronjob whose errors go to /dev/null. A better idea would have been to only die a flaming death if there are in fact tablespaces which you need privileges to back up for. Sep 6 '20 at 13:54
  • 1
    You mention 8.2.21 but it's probably 8.0.21, right?
    – cherouvim
    Dec 3 '20 at 11:56
  • @cherouvim Right, thanks. Replaced the version. Dec 4 '20 at 12:40
  • 1
    GRANT PROCESS ON *.* TO someuser@localhost; did solve the issue. :) Dec 16 '20 at 8:10
  • 1
    Is there any way I can set--no-table-spaces for use within MySQL Workbench? Such as in a .ini or .cnf file? I cannot find any direction in the MySQL documentation, or SE sites, or threads. I only see it as a command line option. I get this error on a remote server. Workbench does not give me an option to specify a Configuration File in the Connections -> System Profile tab for remote servers. How can I get rid of that error message when Exporting tables for that DB? Workbench 8.0, MySQL 5.6. I have no errors from my local servers (using 5.7.28). Jan 9 at 19:27
2

Resolve by adding:

--column-statistics=0 --no-tablespaces

to your dump command. I had to add the column-statistics also as just no-tablespaces was not working.

1
  • --column-statistics=0 --no-tablespaces. That works on mysqldump command. I'm using mysqlworkbench 8.0.19 but couldn't find these options in advanced options section of data export.
    – ramify
    Apr 29 at 15:37
-1

If this is failing for you in Workbench, try to use the command line instead.

First find the path of your mysqldump tool

find / -name "*mysqldump*"
# probably /usr/bin/mysqldump 

Then the following command (Replace DB_HOST, DB_PORT and DB_USER).You can then export the password:

export MYSQL_PWD=DB_PASSWORD

And then run this command

/usr/bin/mysqldump \
--host=DB_HOST \
--port=DB_PORT \
--default-character-set=utf8 \
--user=DB_USER \
--protocol=tcp \
--single-transaction=TRUE > mydump.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy