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.
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
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:
The bugs are actively discussed at https://bugs.mysql.com/bug.php?id=109685 and https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866
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.
Last updated on June 26, 2023