Issue

On Cloud Platform, certain MySQL versions require different permissions to execute data dumps; because of this, some commands such as:

• drush sql-dump
• drush sql-sync
• blt (commandline tool)
• mysqldump
• Any scripts, cronjobs, etc. that depend on the above commands.

... can throw errors similar to this:

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

Resolution

A workaround is to add some extra arguments to your commands. This varies depending on the tool you're using. Two options are provided here for:

• drush
• mysqldump

¶

For Drush¶

If you are using Drush 9 & above, adding the --extra-dump argument as shown below. 

--extra-dump="--set-gtid-purged=OFF --no-tablespaces"

And in case you're using Drush 8, the option which we need to use is --extra, instead of --extra-dump.

This will work with both the drush sql-dump and sql-sync commands.

Example full Drush commands:

# drush sql-dump
drush --uri=mysite.editme.com sql-dump --extra-dump="--set-gtid-purged=OFF --no-tablespaces" > /destination/folder/dump.sql

# drush sql-sync
drush sql-sync @dev @self --extra-dump=--set-gtid-purged=OFF

Also see the Help Article, Troubleshooting "ERROR 1227 (42000) at line 18: Access denied".

For mysqldump¶

The mysqldump command can also receive the extra parameters. Example:

mysqldump MYDBNAMEHERE --no-tablespaces --set-gtid-purged=OFF > /destination/folder/dump.sql