All Categories
Help, Tips & Troubleshooting
Backups failing for MySQL server versions (5.7.41 and 8.0.32)

Backups failing for MySQL server versions (5.7.41 and 8.0.32)

Backups breaking for MySQL server versions (5.7.41 and 8.0.32)

MySQL server versions (5.7.41 and 8.0.32) introduced a bug that is currently breaking backups for users who updated to these versions.

If your backups have been running in the past, and recently started failing without an obvious reason, then your MySQL server was probably just updated to one of these versions.

 

Error messages

You may see one of these errors when trying to back you your database

  • 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)
  • ERROR: The database dump process encountered errors, preferring to fail as a precaution (ED385)
  • mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user
 

Temporary Solution

While this is patched by MySQL, one solution on your end is to grant the MySQL user you use to back up MySQL the needed permissions

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

Note: ensure you replace backup_user by the actual user you use to back up your database.

 

After you do so, ensure that these permissions are applied by running:

SHOW GRANTS;
 
 

Temporary Solution — if you are using your own server to dump the database

If the temporary solution above still fails, and you are using your own server to dump the database, you may need to just use an earlier version of mysqldump — you could try the following:

 
sudo apt-get install libncurses5
mkdir -p /opt/mysql
cd /opt/mysql
curl https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.30-linux-glibc2.17-x86_64-minimal.tar.xz | tar -xvJ
mv mysql-8.0.30-linux-glibc2.17-x86_64-minimal 8.0.30
/opt/mysql/8.0.30/bin/mysqldump --version
/opt/mysql/8.0.30/bin/mysql --version
/opt/mysql/8.0.30/bin/mysqlbinlog --version
sudo ln -s /opt/mysql/8.0.30/bin/mysql /usr/local/bin/mysql
sudo ln -s /opt/mysql/8.0.30/bin/mysqldump /usr/local/bin/mysqldump
sudo ln -s /opt/mysql/8.0.30/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
 

This will install MySQL 8.0.30 on your machine, and will create symlinks to ensure that it is used instead of any other version.

Did this answer your question?
😞
😐
🤩

Last updated on June 26, 2023

Solving Error 2013: Lost connection to MySQL server during query when dumping table PostgreSQL Backup Privileges Issues