mysqldump stopped working recently

4

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?

...
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

asked a year ago1602 views
1 Answer
2

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

answered a year ago
  • For Ubuntu / Debian hosts, check /var/cache/apt/archives for the 8.0.31 .deb files for re-install. It's not easy to do from the official channels since 8.0.32 is a security update.

  • For anyone else that this effects who may come across it. I can confirm that I re-installed the 8.0.31 .deb files (dpkg -i *mysql*_8.0.31*) and my batch process with mysqldump now completes as expected.

  • This issue also affects mysql-community-client 5.7.41. Downgrading to 5.7.40 fixes mysqldump for those on 5.7. The package is still available from the MySQL RPM repo at https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

  • how AWS releases upgrade for mysql version, we are still in version 8.0.31 of mysql but this issue affects us as well? and I can't see the release of 8.0.32 version in aws RDS as well. latest release was 8.0.31 that was on 10 Nov 2022.

  • Same issue with the latest mysqldump from MariaDB repo: $ mysqldump --version mysqldump Ver 10.19 Distrib 10.3.37-MariaDB, for debian-linux-gnu (x86_64)

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions