one of our backup slave systems had to be restarted and we forgot to start the slave. It was a few days before someone noticed and when we now try to start the slave we get an error:
Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Running show slave status, shows the information below:
Master_Log_File: mysql-bin.001067
Read_Master_Log_Pos: 137138436
Relay_Log_File: relay-bin.000267
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.001067
I’m pretty sure I understand what’s happening, in looking at the master directory where the bin files are stored, the earliest one is “mysql-bin.001077”. Basically, b/c of the late restart, the slave is now to far behind to catch up via bin files, is that correct?
What is the best course of action? does the slave data have to be re-loaded?
Thanks
I solved the problem by dumping a copy of another slave and following the instructions found here:
[URL=“http://MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program”][/URL]
Specifically:
It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave:Stop the slave’s SQL thread and get its current status:mysql> STOP SLAVE SQL_THREAD;mysql> SHOW SLAVE STATUS;From the output of the SHOW SLAVE STATUS statement, the binary log coordinates of the master server from which the new slave should start replicating are the values of the Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote those values as file_name and file_pos.Dump the slave server:shell> mysqldump --master-data=2 --all-databases > dumpfileRestart the slave:mysql> START SLAVE;On the new slave, load the dump file:shell> mysql < dumpfileOn the new slave, set the replication coordinates to those of the master server obtained earlier:mysql> CHANGE MASTER TO → MASTER_LOG_FILE = ‘file_name’, MASTER_LOG_POS = file_pos;The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such parameters as necessary.
This URL was also interesting (if you want to do it one db at a time with minimal downtime):
[URL=“http://www.elevatedcode.com/articles/2008/03/02/creating-a-mysql-slave-from-a-running-master/”][/URL]
Yeah, reloading data was the only solution you could take in this case.
For the future I’d suggest you to look at the ‘expire_logs_days’ variable - maybe you have enough disk space to keep old binlogs longer.
Also any script monitoring replication status would save you a lot of time.
Btw. “show binary logs” and “purge binary logs to …” commands are very handful in doing smart binlog rotating scripts )