What happened?

Description

We run many craft instances, on many servers.
They all are cpanel/whm servers, which are a quite common.
With the last update, it installed mysql 5.7.41 / 8.0.32 on most our the servers.
This version was released by Oracle on Jan 17th (3 days ago).
Since then, all running craft instances on those server can no longer run backups.

We tested on other servers that still have mysql 5.7.39 and it stills works.
We tested with craft 4.2.5.2 and 4.3.6.1.
Both works/fails depending on mysql's version (so it's not directly craft related).

The problem is only present when the --single-transaction is used. Removing it creates a backup.
Nothing seems related to this flag in the release notes:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-41.html
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html

Since --single-transaction locks the database, I can understand why mysql would want to FLUSH TABLES before doing so. But having a user that has this privilege might be something pretty rare: Under cpanel, your can't even create a user with such privilege. And it's a global privilege and can not be enable only for specific db.
Using the --single-transaction seems like a good practice tho so maybe craft could check for the privilege before including it ?

I am pretty sure that we are not the only ones with broken backups right now and even tho this is not a bug in craft, we think it should try to maximize the chance of producing a backup.

Steps to reproduce

1. Run ./craft db/backup

Expected behavior

It creates a backup, with a warning that it may be inconsistent because the DB user does not have the RELOAD privilege (?)

Actual behavior

It errors out with the message:

failed with exit code 2: mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)

Craft CMS version

4.x

PHP version

7.4-8.1

Operating system and version

Centos

Database type and version

mysql 5.7.41 and 8.0.32

Image driver and version

gd

Installed plugins and versions

• Does not mater...