How to STOP SLAVE on Amazon RDS read replica

December 20, 2012

Author

Aurimas Mikalauskas

Cloud

Insight for DBAs

MySQL

Share this Post:

We are doing a migration from Amazon RDS to EC2 with a customer. This, unfortunately, involves some downtime – if you are an RDS user, you probably know you can’t replicate an RDS instance to an external server (or even EC2). While it is annoying, this post isn’t going to be a rant on how RDS can make you feel locked in. Instead, I wanted to give you a quick tip.

So here’s the thing – you can’t stop replication on RDS read replica, because you don’t have (and won’t get) privileges to do that:

replica&gt; STOP SLAVE;
ERROR 1045 (28000): Access denied for user 'usr'@'%' (using password: YES)

1 2	replica> STOP SLAVE; ERROR 1045 (28000): Access denied for user 'usr'@'%' (using password: YES)

Normally, you don’t want to do that, however we wanted to run some pt-upgrade checks before we migrate and for that we needed the read replica to stop replicating. Here’s one way to do it:

WARNING! Resuming replication gracefully only works if you run RDS with MySQL version 5.1.62 or 5.5.23 and up.

master&gt; CREATE DATABASE percona;
master&gt; CREATE TABLE percona.test (id int unsigned auto_increment not null primary key);

replica&gt; INSERT INTO percona.test VALUES (1);

master&gt; INSERT INTO percona.test VALUES (1);

replica&gt; show slave statusG
...
     Slave_SQL_Running: No
            Last_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'percona'. Query: 'INSERT INTO test VALUES (1)'

1

2

3

4

5

6

7

8

9

10

11

master> CREATE DATABASE percona;

master> CREATE TABLE percona.test (id int unsigned auto_increment not null primary key);

replica> INSERT INTO percona.test VALUES (1);

master> INSERT INTO percona.test VALUES (1);

replica> show slave statusG

...

Slave_SQL_Running: No

Last_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'percona'. Query: 'INSERT INTO test VALUES (1)'

Of course, for that to work, you will also have to disable read_only mode, which you can do by going to AWS Console and changing value for variable “read_only” from its default “{TrueIfReplica}” to “0”.

When we’re done with pt-upgrade checks, I will just run the following to resume replication:

replica&gt; CALL mysql.rds_skip_repl_error;
master&gt; DROP DATABASE percona;

1 2	replica> CALL mysql.rds_skip_repl_error; master> DROP DATABASE percona;

Note: If you are running an earlier MySQL version and it does not have the rds_skip_repl_error procedure, you can try removing the conflicting record from replication slave and replication should resume shortly. That worked for me.