Couldn't execute 'FLUSH TABLES': Access denied

Note: Although this is a Drupal and Drush specific article, the solution (#2) is global and can be applied to all MySQL servers.

Just tried to sync my production Drupal database to my local machine with the drush sql-sync command and I got the following error message: mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227).

The full error message looks like this:

The command "ssh -p xx goran@xx.xx.xxx.xx '/var/www/gorannikolovski.com/bin/drush sql-dump --yes --structure-tables-key=common --strict=0 --gzip --result-file  
  =auto --format=json --uri=https://gorannikolovski.com --root=/var/www/gorannikolovski.com'" failed.                                                                
                                                                                                                                                                     
  Exit Code: 1(General error)                                                                                                                                        
                                                                                                                                                                     
  Working directory:                                                                                                                                                 
                                                                                                                                                                     
  Output:                                                                                                                                                            
  ================                                                                                                                                                   
                                                                                                                                                                     
                                                                                                                                                                     
  Error Output:                                                                                                                                                      
  ================                                                                                                                                                   
  > mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)          
                                                                                                                                                                     
  In SqlCommands.php line 240:                                                                                                                                       
                                                                                                                                                                     
    Unable to dump database. Rerun with --debug to see any error message.

The only thing that has changed since I last successfully executed this command is that the MySQL database on the production server has been updated to version 8.0.32.

It seems that recent versions of MySQL 5.7.41 and 8.0.32 contain some changes that are causing this error.

So if you suddenly can't use Drush to sync or dump your Drupal database, you have two ways to handle the issue.

1. Remove Single Transaction

Remove the --single-transaction flag from your command.

Before:

bin/drush sql-sync -y --structure-tables-key=common @gorannikolovski.prod @self

After:

bin/drush sql-sync -y --structure-tables-key=common --extra-dump="--single-transaction=false" @gorannikolovski.prod @self

The same --extra-dump option can be used for the drush sql-dump command.

This is an easier solution but it isn't a preferred way to fix the issue as it can cause inconsistent database dumps and issues when you try to restore/import your database in the future. Use it only as a temporary workaround if solution #2 is not working for you.

2. Grant privileges

Log in to your MySQL console on the server. In my case that looks like this:

mysql -uroot -p

and grant privileges to the proper user (in my case the user is goran):

GRANT RELOAD,PROCESS ON *.* TO 'goran'@'%';
FLUSH PRIVILEGES;

This should solve the problem in the right way.

If you don't know the name of the user you can list all your users by executing the following command in the MySQL console:

SELECT User,Host FROM mysql.user;

And that's a wrap. I hope this brief article will be of help in resolving the issue.