5

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 STATUS\G

*************************** 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?

10
  • That's a rather strange IP address: 'slave_user@000.000.000.00'.
    – Rick James
    Jan 5 '18 at 2:15
  • Well yes, I didn't post the actual ip address for the server
    – TH1981
    Jan 5 '18 at 2:50
  • What is the result of SHOW MASTER STATUS; when you run that on the Master ? Jan 26 '18 at 16:16
  • mysql> SHOW MASTER STATUS; +------------------+----------+-----------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-----------------+------------------+ | mysql-bin.000002 | 107 | mydatabase | | +------------------+----------+-----------------+------------------+ 1 row in set (0.00 sec)
    – TH1981
    Jan 26 '18 at 16:25
  • 1
    No, the versions don't have to match, but the slave version has to be >= the master version. So that should be fine. Are you able to connect from the slave to the master with the replication user? mysql -u slave_user -p -h $master_ip_address
    – dbdemon
    Jan 27 '18 at 16:55
7

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!

1
  • Error: 2003 remove bind addr on master server worked like a charm! :) Thanks
    – boomdrak
    Jan 2 '20 at 10:35
3
+50

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

9
  • Okay, the user can connect directly from the slave to the master. The slave right grants gives me this: GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY PASSWORD '####'. I've been checking with my hosting company as well to figure out why I'm getting blocked on the connection and they said the bind-address wasn't needed. It's causing me problems when I connect via Sequel Pro, so I commented it out and it actually connected to the server directly, though still getting the couldn't connect error on the Slave Status.
    – TH1981
    Jan 28 '18 at 16:52
  • Have you tried STOP SLAVE; and then re-running the CHANGE MASTER TO ... command after commenting out the bind-address? And maybe include the MASTER_PORT in that command as I mentioned in the answer above.
    – dbdemon
    Jan 28 '18 at 17:24
  • @TH1981 ... and then START SLAVE; and then SHOW SLAVE STATUS\G
    – dbdemon
    Jan 28 '18 at 19:34
  • well, the error message has changed: Master command COM_REGISTER_SLAVE failed: Access denied for user 'slave_user'@'000.000.000.00' (using password: YES) (Errno: 1045)
    – TH1981
    Jan 28 '18 at 21:22
  • @TH1981 And you definitely can log in from the slave to the master on the command-line with mysql -u slave_user -p -h $master_ip_address using the same password as in the CHANGE MASTER TO ... command?
    – dbdemon
    Jan 28 '18 at 21:34

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.