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';
FLUSH PRIVILEGES;
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';
FLUSH PRIVILEGES;
I also tried with a user that has 1.2.3.4 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);
CALL mysql.rds_start_replication;
When I try again (with a different user, for example), then I first stop it like this:
CALL mysql.rds_stop_replication;
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>
Master_Port: <my_port>
Connect_Retry: 60
Master_Log_File: bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: relaylog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.000006
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,innodb_memcache.cache_policies,mysql.rds_history,innodb_memcache.config_options,mysql.rds_configuration,mysql.rds_replication_status
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 286
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
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