About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/[Additional Note] When using the mysql-client version of MySQL 8.0.32 or 5.7.41, I cannot run mysqldump with the --single-transaction option on Amazon RDS/Aurora.

[Additional Note] When using the mysql-client version of MySQL 8.0.32 or 5.7.41, I cannot run mysqldump with the --single-transaction option on Amazon RDS/Aurora.

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 

overview

As the title suggests, the latest version released in January 2023 has changed the behavior when using the mysqldump --single-transaction option, so it cannot be run on Amazon RDS/Aurora.
💡
MySQL 8.0.33 was released on April 18, 2023, and it is now possible to avoid this issue. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html Details have been added at the bottom of the article.
 

Affected versions

MySQL8.0.32
MySQL5.7.41
 

mysqldump behavior changed

The release notes for MySQL 8.0.32 state the following:
GTID_EXECUTED
📖
When both the --single-transaction and --set-gtid-purged=ON options were used, the data backed up by mysqldump became inconsistent with the GTIDs, because the GTIDs on the server might have already been incremented between the transaction started by mysqldump and the fetch of GTID_EXECUTED.This fix allows FLUSH TABLES WITH READ LOCKwas run before fetching GTID_EXECUTED to ensure that its value matches the snapshot taken by mysqldump.
For some reason, there is nothing about it in the release notes for 5.7.41, but it appears to include a similar fix.
https://github.com/mysql/mysql-server/compare/mysql-5.7.40...mysql-5.7.41#diff-2af757996671100e35b2650825a86d9a7f3d2b3bc538e9267c4195ca2e0b19caR6423
 

I mean, what's going to be inconvenient?

--single-transaction
This is because 'FLUSH TABLES WITH READ LOCK' cannot be executed in Aurora due to insufficient permissions.
FLUSH TABLES WITH READ LOCK
 

Workaround

The first thing you can do is not use the --single-transaction option.
FLUSH TABLES WITH READ LOCK
 
The issue is the same as this one, so the two solutions described here are as follows:
  • Stop the application and run mysqldump with no updates
  • Stop replication on the read replica and run mysqldump
 
If you need a logical backup, one solution is to use the MySQL Shell Dump Utility instead of mysqldump.
This is faster because the dump and restore are performed in parallel.
LOCK TABLES ... READ
 

[Added 2023/04/20] Supported in MySQL 8.0.33

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html
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.
5.7.42 has also been released and is compatible.
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-42.html
 
As stated, this issue is addressed in the above version, so I will check it out.
 

The mysqldump command did not work with MySQL 8.0.32

 

It works with MySQL 8.0.33

 
OFF_PERMISSIVE
--set-gtid-purged=OFF
 

Conclusion

SRG is looking for people to work with us. If you're interested, please contact us here.