Re: Replication fails when mysql has been idle for a long time

[Christer Palm]

Rick James Wrote:
-------------------------------------------------------
> "Idle for a long time" is probably irrelevant.
>
> You probably had sync_binlog=0; changing it to 1
> probably fixes _future_ occurrences. For the
> current situation, doing CHANGE MASTER to point to
> the next binlog is probably the solution.

Sorry, but that's not it. I do have sync_binlog=1. The relevant parts of my my.cnf:

log-bin=mysql-bin
relay-log=mysqld-relay-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_binlog_size=200M
expire_logs_days=14

The problem has occurred multiple times on multiple servers, so it's not a one-off event either. This is an excerpt from the log files;

# Slave sucessfully starts replication on 2013-11-02:

131102 19:05:53 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 39439, relay log './mysqld-relay-bin.000001' position: 4
131102 19:05:53 [Note] Slave I/O thread: connected to master 'repl@xxxx:3306',replication started in log 'mysql-bin.000003' at position 39439

# About 7 weeks later the master crashes and comes back up after the crash:

131221 13:23:29 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 51299, file name ./mysql-bin.000003
131221 13:23:29 InnoDB: Started; log sequence number 0 134239
131221 13:23:29 [Note] Recovering after a crash using mysql-bin
131221 13:23:30 [Note] Starting crash recovery...
131221 13:23:30 [Note] Crash recovery finished.

# Note that mysql-bin.000003 was still the current binlog and that it's present and successfully used to recover the master database.
# However, looking at the slave side as it reconnects to the master a little while later:

131221 13:27:21 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
131221 13:27:21 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
131221 13:27:21 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000003', position 51299

# We can see that mysql-bin.000003 now seems to be GONE. Checking the file system confirms that mysql-bin.000003 is no longer around.

Regards,
Christer Palm



Edited 2 time(s). Last edit at 12/27/2013 06:45AM by Christer Palm.