MySQL RDS instance in "read-only" mode

When we are doing DB migration or upgrade in MySQL, we may want to set the DB instance in "read-only" mode to prevent any modifications to the tables. Typically, there are two ways to achieve this as described in this link

• Set DB as read-only

mysql>FLUSH TABLES WITH READ LOCK;

mysql>SET GLOBAL read_only = 1;

• Revoke insert and update privileges

mysql>REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';

mysql>FLUSH PRIVILEGES;

However, when you are using Amazon RDS, typically, we want to use the "db parameter group" option to change many of such parameters. There is one such parameter called "read_only" as shown in screenshot below:-

This particular parameter takes the value of "{TrueIfReplica}", 1 and 0. So if we set this particular parameter to "1", then DB instance becomes read-only. Additional advantage of this property is that it is "dynamic" which means that no DB reboot is needed. 

NOTE - If you are using a common DB parameter group for a set of DB instances but would like to only apply the read_only parameter to one instance, then you will need to "copy" the DB parameter group, then modify the particular db instance in question to add this newly made copy as it parameter group. Pl. note you will have to select "apply immediately" option in the modify DB step. Once you do, the instance will sync and will say "pending till reboot", which mean you will have to decide whether to reboot right away or can wait the next backup window of RDS instance where it will get automatically rebooted. 

Once the instance is in read-only mode, the insert will fail as below:-

mysql> insert into test.MyGuests (firstname,lastname,email,reg_date) values ('j

oe1','smith1','joe1@test.com',now());

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

To revert, you can simply make the "read_only" parameter "0" , save the changes and wait for the instance to apply the changes. After which you should be able to insert again

mysql> insert into test.MyGuests (firstname,lastname,email,reg_date) values ('joe1','smith1','joe1@test.com',now());

Query OK, 1 row affected (0.02 sec)