A Workaround for The “RELOAD/FLUSH_TABLES privilege required” Problem When Using...
source link: https://www.percona.com/blog/workaround-for-the-reload-flush_tables-privilege-required-problem-when-using-oracle-mysqldump-8-0-32/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
A Workaround for The “RELOAD/FLUSH_TABLES privilege required” Problem When Using Oracle mysqldump 8.0.32
March 24, 2023
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!
Share This Post!
Recommend
-
42
脏页:内存数据页与磁盘数据页内容 不一致 干净页:内存数据页与磁盘数据页内容 一致 flush: 将内存中的脏页写入磁盘 ...
-
23
本文来自51CTO博客, 原 文地址 https://blog.51cto.com/13981400/2402526...
-
22
文档提交之flush(二) 本文承接文档提交之flush(一),继续依次介绍每...
-
41
文档提交之flush(六) 本文承接文档提交之flush(五),继续依次介绍每...
-
26
文档提交之flush(八)终 本文承接文档提交之flush(七),继续依次介绍...
-
6
本文主要研究一下klog的FlushFlushk8s.io/klog/[email protected]/klog.go// Flush flushes all pending log I/O. func Flush() { logging.lockAndFlushAll() }Flush方法执行的是logging.lockAndFlushA...
-
11
前面文章说了,ChannelHandlerContext#write只是将数据缓存到ChannelOutboundBuffer,等到ChannelHandlerContext#flush时,再将ChannelOutboundBuffer缓存的数据写到Channel中。 本文分享Netty中ChannelOutboundBuffer的实现以及Flush过...
-
2
Creativity and Problem Solving is Required at Every LevelFebruary 15th 2022 new story4Many times, th...
-
12
[oss-security] Linux kernel: CVE-2022-1015,CVE-2022-1016 in nf_tables cause privilege escalation, information leak Thread information [Search the oss-security...
-
6
Microsoft issues workaround for VPN problem in Windows 11 Dev Channel build 25284...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK