CODEWITHSUNDEEP
linuxmariadbamazon-rds
cjashwell
cjashwell

Reputation: 619

mysqldump for AWS RDS `flush tables` error on linux only

I have a process that exports the data from an AWS RDS MariaDB using mysqldump which has been running succesfully in a docker-image on Concourse for years.

Since two nights ago the process has started failing with the error:

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'admin'@'%' (using password: YES) (1045)

The official AWS explanation seems to be that because they do not allow super privileges to the master user or GLOBAL READ LOCK the mysqldump fails if the --master-data option is set.

I do not have that option set. I'm running with these flags:

mysqldump -h ${SOURCE_DB_HOST} ${SOURCE_CREDENTIALS} ${SOURCE_DB_NAME} --single-transaction --compress | grep -v '^SET .*;$' > /tmp/dump.sql

mysqldump works fine when executed from my local Mac. It fails with the error that it couldn't execute FLUSH TABLES WITH READ LOCK only from the linux environment.

My question is, does anyone know how to disable the FLUSH TABLES WITH READ LOCK command in mysqldump on linux?

EDIT: Happy to accept @sergey-payu answer below as having fixed my problem, but here's a link to the MySQL bug report for the benefit of anyone else coming across this issue https://bugs.mysql.com/bug.php?id=109685

Upvotes: 43

Views: 26344

Answers (4)

Jeremy Wright
Jeremy Wright

Reputation: 621

Along with granting my user PROCESS priv, I found that the --set-gtid-purged=OFF option worked for me.

mysqldump --single-transaction --set-gtid-purged=OFF -h {host} {schema}

Hitting AWS RDS MySQL 8.0.33 from Ubuntu 22.04

Upvotes: 52

David Winiecki
David Winiecki

Reputation: 4203

On Debian, I'm having a really difficult time downgrading mysqlclient and mysqldump from 8.0.32 to 8.0.31 (or any other 8.0.x). (At least on ubuntu it looks like downgrading to 8.0.28 is easy. I don't know if 8.0.28 is stable.)

Here's the best I've come up with. It seems to work for me but YMMV (your mileage may vary). Generally the deb packages in the https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/ directory seem to work.

# May be optional:
apt-get remove -y default-libmysqlclient-dev && apt-get autoremove -y

# NOTE: Use `dpkg -I abc.deb | grep Depends` (recursively, on mysql-client
#       and each of its mysql package dependencies) to find out which other
#       mysql packages are needed.
# apt-get check: Report if there are now any broken dependencies.
# rm -rf /var/lib/apt/lists/*: This is meant to be run in a Dockerfile,
# and deleting these files after apt-get install is commonly done to
# make the resulting image layer smaller, which speeds up image download
# from a docker image repo later.
wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-client_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client-core_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client-plugins_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/libmysqlclient-dev_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/libmysqlclient21_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-common_8.0.31-1debian11_amd64.deb && \
    dpkg -i *_8.0.31-1debian11_amd64.deb && \
    rm *_8.0.31-1debian11_amd64.deb && \
    apt-get update && \
    apt-get check && \
    rm -rf /var/lib/apt/lists/*

Upvotes: 0

Dave
Dave

Reputation: 159

I found that installing MariaDB Client resolves this issue.

In Ubuntu, run:

sudo apt remove mysql-client -y && sudo apt install mariadb-client -y

Upvotes: 15

Sergey Payu
Sergey Payu

Reputation: 304

I faced the same issue couple of days ago. My mysqldump script had been working for years just fine until it started to give me the Access denied; you need (at least one of) the RELOAD privilege(s) for this operation error. My first instinct was to grant this privilege. But after that I started to get Access denied for user 'user'@'%' (using password: YES) (1045) error, which is documented in AWS docs. After couple hours of investigation it turned out to be a bug of the most recent 5.7.41 version of mysql (It was released 17th of January, exactly when we started to get errors). Downgrading to 5.7.40 solved the problem. It's interesting that 5.7.41 changelog doesn't list anything close to the FLUSH TABLES WITH READ LOCK or to default values.

Upvotes: 23

Related Questions