In MySQL Server 8.0.32, Oracle fixed Bug #105761:
“mysqldump make a non-consistent backup with ‐‐single-transaction option” (this commit)
which caused a wave of complaints from users who could no longer do backups with the mysqldump utility because of the lack of the required privileges.
- Bug #109701 “Fix for #33630199 in 8.0.32 introduces regression when ‐‐set-gtid-purged=OFF”
- Bug #109685 “mysqldump has incompatible change in MySQL 8.0.32″
Later, Oracle admitted the problem and even added a new paragraph to the MySQL Server 8.0.32 Release Notes.
Limitation: This fix adds a requirement for the RELOAD privilege when using ‐‐single-transaction to execute FLUSH TABLES WITH READ LOCK; the MySQL team is investigating a solution. (Bug #33630199, Bug #105761)
With the release of Percona Server for MySQL 8.0.32-24, we believe there is a better solution to this problem.
For some combinations of server Global Transaction Identifier (GTID) mode and mysqldump command line arguments, you can use the mysqldump utility from the Percona Server for MySQL 8.0.32 packages/tarballs.
Below you will find a detailed description of the mechanism that mysqldump utility is using when creating dumps (thanks to Saikumar VS for conducting a series of experiments).
The terms below:
- STWCS – START TRANSACTION WITH CONSISTENT SNAPSHOT
- FTWRL – FLUSH TABLES WITH READ LOCK
- When the server has GTIDs enabled
mysqldump command line arguments mysqldump from MySQL Server 8.0.31 mysqldump from MySQL Server 8.0.32 mysqldump from Percona Server for MySQL 8.0.32 ‑‑single‑transaction
‐‐set‑gtid‑purged=OFFSTWCS FTWRL
STWCSSTWCS ‑‑single-transaction
‐‐set‑gtid‑purged=ONSTWCS FTWRL
STWCSSTWCS - When the server has GTIDs disabled
mysqldump command line arguments mysqldump from MySQL Server 8.0.31 mysqldump from MySQL Server 8.0.32 mysqldump from Percona Server for MySQL 8.0.32 ‑‑single‑transaction STWCS FTWRL
STWCSSTWCS
To sum up, it is safe to use the mysqldump utility from Percona Server for MySQL 8.0.32 with the “‐‐single-transaction” command line argument when the server is not using GTIDs. Also, when GTIDs are enabled on the server, it is safe to use the mysqldump utility from Percona Server for MySQL 8.0.32 with the “‐‐single-transaction ‐‐set-gtid-purged=OFF”. Creating these dumps does not rely on sending FTWRL to the server and, therefore, won’t require the unnecessary RELOAD/FLUSH_TABLES privilege from the user on behalf of whom the backup is created.
On the other hand, when you use the mysqldump utility from Percona Server for MySQL 8.0.32 with the “‐‐single-transaction ‐‐set-gtid-purged=ON”, just the STWCS statement (without FTWRL) will be sent to the server, which guarantees consistent results only when using Percona Server for MySQL (not Oracle MySQL Server). This happens because of additional code patches that Percona Server for MySQL has and Oracle MySQL Server does not. Basically, when you use mysqldump utility from Percona Server for MySQL 8.0.32 with “‐‐single-transaction ‐‐set-gtid-purged=ON” on an instance of Oracle MySQL Server 8.0.32, you will get the old (faulty) 8.0.31 behavior that does not guarantee that the snapshot taken and purged GTID set will be in sync.
If you indeed need to run mysqldump with “‐‐single-transaction ‐‐set-gtid-purged=ON” on an instance of Oracle MySQL Server and you want to get a consistent snapshot of your data, you have no other choice than using the mysqldump binary from the MySQL Server 8.0.32. And “yes,” in this case, your user needs to have either RELOAD or FLUSH_TABLES privilege.
Please also notice that these recommendations are just temporary solutions that can be used until Oracle fixes these bugs properly (the fixes are currently scheduled to be included in the upcoming MySQL Server 8.0.33).
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
