13

Our setup:

  • Master: MariaDB 10.0.21
  • Slave: MariaDB 10.0.17

Replication was working fine until recently at which point the slave's DBs had to be restored from a dump. I performed all of the necessary steps: Dump the master's DBs, transfer the dump to the slave, drop the old DBs, execute the dump to restore the DBs, execute the appropriate CHANGE MASTER command, and finally START SLAVE.

I am receiving the 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'

The first log file that the slave needs from the master is mysql-bin.000289. I can see that this is present on the master: enter image description here

I can also see that the binary log index on the master seems to have an entry for this log file: enter image description here

Still replication is not working - I keep getting the same error. I'm out of ideas - what should I check next?


Updated: Output of SHOW SLAVE STATUS\G as requested:

MariaDB [(none)]> SHOW SLAVE STATUS\G
--------------
SHOW SLAVE STATUS
--------------

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 127.0.0.1
                  Master_User: replication
                  Master_Port: 1234
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000289
          Read_Master_Log_Pos: 342
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000289
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: xxx_yyy,xxx_zzz
          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: 342
              Relay_Log_Space: 248
              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: 3
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

Additional requested information:

root@master [818 18:54:22 /var/lib/mysql]# ls -l /var/lib/mysql/mysql-bin.000289
-rw-rw---- 1 mysql mysql 1074010194 May 19 03:28 /var/lib/mysql/mysql-bin.000289
root@master [819 18:54:29 /var/lib/mysql]# ls mysql-bin.00029*
mysql-bin.000290  mysql-bin.000291  mysql-bin.000292 #(Yes, it was created)
root@master [821 18:56:52 /var/lib/mysql]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6345382
Server version: 10.0.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000279 | 1074114047 |
| mysql-bin.000280 | 1074004090 |
| mysql-bin.000281 | 1074035416 |
| mysql-bin.000282 | 1073895128 |
| mysql-bin.000283 | 1073742000 |
| mysql-bin.000284 | 1074219591 |
| mysql-bin.000285 | 1074184547 |
| mysql-bin.000286 | 1074217812 |
| mysql-bin.000287 | 1022733058 |
| mysql-bin.000288 |     265069 |
| mysql-bin.000289 | 1074010194 |
| mysql-bin.000290 | 1074200346 |
| mysql-bin.000291 |  617421886 |
| mysql-bin.000292 |     265028 |
+------------------+------------+
14 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye
root@master [821 18:57:24 /var/lib/mysql]# mysqlbinlog mysql-bin.000289 > /tmp/somefile.txt
root@master [822 18:58:13 /var/lib/mysql]# tail /tmp/somefile.txt 
# at 1074010124
#160519  3:28:59 server id 5  end_log_pos 1074010151    Xid = 417608063
COMMIT/*!*/;
# at 1074010151
#160519  3:28:59 server id 5  end_log_pos 1074010194    Rotate to mysql-bin.000290  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@master [823 18:58:31 /var/lib/mysql]# 

/etc/my.cnf.d/server.cnf (excerpt):

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

Edit: Postion 342 does seem to exist:

root@master [826 12:15:33 /var/lib/mysql]# grep "end_log_pos 342 " /tmp/somefile.txt
#160517 14:43:13 server id 5  end_log_pos 342   Binlog checkpoint mysql-bin.000288
1
  • Also beware as your master version is slightly more recent than your slave version. While slave version can be higher (because it will undoubtedly understand all command/functions/features), if the Master is more recent, it could invoke something the Slave has never heard of. I suspect it would not occur in such a minor revision difference, but can't be ruled out and would undoubtedly be Arcane in the extreme and difficult to find. Also: the official line is that more recent master is not supported. Jun 14, 2018 at 21:23

4 Answers 4

6

You seem to not be connecting to the master that you think you are. Per your binary logs on the master you seem to have:

#160519 3:28:59 server id 5

But per SHOW SLAVE STATUS we see:

         Master_Server_Id: 3

And further you seem to be connecting on localhost, but you implied your master/slave are on different hosts:

              Master_Host: 127.0.0.1
