MySQL Slave Failed to Open the Relay Log

This problem is a little tricky, there are possible fixes that MySQL website has stated. Sad to say, the one’s I read in the forum and site didn’t fix my problem. What I encountered was that the relay-bin from my MySQL slave server has already been ‘rotated’, meaning deleted from the folder. This happens when the slave has been disconnected from the master for quite a long time already and has not replicated anything. A simple way to fix this is to flush the logs, but make sure the slave is stopped before using this command…

FLUSH LOGS;

Bring in a fresh copy of the database from the master-server and update the slave-server database. THIS IS IMPORTANT! Since if you don’t update the slave database, you will not have the data from the time you were disconnected until you reset the relay logs. So UPDATE YOUR SLAVE WITH THE LATEST DATABASE FROM THE MASTER!

Now when the logs are flushed,all the relay-bin logs will be deleted when the slave is started again. Usually, this fixes the problem, but when you start the slave and the failed relay log error is still there, now you have to do some more desperate measures… reset the slave. This is what I had to do to fully restore my MySQL slave server. Reseting the slaves restores all the settings to default… password, username, relay-log, port, table to replicate, etc… So better to have a copy of your settings first before actually do a slave reset. When your ready to rest the slave, do the command…

RESET SLAVE;

after which you should restore all your setting with a command something like…

CHANGE MASTER TO MASTER_HOST=.....

now start your server with…

SLAVE START;

check your slave server with…

SHOW SLAVE STATUS\G

look for …

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

both should be YES, if not, check your syslog if there are other errors encountered. I’ll leave this until here since this is what I encountered and I was able to fix it.

Edit 5/14/11:

There is a possible change that after executing the CHANGE MASTER command that you’ll receive this error below…

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

This can occur when the relay logs underย /var/lib/mysql were not properly cleaned and are still there. The next thing is to delete them manually, log back in to mysql, refresh logs, reset slave then execute the CHANGE MASTER command again. The file to delete would be relay-log.info .This should work now. Sometimes I don’t know why mysql can’t reset the slave logs.

11 Replies to “MySQL Slave Failed to Open the Relay Log”

  1. A great fix, thanks so much. This google search was yielding more bug reports than anything.

  2. I had the error “[ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Error_code: 1872” too. It was fixed by deleting files mysqld-relay-bin* and relay-log.info.

Leave a Reply

Your email address will not be published. Required fields are marked *