0

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
3
  • Where is the external master? How did you arrange for the RDS instance to have network connectivity to it? Jun 15 '20 at 16:23
  • External master is on an external server, not AWS. I opened the master server's MySQL listening port and created a replication user as described above. I am able to connect to the master server from my personal computer using the replication user (the one with access to '%').
    – user984003
    Jun 15 '20 at 17:21
  • I created a new RDS database and did everything again. That RDS is able to connect to my server. I wonder if there is some issue with stopping replication and starting it up again. I accidentally used the wrong password the very first time and so I had to stop and try again using mysql.rds_stop_replication;
    – user984003
    Jun 15 '20 at 17:35
0
  1. Mysql replication is a pull model. Communication happens from slave --> master.

  2. When it comes to AWS: We need to create a network between master and slave by creating routes tables/VPC peering(if master and slave are in different VPC) etc.

  3. Security group can be the issue in most of the cases, security group is stateful. We need to make sure that security group with proper inbound/outbound rules are attached to master and slave. In slave's security group Outbound rule with the master address as destination is needed and In master's security group Inbound rule with the slave address as destination is needed.

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.