AWS RDS read-only machine -- how to add new user

Question

I have two RDS mysql servers running, one is the main (master) rds server and the other is the read-only slave, which was created through the RDS console.

How would I create a new user account for the read-only machine, that would not have access to the main master machine? For example, when I run the following command:

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password';

I get:

The MySQL server is running with the --read-only option so it cannot execute this statement

I would like the user to have access to the following:

• YES (read-only): site-read.cyrnzp...
• NO (master): site-provisioned.cyrnzp...

How would this be done then?

Answer 1

Edit: Won't work on AWS RDS as sql_log_bin is privileged

Easiest way would be to create it on the master CREATE USER foo@localhost. This will replicate to the slave creating a user there as well.

Then to remove access on the master, SET SESSION sql_log_bin=0; DROP USER foo@localhost. The sql_log_bin=0 means that all statements in the same session aren't written to the binary log. All other server activity on different sessions is still replicated.

The result of this is the slave server won't see the DROP USER statement, which means the foo user is still there on the slave, but not on the master server.