Vague replication error code 2005

Question

2015-04-07 13:49:53 23758 [ERROR] Slave I/O: error connecting to master 'replication'@master-host:3306' - retry-time: 60  retries: 1, Error_code: 2005

This was after attempting to do a change master to from previously running replication. The only change was the master host. The new guy I wanted to change master to is running from the same actual master.

This is using GTID so i was doing auto position=1 and not mucking with master_log_file master_log_position values.

I am able to connect to the new master using the replication credentials that show in mysql.slave_master_info via mysql client. Show grants confirms it has replication slave on .

I'm not seeing anything about denied connection attempts in the new master's error file, but I do see retry error attempts on the slave.

There's nothing more useful in show slave status\G

After getting frustrated I did a change master to back to the old master and it started running just fine.

I'm at a loss for how to continue trouble shooting this.

score 1 · Answer 1 · answered Apr 07 '15 at 19:30

Once you change the master_host, you have to redo everything else

It says so in the MySQL Documentation on CHANGE MASTER TO

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

Just specify everything again

STOP SLAVE;
CHANGE MASTER TO
    master_host='IP_of_new_host',
    master_port=3306,
    master_user='repluser',
    master_password='repluserpasswowrd',
    master_heartbeat_period=1,
    master_auto_position=1
;
START SLAVE;

GIVE IT A TRY !!!

score 1 · Answer 2 · answered Apr 08 '15 at 00:54

This appears to be client error 2005, which is "unknown host."

If you've configured the target master by hostname, it suggests a DNS resolution issue on that hostname by the slave host. The hostname is resolved using the slave machine's OS's DNS resolver, so the fact that you can connect to it, if you're trying from elsewhere, doesn't necessarily mean much compared to connecting from the command line on the slave machine itself.

You can prove or disprove this with a simple test -- connect to the master by IP address. If it works, that confirms it's an incorrect hostname or DNS isn't working properly for you, for some reason, when resolving that hostname.

Also, to confirm this, I tried the following on a MySQL 5.6.21 machine that was not a slave -- changed the master to a nonexistent hostname in a domain that I control, added minimal configuration needed to START SLAVE IO_THREAD; and voilà, I can duplicate this condition:

2015-04-08 00:44:15 18140 [ERROR] Slave I/O: error connecting to master 
'user@nonexistent-host.my-domain.domain:3306' - retry-time: 60  
retries: 1, Error_code: 2005

Vague replication error code 2005

2 Answers2

GIVE IT A TRY !!!