mysqldump stopped working recently

Question

I use mysqldump nightly to ensure provider-redundant backups of my rds mysql instance. My last successful dump was Jan 26 02:26 (UTC). Now, I get permission denied errors even as the db administrator user.
As the original user, I get

```
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)
```
I tried to grant that user `FLUSH TABLES` but was unable to grant that privilege as the db administrator. The db administrator has `RELOAD`, so I tried the mysqldump as the db administrator, but then I get

```
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'dbadmin'@'%' (using password: YES) (1045)
```
My research turned up this knowledge center article: https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/

But I'm unable to follow the advice to exclude the `--master-data` argument because I'm already not including it.

My failing command line is
```
 /usr/bin/mysqldump --login-path='{login_path}' --ssl-ca=/etc/ssl/certs/rds-combined-ca-bundle.pem --ssl-mode=VERIFY_IDENTITY --max_allowed_packet=1G --single-transaction --quick --lock-tables=false --column-statistics=0 {database_name}
```

The most obvious culprit is a mysql upgrade on the OS on the machine trying to do the dump though it confuses me about why the _client_ permissions needs would change?

```dpkg.log
...
2023-01-26 06:45:09 upgrade mysql-client-core-8.0:amd64 8.0.31-0ubuntu0.20.04.2 8.0.32-0buntu0.20.04.1
...
```

So, I'll roll back that upgrade, but if anyone has pointers on how to both keep the mysql client current _and_ continue to successfully mysqldump from RDS, I'd certainly appreciate it.

Client:
Ubuntu 20.04.5
mysqldump  Ver 8.0.32-0buntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Server:
RDS with MySQL engine version 8.0.28

TIA,
AC

accapehart · Answer

This isn't an answer, exactly, but is at least more information.

The update from 8.0.31 to 8.0.32 did, indeed, cause this change. It was a change to avoid potentially inconsistent data in the dump file. (See this commit: https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28) The "fix" is to use `FLUSH TABLES WITH READ LOCK`.

However, it breaks mysqldumps against AWS instances because AWS doesn't allow `FLUSH TABLES WITH READ LOCK` because that in turn would break the AWS RDS backups (as per the earlier article: https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/).

So, it appears that upgrading to 8.0.32 clients does put AWS RDS users in a bit of a bind for now.

More information (the resource for everything I just posted) is found here: https://bugs.mysql.com/bug.php?id=109685

mysqldump stopped working recently

Add your answer

Relevant content