RDS MySQL 5.5 replication to External server (ec2)

Question

I'm looking for some help with a specific issue trying to migrate an RDS MySQL to a external server.

Below is the arquitecture:

• RDS instance -> MySQL 5.5 (production dbs)
• EC2 instance -> Linux Ami2 , with MySQL 5.5 (new dbs instance)
• There are no network issues between them, port 3306 is open on Both Sides.

The idea is:

• Create a read only replica for the RDS instance that i want to migrate (done)
• Dump the databases from RDS ReadOnly, and import in the new EC2 instance (the new MySQL) (done)
• Stop replication on the new RDS replica (done)
• Set up the new MySQL (EC2 instance) as a slave (done)
• Connect new MySQL to RDS intance for the sync --> STUCK
• When the data is sycn, switch all applicattions that our company has to the new MySQL, delete RDS master (and RDS read only also), and leave the new MySQL instance as the principal. -> pending

I read all these documentation:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html https://www.percona.com/blog/2018/07/04/how-to-setup-replication-between-aws-aurora-external-mysql-instance/ https://www.aws.ps/how-to-configure-rds-replication-between-aurora-and-remote-mysql-server/

But in all cases, When i want to grant replication slave privileges for the new user created in RDS instance, i get this error:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'reply'@'%';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

so, when i check the salve status on the new MySQL instance, i get this:

Master command COM_REGISTER_SLAVE failed: Access denied for user 'reply'@'%' (using password: YES) (Errno: 1045)

I also notice that que MySQL version of all the documentation that y read is about RDS 5.6 and later.

so...

anyone ever had this problem? does anyone experiencie about migration from RDS mySQL 5.5 to External MySQL instance? I don't known if it's a specific problem related the mysql RDS version (5.5).

Maybe "GRANT REPLICATION SLAVE" doesn't exist in RDS MySQL 5.5 ¿?

I look forwar to if you need more information.

Thankyou for your time! :D

King regards.

Iván.

Answer 1

It looks like the current user can’t assign privileges:

ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

So in this case, you should check if you created the admin user and if you have grant privileges (WITH GRANT OPTION).

You can run the following command to know the privileges of the current user:

$ show grants\G