3

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?

2
+50

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.

  • Since posting my question I've already completed the migration of the production MySQL database (using mysqlbinlog to apply updates missing since the baseline dump) but I've tried the above in a test instance and it worked - thanks! – Matt Healy Apr 21 '14 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.