mysql dump FLUSH TABLES access denied

I've recently upgraded the server Ubuntu 20.04 updating the PHP version from 7.4 to 8.1.  However, when I've attempted to export the MySQL database using the command

drush sql-dump --extra-dump=--no-tablespaces --result-file=../sql/db-2023-02-25.sql

I seeing the following response

> 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 242:

  Unable to dump database. Rerun with --debug to see any error message.

As noted in the response, try adding --debug to see what additional information can assist

 [preflight] Redispatch to site-local Drush: '/var/www/html/{client}/vendor/drush/drush/drush'.
 [preflight] Config paths: /var/www/html/{client}/vendor/drush/drush/drush.yml
 [preflight] Alias paths: /var/www/html/{client}/drush/sites,/var/www/html/drush/sites
 [preflight] Commandfile search paths: /var/www/html/{client}/vendor/drush/drush/src
 [debug] Starting bootstrap to max [0.12 sec, 9.79 MB]
 [debug] Drush bootstrap phase: bootstrapDrupalRoot() [0.12 sec, 9.79 MB]
 [debug] Change working directory to /var/www/html/{client} [0.12 sec, 9.79 MB]
 [debug] Initialized Drupal 9.3.2 root directory at /var/www/html/{client} [0.12 sec, 9.79 MB]
 [debug] Drush bootstrap phase: bootstrapDrupalSite() [0.12 sec, 9.8 MB]
 [debug] Initialized Drupal site default at sites/default [0.13 sec, 9.96 MB]
 [debug] Drush bootstrap phase: bootstrapDrupalConfiguration() [0.13 sec, 9.96 MB]
 [debug] Add service modifier [0.13 sec, 10.2 MB]
 [info] Executing: command -v mysql [0.14 sec, 10.68 MB]
 [info] sql:query: SHOW TABLES; [0.14 sec, 10.75 MB]
 [info] Executing: mysql --defaults-file=/tmp/drush_iccYgr --database={client_db} --host=localhost --port=3306 --silent -A < /tmp/drush_KLhprp [0.14 sec, 10.75 MB]
 [info] Executing: mysqldump --defaults-file=/tmp/drush_OesE3q {client_db} --host=localhost --port=3306 --no-autocommit --single-transaction --opt -Q --no-tablespaces  > ../sql/db-2023-02-25.sql [0.18 sec, 10.78 MB]

Grant reload

Most likely you're not running FLUSH command using root, instead with a limited user.  You need to be granted RELOAD privilege to run FLUSH command.

This is the recommended solution. You need to run the following two commands in order to grant the privileges:

# Replace your-user with your user name and make sure the host matches the user host
GRANT RELOAD, PROCESS ON *.* TO 'your_user'@'localhost';

# Reload the privileges
FLUSH PRIVILEGES;

or...

GRANT RELOAD ON *.* TO 'your_user'@'localhost';

Database dump a better way

Use the following command to dump your database

drush sql-dump --extra-dump=--no-tablespaces --result-file=../sql/db-$(date +%Y-%m-%d).sql