I’m configuring this across two different servers on two different hosting companies.
Here is the tutorial I’ve followed for setting this up: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
The master my.cnf settings are as follows:
bind-address = 00.000.000.00 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = mydatabase
On the slave:
server-id = 2 relay-log = /var/log/mysql/mysql-relay-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = mydatabase
Everything looks good until I try and check
mysql> SHOW SLAVE STATUSG
*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 000.000.000.00 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'firstname.lastname@example.org' - retry-time: 60 retries: 3 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 180104 23:22:36 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
I’ve checked the error log as well and can see this:
2018-01-04T23:05:10.948738Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'email@example.com:3306' - retry-time: 60 retries: 6, Error_code: 2003
I’ve checked the firewall and the port, which looks correct. I’m not sure what else I can check?
So, I actually had several issues that may come in handy to walk through for others:
I removed the
bind-address from the master
Access denied for user
I explicitly set the IP address for the slave user:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'000.000.000.00' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Got fatal error 1236 from master when reading data from binary log: ‘log event entry exceeded max_allowed_packet
It turns out the default
max_allowed_packet is 1MB for <= 5.6.5 and 4MB for >= 5.6.6.
I set both of these manually to 16MB as per this article
Got fatal error 1236 from master when reading data from binary log: ‘bogus data in log event
I followed the instructions in this answer
That solved the last of my errors and the replication appears to be working as expected now!
indicates that the network connection has been refused. You should
check that there is a MySQL server running, that it has network
connections enabled, and that the network port you specified is the
one configured on the server.
I think you’ve already established that the master server is running.
You can check whether you’re able to connect from the slave to the master with:
mysql -u slave_user -p -h $master_ip_address -P 3306
Just to be sure, you can explicitly specify the port in the
CHANGE MASTER TO command by adding
MASTER_PORT=3306. (This was the solution to another question where error code 2003 was the issue.)
To check that slave_user has the right grants, run this on the master:
SHOW GRANTS FOR slave_user@'%';
(That should respond with something about “REPLICATION SLAVE ON *.*”)
It might also be worth checking out the documentation on bind-address.
CHANGE MASTER TO MASTER_HOST command, note that there is obviously a difference between local and external IP addresses, and you should use the external IP address here since you’re replicating across the Internet. (On that note, once you’ve sorted out the replication problem, it probably makes sense to enable encryption.)