I am trying to set up replication between my external database (master) to an AWS RDS database (slave). Both are MySQL 5.6.
Please don’t mark this as a duplicate. I know others have asked a similar question, but the answers didn’t help me.
I am following the directions here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html.
I have loaded the data from mysqldump.
I got the RDS IP address like this:
host <RDS ENDPOINT>
I created a replication user on the source, limited to RDS IP:
CREATE USER repl_user5@'<RDS IP>' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user5'@'<RDS IP>' IDENTIFIED BY 'password';
I also tried with a user that is not limited to any IP:
CREATE USER repl_user4@'%' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user4'@'%' IDENTIFIED BY 'password';
I also tried with a user that has 18.104.22.168 as IP (one I know will fail to connect).
I verified that I can access that @’%’ user from my own computer:
mysql -h <source IP> -P <source port> -u repl_user4 -p
I don’t know of an independant way to test that a MySQL user has access from the RDS.
I verified that I can access the RDS admin user from the source server. (Not sure if that’s even needed.)
I double-checked the users and grants, show grants, etc.
Source has server-id. Binlogs are created. This was set up before the dump was created and MySQL restarted.
I start replication like this:
CALL mysql.rds_set_external_master ('<source IP>', '<source port>', 'repl_user4', 'password', 'bin.000006', 120, 0);
When I try again (with a different user, for example), then I first stop it like this:
I double-checked IP, password, user, port… many times.
ssl is disabled on the source (checked with SHOW GLOBAL VARIABLES LIKE ‘%ssl%’) so the issue isn’t the Master_SSL_Allowed = No setting on the RDS.
The result of SHOW SLAVE STATUS is the same regardless of which of the three users I try with. Slave_IO_Running is connecting and there is no listed error.
Slave_IO_State: Connecting to master
Master_Host: <my ip>
Master_User: <the user>
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
The RDS MySQL error log says the same regardless of which user I try with (the one with access to RDS IP, all IP, other IP)
2020-06-14 21:52:07 20197 (Note) Slave SQL thread initialized, starting replication in log 'bin.000006' at position 120, relay log '/rdsdbdata/log/relaylog/relaylog.000001' position: 4
2020-06-14 21:53:00 20197 (Note) Error reading relay log event: slave SQL thread was killed
2020-06-14 21:53:00 20197 (Note) Slave SQL thread exiting, replication stopped in log 'bin.000006' at position 120
2020-06-14 21:53:00 20197 (ERROR) Slave I/O: error connecting to master 'repl_user4@<source host>:<source port>' - retry-time: 60 retries: 1, Error_code: 2003
2020-06-14 21:53:00 20197 (Note) Slave I/O thread killed while connecting to master
2020-06-14 21:53:00 20197 (Note) Slave I/O thread exiting, read up to log 'bin.000006', position 120