[SOLVED] Rights needed to Flush and Lock tables in RDS Mysql DB

Rights needed to Flush and Lock tables in RDS Mysql DB

I would like to Flush then Lock all tables in all Mysql databases on a RDS instance.

Following Mysql documentation, the simplest way to do so is to run :

FLUSH LOCAL TABLES WITH READ LOCK

cf. : http://dev.mysql.com/doc/refman/5.6/en/flush.html

but when I run this query with a Master user (created with RDS instance creation wizard) I get this Error :

Mysql::Error: Access denied for user 'my_db_user'@'%' (using password: YES)

my_db_user has the LOCK TABLES and RELOAD rights (among others) on %.

Mysql Engine is 5.6.13.

What am I missing here ?

Solution

You can't do that on RDS.

FLUSH [LOCAL] TABLES WITH READ LOCK; is not supported. My assumption is that it might interfere with whatever their "rdsadmin" user (visible in the processlist) is doing to monitor or manage your instance.

If you're trying to use mysqldump, then --single-transaction is the way to go:

https://forums.aws.amazon.com/thread.jspa?threadID=57642#

Otherwise, if you're trying to block writes, the only option I know would be to lock all of the tables manually or with an automated script that can enumerate the tables and acquire the locks.