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

1

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 !!!

  • Maybe it has something to do w/ running percona builds but I most certainly can specify just master_host with previous user names, password and non standard ports defined. I had about half a dozen other slaves I moved over in a similar fashion. At anyway trying to give things another try, attempts to at least first reproduce the problem first failed (replication started back up as I hoped). Not sure what I did wrong/differently first time. – atxdba Apr 7 '15 at 19:37
  • Since it is the Slave I/O thread affected, you should also check your firewall, your network, and the IP you used. – RolandoMySQLDBA Apr 7 '15 at 19:47
1

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
  • Yeah I had found something similar before posting here. The previous grants were a wild carded ip type grant though. I even tried renaming to just @% briefly. As I mentioned in a previous retry it all of a sudden worked as planned, alas I didn't save the terminal history to be certain of what I did or didn't do. There had been plenty of times I've not gotten GTID replication right the first time but there were must more specific errors. Further confounding the situation is there a another slave in the same DC that went w/o issue. I clearly forgot some step but erased the history. – atxdba Apr 8 '15 at 5:08

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.