Is it possible to recover from a LOST_EVENTS incident from an RDS read replica?

Question

I was setting up a read replica of an RDS database for a reporting project, and issued an incorrect GRANT statement when configuring a user.

My RDS read replica stopped, with an error message on the slave:

The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

It seems that the recovery solution is to:

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

Except that the "root" user for an RDS read replica doesn't have rights to issue the stop slave command.

Is there a way to recover without deleting & recreating the read replica?

Answer 1

In this specific case (a failed grant statement) stopping the slave, skipping the event, and restarting the slave is likely a safe operation... you just have to do it the RDS way.

mysql> CALL mysql.rds_skip_repl_error;

This is one of several stored procedures installed on every RDS instance that partially make up for the lack of SUPER privilege.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html