When MySQL shows the error “Could not find first log file name in binary log index file” it means that mysql is looking for a bin-log file that is not shown in the bin-log index file. This does not necessarily mean the replication is completely broken.
What does this error mean?
[Slave]$ mysql -e "SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.1
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.000008
Read_Master_Log_Pos: 106
Relay_Log_File: log-relay-bin.000045
Relay_Log_Pos: 4
Relay_Master_Log_File: log-bin.000008
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_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'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
This error message means that the mysql slave server tried to connect to the master server to retrieve the bin-logs. The slave knows the position he stopped retrieving data from the master. Knowing its last retrieved position, the slave tries to fetch the next position from the master’s bin-log, but the master server does not have the bin-log file in the index.
In detail, the master server writes the bin-log files into the mysql data directory. Each file has a number which is incremented when each new bin-log file is created. When a new file is created, the file is also written to an index file and when a file is deleted it is removed from the index file. A mysql data directory will look similar to this.
[Maser]$ ls -l /var/lib/mysql/log-bin.*
-rw-rw---- 1 mysql mysql 658K Feb 25 13:16 log-bin.000001
-rw-rw---- 1 mysql mysql 11K Feb 26 11:48 log-bin.000002
-rw-rw---- 1 mysql mysql 125 Feb 26 13:58 log-bin.000003
-rw-rw---- 1 mysql mysql 9.8K Feb 27 10:26 log-bin.000004
-rw-rw---- 1 mysql mysql 125 Feb 27 10:43 log-bin.000005
-rw-rw---- 1 mysql mysql 12K Mar 6 10:01 log-bin.000006
-rw-rw---- 1 mysql mysql 295 Mar 10 15:20 log-bin.000007
-rw-rw---- 1 mysql mysql 125 Mar 10 15:33 log-bin.000008
-rw-rw---- 1 mysql mysql 125 Mar 10 15:22 log-bin.000009
-rw-rw---- 1 mysql mysql 385 Mar 10 15:46 log-bin.000010
-rw-rw---- 1 mysql mysql 2.9K Mar 10 15:54 log-bin.000011
-rw-rw---- 1 mysql mysql 211 Mar 10 15:55 log-bin.000012
-rw-rw---- 1 mysql mysql 126 Mar 10 15:56 log-bin.000013
-rw-rw---- 1 mysql mysql 126 Mar 10 15:56 log-bin.000014
-rw-rw---- 1 mysql mysql 107 Mar 10 15:59 log-bin.000015
-rw-rw---- 1 mysql mysql 450 Mar 10 15:59 log-bin.index
When the bin-log is missing in the index, it can be because the bin-logs have already rotated so far that the last retrieved position is already in a bin-log that has been deleted, but I have realised that in some situations the index file is not managed correctly. I did not find out yet what causes this problem, but when I checked the log-bin.index, which is a plain text file, one bin-log file was missing.
[Maser]$ cat /var/lib/mysql/log-bin.index
/var/lib/mysql/log-bin.000001
/var/lib/mysql/log-bin.000002
/var/lib/mysql/log-bin.000003
/var/lib/mysql/log-bin.000004
/var/lib/mysql/log-bin.000005
/var/lib/mysql/log-bin.000006
/var/lib/mysql/log-bin.000007
/var/lib/mysql/log-bin.000008
/var/lib/mysql/log-bin.000009
/var/lib/mysql/log-bin.000011
/var/lib/mysql/log-bin.000012
/var/lib/mysql/log-bin.000013
/var/lib/mysql/log-bin.000014
/var/lib/mysql/log-bin.000015
In this example the log-bin.000010 is missing but as shown before the file exists in the mysql data directory. This indicates that there is a problem in the index file.
Important: before you start editing the log-bin.index file, stop the mysql server. Never change files in the mysql data directory while the mysql server is running.To fix this problem in the bin-log index file, stop the mysql server and make sure it is stopped. Now the log-bin.index file can be corrected. The corrected bin-log index file should look like this.
[Maser]$ cat /var/lib/mysql/log-bin.index
/var/lib/mysql/log-bin.000001
/var/lib/mysql/log-bin.000002
/var/lib/mysql/log-bin.000003
/var/lib/mysql/log-bin.000004
/var/lib/mysql/log-bin.000005
/var/lib/mysql/log-bin.000006
/var/lib/mysql/log-bin.000007
/var/lib/mysql/log-bin.000008
/var/lib/mysql/log-bin.000009
/var/lib/mysql/log-bin.000010
/var/lib/mysql/log-bin.000011
/var/lib/mysql/log-bin.000012
/var/lib/mysql/log-bin.000013
/var/lib/mysql/log-bin.000014
/var/lib/mysql/log-bin.000015
Add the missing file to the index file, save the changes and start the mysql server again. The replication on the slave has stopped and will not start automatically.
[Slave]$ mysql -e "SLAVE START;"
With the above command, replication will be restarted. After a few seconds you can check the status of the replication again. The replication should start to catch up, or might already have caught up, with the master.
[Slave]$ mysql -e "SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.1
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.000015
Read_Master_Log_Pos: 118408
Relay_Log_File: log-relay-bin.000056
Relay_Log_Pos: 118511
Relay_Master_Log_File: log-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 118408
Relay_Log_Space: 118809
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
The above output shows that the replication has already caught up with the master and the error information is now gone.
Read more of my posts on my blog at http://blog.tinned-software.net/.
