Using --no-tablespace option when backing up Magento 2 databases

[skinnybloke]

Hi - I run a nightly cron job to back-up to backup my SQL databases using mysqldump.

This has started to display the following error on all my databases:

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

One of the solutions for this is to run the mysqldump using the --no-tablespace option.

Is this a safe practice, and recommended practice, to implement with Magento 2 databases?

[JLHC]

Are you using the root user or a database user to run the mysqldump? 

Did you assign the necessary privileges (in this case PROCESS) to the user? 

[skinnybloke]

Hi - the job is defined in the cron tab of the user's cPanel.

I get the same error if run the backup script from SSH using the user login and the root login.

All privileges are assigned to the database for the user.

This script ran fine for about 5 years. I don't think the version of SQL has updated. We are still running v5.6.49.

My understanding is that the tablespaces are reliant on a global privilege and not a user privilege.

[haijerome]

If you face this issue on mysql 5.7+, then you need to GRANT the PROCESS previleges to the respective user that you use to DUMP the database backup (for this you need to be a user with GRANT previleges or a root user)

GRANT PROCESS ON *.* TO 'database_user'@'localhost'

or you can use the option --no-tablespaces        

mysqldump --opt --no-tablespaces -u <database_user> -p<database_password> <database_name> > <databasename.sql>