Andrew Fletcher published: 25 February 2023 2 minutes read
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
Related articles
Andrew Fletcher
•
20 May 2024
Create a copy of files that go to the tmp directory
To review the content of files being generated in the /tmp directory on an Ubuntu server before Microsoft Defender removes them, you can use several approaches. Following is the approach we took. Real-Time MonitoringYou can set up a script to monitor the /tmp directory and log the...
Andrew Fletcher
•
09 May 2024
Accessing nested paragraph fields in Drupal: a step-by-step reference
The issue – I have a content type that includes an entity reference revisions field named field_introduction. This field points to a paragraph, which itself contains a text field also named field_introduction, of the type Text (formatted, long). How can I access the content stored in this...
Andrew Fletcher
•
09 May 2024
How to retrieve the current menu title in Drupal – a developer’s reference
When building a theme in Drupal, it's often necessary to adapt your site’s UI dynamically based on the context in which a user is navigating. One such piece of dynamic content is the menu title. Achieving this can be a bit tricky due to the layered nature of its routing and menu systems. This...