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
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 withmysqldump
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)