I have setup MySQL Master - slave replication on Amazon EC2 instance.
- OS : RHEL 7.6
- MySQL Version: 8.0.13
- Master server IP : 192.168.1.128
- Slave server IP : 192.168.1.129
I have two EC2 instances on which I have configured, one as Master and another one as its slave.
I have created user "replicator" on master server for replication with " %" host.
After executing change master statement on slave server as mentioned below.
CHANGE MASTER TO MASTER_HOST='192.168.1.128', MASTER_USER='replicator', MASTER_PASSWORD='Replication@123', MASTER_HEARTBEAT_PERIOD=5, MASTER_LOG_FILE='mysql-bin.000063', MASTER_LOG_POS=195;
I have executed
start slave and
show slave status\G commandd on slave server i.e. on 192.168.1.129 .
I have got following error:
Slave_IO_State: Connecting to master Master_Host: 192.168.1.128 Master_User: replicator Slave_IO_Running: Connecting Slave_SQL_Running: Yes Last_IO_Error: error connecting to master firstname.lastname@example.org:3306 - retry-time: 60 retries: 8
Then I logged in to master MySQL server from slave server i.e.
mysql -u replicator -p -h192.168.1.128
Slave started sucessfully.
show slave status\G ouput:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
After all these I came to know that I have to repeat this step i.e.
mysql -u replicator -p -h192.168.1.128 from slave server every time whenever master MySQL service restarts. Otherwise replication will not take place and we will get the same error as describe above i.e. error connecting to master 'email@example.com:3306'
I have tested same replication on physical as well as on different VMs from other vendor. It's working fine, no need to authenticate slave user whenever master service restarts.