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:
|
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.
|
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:
|
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.
Resources
RELATED POSTS
Now if you can figure out a way to set SQL_SLAVE_SKIP_COUNTER in RDS ..
On the topic of RDS it’s interesting to note that if you make use of in-memory tables the schemes that Amazon does to perform backups does not promise data integrity. An application that I support makes extensive use of in memory tables, it sucks, causes a bunch of problems like that. Fortunately I haven’t had to use RDS in 6 months, totally moved out of that bastard cloud! woohoo.
nate, mysql.rds_skip_repl_error procedure is exactly the “SET GLOBAL SQL_SLAVE_SKIP_COUNTER” call from within RDS, available since MySQL versions 5.1.62 and 5.5.23.
Hi Aurimas, Thanks for the tip.
I am also in the process of migrating from RDS to Percona on EC2. Interesting to know if your use case of running pt-upgrade involved using slow log queries to compare RDS vs ‘Percona on EC2’. If so what method you used to convert RDS slow query log entries in a table format to a slog log txt format. (Here is one which I plan to use https://gist.github.com/1484489 ).
Jasib, –
indeed it did and I have used the same script you posted here for conversion – it was very useful!
Cheers
Hi all,
How to setup replication from external mysql to RDS.I setup everything fine. I am getting below error.I have given all permissions in security group also.
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.152.*.*
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 30682489
Relay_Log_File: relaylog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.plugin,innodb_memcache.config_options,innodb_memcache.cache_policies,mysql.rds_replication_status,mysql.rds_history
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 30682489
Relay_Log_Space: 1116
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 140324 11:52:00
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.47 sec)
mysql>
Thanks
Hi, anu –
our MySQL community forum is where you should try asking this questions. It looks like RDS just can’t connect to external mysql server. And in fact if it’s external to AWS, using an internal IP is probably not going to work either way.
Aurimas
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?