search

etiennerached / godaddy-backup-script

Automatically Backup GoDaddy Files and Databases
Mozilla Public License 2.0
19 stars 14 forks source link

mysqldump error #3

Open andrewparlane opened 3 years ago

andrewparlane commented 3 years ago

Recently my backup script started producing an error from mysql dump:

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

After a bit of research I found this thread: https://forums.cpanel.net/threads/cpanel-33473-mysql-dump-process-privilege-error-after-5-7-31-update.675657/ which explains the issue.

So there are two possible fixes, provide your backup database user with the PROCESS privilege or add --no-tablespaces to the mysqldump command. I have not found a way to add the PROCESS privilege to the user via godaddy's "shared hosting with cpanel", which leaves just the --no-tablespaces workaround.

The main reason I'm posting this as an issue rather than a pull request, is I'm not sure exactly what the --no-tablespaces argument does and what effect that is likely to have on the backups. If tablespaces are a critical part of the database, then this workaround is useless as we won't be able to restore from the backup. If however they are not actually used for anything important / are automatically created as needed, then this workaround will be fine.

Some more research is required into what exactly a tablespace is, and whether or not they should be backed up.

In the meantime if anyone wants to use this workaround they should modify backup.sh to change the mysqldump command from:

mysqlCmd="mysqldump --defaults-extra-file=$BASEDIR/${dbCnf[$i]} ${dbName[$i]}"

to

mysqlCmd="mysqldump --defaults-extra-file=$BASEDIR/${dbCnf[$i]} --no-tablespaces ${dbName[$i]}"

etiennerached commented 3 years ago

Hello Andrew,

Thank you for reporting this.

That's correct, the new mysql/mariadb requires this argument --no-tablespaces

I will fix this before the end of the week.

You can't add the PROCESS on a shared hosting because it affects all users.

"With the PROCESS privilege, a user has access to information about all threads, even those belonging to other users."

etiennerached commented 3 years ago

Fixed and pushed to master branch

andrewparlane commented 3 years ago

Great thanks.

How certain are you that not duming tablespaces is OK? I'm in agreement with everything else, I just can't figure out if tablespaces are important and need to be backed up.