Amazon RDS replication with external slave

amazon-web-servicesrdsreplication

I've recently configured a replication with an external MySQL master and RDS as slave but now I need to reverse that config and use RDS as master and the other instance as slave.
Actually I use a vpn and the other instance is outside AWS.

I changed the server host and password but I got this error:

141103 11:35:44 [ERROR] Error reading packet from server: Binary log is not open ( server_errno=1236)
141103 11:35:44 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open', Error_code: 1236
141103 11:35:44 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000007', position 284079763
141103 11:37:09 [Note] Error reading relay log event: slave SQL thread was killed

Perhaps I didn't get the right log filename and postion? but I can't find them in to RDS.

Best Answer

Finding the right position is kind of tricky.

  • Spin up a RDS read replica
  • stop the replication manually with mysql.rds_stop_replication
  • use mysql> SHOW SLAVE STATUS\G to get needed information for the next steps
  • the Exec_Master_Log_Pos variable will tell you the master binlog position the slave currently is
  • the binlog file name can be found in variable Relay_Master_Log_File

One additional advice: be careful with this setup. It is not officially supported by AWS to run a permanent replication to an external slave. Pleas see ...

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

Related Topic