Can’t connect MySQL EC2 master to RDS slave

amazon ec2amazon-rdsmysql-replication

I am having issues setting up replication between a MySQL RDS instance and an external master on EC2. As far as I can tell, everything is set up correctly but the slave never reaches the master and does not update.

Master is MySQL version 5.5, I've tried setting up slave with 5.5, 5.6, and 5.7 to check if version incompatibility was the problem, but get the same result regardless of version.

Master EC2 and slave RDS are in the same region and VPC, and their security groups have access to each other on port 3306.
I thought it could be a firewall issue, so I used the same settings to create an EC2 slave (version 5.5) in the same security group as the RDS slave, and that one was able to connect, so that's probably not it.

The EC2 slave is the same size, so it's likely not a space problem.

I set up the connection with the AWS RDS tool:

call mysql.rds_set_external_master \
    ('[ec2_master].compute.amazonaws.com', \
     3306, \
     '[master_username]', \
     '[master_password]', \
     'mysql-bin.000015', \
     17072930, \
     0);

The slave remains in 'connecting' status and displays no error in Last_IO_Errno, but the log gives the following (repeats same messages over and over again):

[Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000015' at position 17072930, relay log
'/rdsdbdata/log/relaylog/relaylog.000001' position: 4
[Note] Error reading relay log event: slave SQL thread was killed
[ERROR] Slave
I/O: error connecting to master '[master_username]@[ec2_master].compute.amazonaws.com:3306' – retry-time: 60 retries: 1,
Error_code: 2003
[Note] Slave I/O thread killed while connecting to master
[Note] Slave I/O thread exiting, read up to log 'mysql-bin.000015', position
17072930

Here is the output of 'show slave status' for my RDS instance:

               Slave_IO_State: Connecting to master
                  Master_Host: [ec2_master].compute.amazonaws.com
                  Master_User: [master_username]
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 17072930
               Relay_Log_File: relaylog.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 17072930
              Relay_Log_Space: 950
              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: NULL
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: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_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

What am I doing wrong on the EC2->RDS setup?

Best Answer

[ec2_master].compute.amazonaws.com looks like the hostname for a public IP address. You should be using the private IP address of the EC2 machine, inside VPC.

RDS instances may not have access to the VPC's DNS resolver, which would otherwise cause the public hostname to resolve to the private IP address for queries that originate from inside, if the VPC is configured correctly... but in any event, you'll want to use the private IP.

Related Topic