Replicating AWS RDS MySQL databases to external slaves

Update: Using an external slave with an RDS master is now possible as well as RDS as a slave
with an external master

Connecting external MySQL slaves to AWS RDS mysql instances is one of the most wanted features, for example to have migration strategies into and out of RDS or to support strange replication
chains for legacy apps. Listening to binlog updates is also a great way to update search indexes or to
invalidate caches
.

As of now it is possible to access binary logs from outside RDS with the release of MySQL
5.6 in RDS
. What amazon does not mention is the possibility to connect external slaves to RDS.

Here is the proof of concept (details on how to set up a master/slave setup is not the focus here :-) )

First, we create a new database in RDS somehow like this:

soenke♥kellerautomat:~$ rds-create-db-instance soenketest --backup-retention-period 1 --db-name testing --db-security-groups soenketesting --db-instance-class db.m1.small --engine mysql --engine-version 5.6.12 --master-user-password testing123 --master-username root --allocated-storage 5 --region us-east-1 
DBINSTANCE  soenketest  db.m1.small  mysql  5  root  creating  1  ****  n  5.6.12  general-public-license
      SECGROUP  soenketesting  active
      PARAMGRP  default.mysql5.6  in-sync
      OPTIONGROUP  default:mysql-5-6  in-sync  

So first lets check if binlogs are enabled on the newly created RDS database:

master-mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.12 sec)

master-mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000060 |      120 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.12 sec)

Great! Lets have another check with the mysqlbinlog tool as stated in the RDS
docs.

But first we have to create a user on the RDS instance which will be used by the connecting slave.

master-mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.13 sec)

master-mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.12 sec)

Now lets have a look at the binlog:

soenke♥kellerautomat:~$ mysqlbinlog -h soenketest.something.us-east-1.rds.amazonaws.com -u repl -pslavepass --read-from-remote-server -t mysql-bin-changelog.000060
...
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
CREATE USER 'repl'@'%' IDENTIFIED BY PASSWORD '*809534247D21AC735802078139D8A854F45C31F3'
/*!*/;
# at 582
#130706 20:12:02 server id 933302652  end_log_pos 705 CRC32 0xc2729566  Query   thread_id=66    exec_time=0     error_code=0
SET TIMESTAMP=1373134322/*!*/;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

As we can see, even the grants have been written to the RDS binlog. Great! Now lets try to connect a real slave! Just set up a vanilla mysql server somewhere (local, vagrant, whatever) and assign
a server-id to the slave. RDS uses some (apparently) random server-ids like 1517654908 or 933302652 so I currently don’t know how to be sure there are no conflicts with external slaves. Might be
one of the reasons AWS doesn’t publish the fact that slave connects actually got possible.

After setting the server-id and optionally a database to replicate:

server-id       =  12345678
replicate-do-db=soenketesting

lets restart the slave DB and try to connect it to the master:

slave-mysql> change master to master_host='soenketest.something.us-east-1.rds.amazonaws.com', master_password='slavepass', master_user='repl', master_log_file='mysql-bin-changelog.000067', master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

slave-mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

And BAM, it’s replicating:

slave-mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: soenketest.something.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000068
          Read_Master_Log_Pos: 422
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 595
        Relay_Master_Log_File: mysql-bin-changelog.000068
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: soenketesting
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 422
              Relay_Log_Space: 826
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 933302652
                  Master_UUID: ec0eef96-a6e9-11e2-bdf0-0015174ecc8e
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

So lets issue some statements on the master:

master-mysql> create database soenketesting;
Query OK, 1 row affected (0.12 sec)
master-mysql> use soenketesting
Database changed
master-mysql> create table example (id int, data varchar(100));
Query OK, 0 rows affected (0.19 sec)

And it’s getting replicated:

slave-mysql> use soenketesting;
Database changed
slave-mysql> show create table example\G
*************************** 1. row ***************************
       Table: example
Create Table: CREATE TABLE `example` (
  `id` int(11) DEFAULT NULL,
  `data` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Kommentar schreiben

Kommentare: 23

  • #1

rb (Montag, 08 Juli 2013 04:26)

Does this mean it is possible to perform MySQL master-master replication with RDS and an external DB(s)?

  • #2

s0enke (Dienstag, 09 Juli 2013 03:05)

@rb: Probably not - as still you cannot replicate INTO RDS. But there is a possible workaround with tungsten: https://docs.continuent.com/wiki/display/TEDOC/Replicating+from+MySQL+to+Amazon+RDS

  • #3

Ludo (Donnerstag, 29 August 2013 22:56)

nice!

  • #4

Ross (Sonntag, 01 September 2013 16:00)

It would be nice to see the process for creating a slave from an existing rds master as there is no way to force a global write lock on the tables and you’d need to deal with dumps etc.

  • #5

Ludo.helder@gmail.com (Montag, 16 September 2013 17:43)

Question:
why do you set log_position=0 on the slave when it’s 120 on the master?

  • #6

Anonymous Coward (Montag, 23 September 2013 04:03)

Man. Your background is pretty, but offensive when trying to read your page.

  • #7

victoroloan (Mittwoch, 02 Oktober 2013 11:35)

Hi There,

master-mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’
does not work on me.

ERROR 1045 (28000): Access denied for user ‘repl’@’%’ (using password: YES)

  • #8

Neal (Mittwoch, 02 Oktober 2013 17:43)

You can now replicate TO and FROM RDS instances with outside MySQL instances:

To import into RDS:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

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