2
  • 1
    We're using SSH tunneling (with autossh) to expose the remote master locally at 127.0.0.1:3305. I noticed the Master_Server_Id as well, but I figured that was just a remnant from long ago when we were using a different master. I was expecting the value in SHOW SLAVE STATUS to update once we had fully reestablished replication. Regardless, this is an awesome suggestion; I'll triple-check that we are, indeed, connecting to the right master!
    – rinogo
    Jun 2, 2016 at 19:02
  • 1
    Thank you so much for pointing me in the right direction! We were, indeed, connecting to the wrong master. I confirmed it by doing a telnet 127.0.0.1:3305 - I could see that the reported MySQL version matched the version from the old master. I think the root of the problem was likely due to some DNS quirks on our network - it seems that the autossh connection was erroneously established to domain.com, even though it was configured to connect to db.domain.com. Thanks so much yet again.
    – rinogo
    Jun 2, 2016 at 19:39
12

If all else fails, you may find that you need to reset the slave and restart replication. From https://www.redips.net/mysql/replication-slave-relay-log-corrupted/ :

# First note current settings (note Relay_Master_Log_File and Exec_Master_Log_Pos)
mysql> show slave status\G
# then stop slave
mysql> stop slave;
# make slave forget its replication position in the master's binary log
mysql> reset slave;
# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.XXX', master_log_pos=XXX;
# start slave
mysql> start slave;
4
  • 1
    The OP has commented that another answer was correct (and they were connecting to the wrong instance). Dec 22, 2017 at 12:15
  • 5
    @yper-trollᵀᴹ - yes, but half the point of Stackexchange is for the archive of the questions, which inevitably land at the top of Google searches for other people with the same problem. I had the exact same problem myself, and this was the solution for me (especially as I literally spent hours trying to solve it, because most other pages only show the same other answers). The fact that the other "wrong" answer has 2 up-votes is testament to this. Dec 23, 2017 at 19:14
  • Yes, fair point. As long as this (suggestion) is meant to be used when all else fail (and clearly marked as such), that's fine. That's why I only commented and not downvoted. Dec 24, 2017 at 3:18
  • 1
    This answer worked for me when none of the other advice applied. (I was working off an existing, current binlog, had the master properly configured, etc.) It's a sound answer and frankly the RESET SLAVE; option should be mentioned more prominently above.
    – JD Baldwin
    Feb 7, 2019 at 22:24
5

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

One of the top most critical replication error Got fatal error 1236 It can be triggered by multiple reasons, one of them is the title of this question.

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

This error occurs when the slave server required binary log for replication no longer exists on the master database server.

So many scenarios can cause this:

  • The master server expired binary logs via system variable expire_logs_days (my.cnf if you set expire_logs_days old binlogs expire automatically and are removed; When MySQL opens a new binlog file, it checks the older binlogs, and purges any that are older than the value of expire_logs_days)
  • Someone manually deleted binary logs from master via PURGE BINARY LOGS command or via rm -f command
  • You have some cronjob which archives older binary logs to claim disk space

In order to resolve this problem, the only clean solution I can think of is to re-create the slave server from a master server backup or from other slave in replication topology.

Reference : mysql replication got fatal error

0
5

The error message is the answer.

Look at the output of SHOW BINARY LOGS query:

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000279 | 1074114047 |
| mysql-bin.000280 | 1074004090 |
| mysql-bin.000281 | 1074035416 |
| mysql-bin.000282 | 1073895128 |

There is no mysql-bin.000278 in the display.

Unless the binary logs rotated, the contents of mysql-bin.index is wrong.

Please compare the contents of mysql-bin.index with the binlogs files now existing and make sure they match. You can fix this on the Master with

mysql> PURGE BINARY LOGS TO 'mysql-bin.000279';

then go to the Slave and run

mysql> STOP SLAVE; START SLAVE;

Give it a Try !!!

3
  • Hi, Rolando! Thanks so much for your help! Unfortunately, I'm still confused. Did you mean mysql-bin.000288 instead of mysql-bin.000278? If so, mysql-bin.000288 does seem to exist. Will this still fix the problem?
    – rinogo
    May 23, 2016 at 21:08
  • PURGE BINARY LOGS TO 'mysql-bin.000279'; gave me an error (as was to be expected), since log "279" no longer existed (had been rotated out). PURGE BINARY LOGS TO 'mysql-bin.000288'; executed successfully, and deleted all binary logs up to "288". Unfortunately, I'm still getting the error.
    – rinogo
    May 25, 2016 at 15:27
  • Thanks so much for your detailed suggestions, Rolando! The problem ended up being that we were connecting to the wrong master (dba.stackexchange.com/a/140259/55530).
    – rinogo
    Jun 2, 2016 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.