Drush throws an "Access denied; you need (at least one of) the PROCESS privilege(s)" error message

Issue

Running a `drush sql-dump` can sometimes result in an error similar to:

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

A similar error may be:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME .... {snip}
Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Resolution

This issue can be bypassed by re-running the command adding some arguments to the internal call to mysqldump using the --extra or --extra-dump flag:


For drush 8:

$ drush sql-dump --extra="--set-gtid-purged=off --column-statistics=0 --no-tablespaces"

For drush 12:

$ drush sql-dump --extra-dump="--set-gtid-purged=off --column-statistics=0 --no-tablespaces"

If the above does not resolve the issue, file a ticket with Acquia Support for further investigation.

Cause 

With the recent upgrade to MySQL version 5.7 there was a change in the permissions required to utilize the `mysqldump` function used by Drush. Quote from https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html#mysqld-5-7-31-security:

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.