[Pacemaker] mysql m/s failover: 'Could not find first log file name in binary log index file'

[Raoul Bhatia [IPAX]]

hi,

i'm starting a new thread to address a specific
"Could not find first log file name in binary log index file" error
upon failover.


background:
i currently have a two node mysql m/s setup.
expire_logs_days (was) set to 7 days
last failover happend > 7 days ago (therefore, binlogs have been purged)


i today tested another failover.
config before failover:
> node wdb01 \
>         attributes service="wdb" \
>         attributes standby="off" wdb02-log-file-wdb-mysql="mysql-bin.000015" wdb02-log-pos-wdb-mysql="24386"
> node wdb02 \
>         attributes service="wdb" \
>         attributes standby="off"


as stated, some binlogs, including mysql-bin.000015, have already been
purged from wdb02. current binlogs:

> -rw-rw---- 1 mysql adm      149 Apr 11 06:25 mysql-bin.000031
> -rw-rw---- 1 mysql adm      125 Apr 11 10:44 mysql-bin.000032
> -rw-rw---- 1 mysql adm      125 Apr 11 22:50 mysql-bin.000033
> -rw-rw---- 1 mysql adm      149 Apr 12 06:25 mysql-bin.000034
> -rw-rw---- 1 mysql adm      149 Apr 13 06:25 mysql-bin.000035
> -rw-rw---- 1 mysql adm      149 Apr 14 06:25 mysql-bin.000036
> -rw-rw---- 1 mysql adm      125 Apr 14 17:01 mysql-bin.000037
> -rw-rw---- 1 mysql adm      149 Apr 15 06:25 mysql-bin.000038
> -rw-rw---- 1 mysql adm      149 Apr 16 06:25 mysql-bin.000039
> -rw-rw---- 1 mysql adm      149 Apr 17 06:25 mysql-bin.000040
> -rw-rw---- 1 mysql adm      149 Apr 18 06:25 mysql-bin.000041
> -rw-rw---- 1 mysql adm      125 Apr 18 15:45 mysql-bin.000042
> -rw-rw---- 1 mysql adm      149 Apr 19 06:25 mysql-bin.000043
> -rw-rw---- 1 mysql adm      125 Apr 19 09:03 mysql-bin.000044
> -rw-rw---- 1 mysql adm  5366995 Apr 19 09:46 mysql-bin.000045
> -rw-rw---- 1 mysql adm      540 Apr 19 09:04 mysql-bin.index


the failover worked and wdb02 is up and running.
upon rejoin, wdb01 wanted to start syncing from mysql-bin.000015,
position 24386 (as saved in the cib).

this fails with error "Last_IO_Errno: 1236" and the message:
> 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'

given the circumstance, that the binlogs have been purged, this is
somewhat expected.

i wonder though, if there is a possibility to automagically trouble-
shoot this issue, as - as you can see from above - all binlogs up to
mysql-bin.000045 are empty.


what i can currently think of:

1. run a cronjob which periodically analyzes the binlogs and will update
the node's log-file and log-pos attributes if there are empty binlogs;
(that's the best method, i think)

2. restore the purged binlogs from a backup, hack the mysql-bin.index
file to re-include them, and hope that they will not be purged upon
replication restart?


any input on this?

thanks,
raoul
-- 
____________________________________________________________________
DI (FH) Raoul Bhatia M.Sc.          email.          r.bhatia at ipax.at
Technischer Leiter

IPAX - Aloy Bhatia Hava OG          web.          http://www.ipax.at
Barawitzkagasse 10/2/2/11           email.            office at ipax.at
1190 Wien                           tel.               +43 1 3670030
FN 277995t HG Wien                  fax.            +43 1 3670030 15
____________________________________________________________________