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.
Recent Comments