Mysql – Replication from EC2 Master to RDS Slave – Connecting to master

amazon ec2amazon-rdsMySQLreplication

I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added since the snapshot.

I am following the instructions at this link:

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

When I call mysql.rds_start_replication , SHOW SLAVE STATUS\G shows the following:

               Slave_IO_State: Connecting to master
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

From my Googling on the subject matter, I'm led to believe this is a network/firewall problem. I've checked that my RDS has a security group allowing all network traffic with the source machine (both private IP and Elastic IP) and the source machine has a security group allowing all network traffic with the RDS instance IP address.

The master server has a user set up with replication privileges.

I'm sure there must be something easy I'm missing here.

I've tried running flush hosts on the master server.

How do I get the replication to proceed successfully?

Some additional information:

  1. The master server is version 5.5.33
  2. The slave server is version 5.6.13
  3. The master server is using old_passwords=1
  4. The slave server is using old_passwords=0

I've tried setting SESSION old_passwords=0 on the master, creating a new replication user with a new, long hash format, but still can't get replication to start.

The error I get is:

        Last_IO_Errno: 2049

which seems to relate to the authentication protocol.

Do I need to completely restart the MySQL master server, with old_passwords=0 in the my.cnf file?

Best Answer

Since you control my.cnf on EC2, you have two options, neither of which require restarting mysql

OPTION #1 : Change old_password Protocol for EC2

  1. Stop replication to the RDS Slave
  2. As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0;
  3. Start replication to the RDS Slave

OPTION #2 : Lower the Security for EC2

  1. Stop replication to the RDS Slave
  2. As root@localhost run this : SET GLOBAL secure_auth = OFF;
  3. Start replication to the RDS Slave

For more information, please read the old_passwords and secure_auth MySQL Documentation.

Give it a Try !!!

UPDATE 2017-07-20 16:32 EDT

Neither of these methods work for MySQL 5.7 as both options are deprecated.

Related Question