[Update Added] With MySQL 8.0.32 and 5.7.41, mysql-client cannot execute mysqldump with the --single-transaction option on Amazon RDS/Aurora.
This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
overviewTarget versionmysqldump's behavior has changed.So what exactly would become inconvenient?Workaround[Added on 2023/04/20] This issue has been resolved in MySQL 8.0.33.The mysqldump command did not work with MySQL 8.0.32.It works with MySQL 8.0.33.In conclusion
overview
As the title suggests, the latest version released in January 2023 has changed the behavior when using the --single-transaction option of mysqldump, making it impossible to execute on Amazon RDS/Aurora.
MySQL 8.0.33 was released on April 18, 2023, which resolved this issue.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html
Further details are provided at the bottom of the article.
Target version
MySQL8.0.32
MySQL5.7.41
mysqldump's behavior has changed.
The release notes for MySQL 8.0.32 state the following:
GTID_EXECUTEDWhen both the --single-transaction and --set-gtid-purged=ON options were used, a discrepancy occurred between the data backed up by mysqldump and the GTID. This was because the GTID on the server may have already increased between the transaction initiated by mysqldump and the fetching of GTID_EXECUTED.This fix enables the use of FLUSH TABLES WITH READ LOCK.This was executed before fetching GTID_EXECUTED, and it was confirmed that its value matched the snapshot taken by mysqldump.
For some reason, nothing is written in the release notes for 5.7.41, but it appears that a similar fix has been included.
So what exactly would become inconvenient?
--single-transactionThis is because the command 'FLUSH TABLES WITH READ LOCK' cannot be executed in Aurora due to insufficient permissions.
FLUSH TABLES WITH READ LOCKWorkaround
One option is to simply not use the single-transaction option.
FLUSH TABLES WITH READ LOCKSince the situation is the same as this time, the two solutions described here are as follows:
- Stop the application and run mysqldump when there are no updates.
- Stop read replica replication and run mysqldump
When a logical backup is needed, using the MySQL Shell Dump Utility instead of mysqldump might be a good solution.
This method is faster because the dump and restore processes run in parallel.
LOCK TABLES ... READ[Added on 2023/04/20] This issue has been resolved in MySQL 8.0.33.
Using --single-transaction with mysqldump version 8.0.32 required either the RELOAD or FLUSH_TABLES privilege. This requirement now applies only when both gtid_mode=ON (default OFF) and with --set-gtid-purged = ON|AUTO (default AUTO). (Bug #109685, Bug #109701, Bug #34993824, Bug #34998910, Bug #35020512)References: This issue is a regression of: Bug #105761, Bug #33630199.
Version 5.7.42 has also been released and is compatible.
As stated, the issue has been addressed in the version mentioned above, so I will check it.
The mysqldump command did not work with MySQL 8.0.32.
It works with MySQL 8.0.33.
OFF_PERMISSIVE--set-gtid-purged=OFFIn conclusion
SRG is looking for new team members.
If you are interested, please contact us here.