mysqldump.guru

# mysqldump: Couldn't execute 'FLUSH TABLES': Access denied

An update for mysqldump has been released by Oracle, causing backups to malfunction when the flag --single-transaction is used. The update, which was released as a minor release, was automatically fetched by systems with automatic updates enabled and started to cause backups to fail.

The error message produced while trying to create a backup with the flag --single-transaction is the following:

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied

# How to fix it?

Currently, there are two possible solutions: granting READ and PROCESS permissions to the user performing the backup (preferred method) or avoiding the use of the --single-transaction option (not recommended).

This is the preferred solution. To grant the necessary privileges, you should run the following two commands:

# Replace my_user with your user and make sure the host matches the user host
GRANT RELOAD, PROCESS ON *.* TO 'my_user'@'%';

# Reload the privileges
FLUSH PRIVILEGES;

To obtain a list of your users and their respective hosts, you can execute the following MySQL query:

SELECT `User`, `HOST`, `Process_priv`, `Reload_priv` FROM mysql.user;

# Solution #2: don't use the --single-transaction flag

Although simpler, this solution should be avoided if possible. Omitting the --single-transaction flag when executing mysqldump can lead to potential issues, as it can result in an inconsistent backup.

←