Grants for locking and unlocking tables in MySQL RDS instances

Sometimes you will need lock tables privilege when trying to dump data from an RDS instances. To see if the current user has lock privileges, you can run the below command

$mysql> show grants for current_user\G;
*************************** 1. row ***************************
Grants for <rds_user>@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO '<rds_user>'@'%' IDENTIFIED BY PASSWORD 'XYZ' WITH GRANT OPTION

If you would like to grant all privileges for the RDS mysql root user, you can run the below query

$mysql>GRANT ALL PRIVILEGES ON `%`.* TO <rds_user>@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION;

Now you can run the "show grants" command to see an additional row displayed

mysql> show grants for current_user\G;
*************************** 1. row ***************************
Grants for <rds_user>@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO '<rds_user>'@'%' IDENTIFIED BY PASSWORD 'XYZ' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for <rds_user>@%: GRANT ALL PRIVILEGES ON `%`.* TO '<rds_user>'@'%'
 WITH GRANT OPTION
2 rows in set (0.00 sec)

Now to test if the locks are enabled, you can try the below queries

mysql> lock tables <db>.<table_nameA> READ;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from <db>.<table_nameA>;
+----------+
| count(*) |
+----------+
|   991225 |
+----------+
1 row in set (0.41 sec)

mysql> select count(*) from <db>.<table_nameB>;
ERROR 1100 (HY000): Table '<table_nameB>' was not locked with LOCK TABLES

To unlock the tables, you can run "unlock tables" command

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)