About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/[Additional Note] When using mysql-client for MySQL 8.0.32 and 5.7.41, I cannot run mysqldump with the --single-transaction option on Amazon RDS/Aurora

[Additional Note] When using mysql-client for MySQL 8.0.32 and 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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 

overview

As stated in the title, the latest version released in January 2023 has changed the behavior of the mysqldump --single-transaction option, so it cannot be run on Amazon RDS/Aurora.
💡
MySQL 8.0.33 was released on April 18, 2023, which makes it 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 has changed

The release notes for MySQL 8.0.32 state the following:
GTID_EXECUTED
📖
When using both the --single-transaction and --set-gtid-purged=ON options, the data backed up by mysqldump could become inconsistent with the GTIDs, because the GTIDs on the server could have already been incremented between the transaction started by mysqldump and the fetch of GTID_EXECUTED.This fix enables FLUSH TABLES WITH READ LOCK.was run before fetching GTID_EXECUTED to ensure that its value matches the snapshot taken by mysqldump.
For some reason, there is nothing about this in the 5.7.41 release notes, 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 go wrong?

--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:
  • Run mysqldump with applications stopped and no updates
  • Stop replication on the read replica and run mysqldump
 
When 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 on 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 supported.
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.
 

mysqldump command did not work with MySQL8.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 are interested, please contact us here.