Setting up Master – Slave replication: error connecting to master (Error_code: 2003)

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 'slave_user@000.000.000.00' - 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 'slave_user@000.000.000.00: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:

Error: 2003

I removed the bind-address from the master

Errno: 1045

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';

instead of

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Error 1236

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

Error 1236

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!

Error code 2003:

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.

In the 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.)