MySQL slaves and the “corrupted” relay log/binlog problem
When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken
091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not
parse relay log event entry. The possible reasons are: the master's
binary log is corrupted (you can check this by running 'mysqlbinlog' on
the binary log), the slave's relay log is corrupted (you can check this
by running 'mysqlbinlog' on the relay log), a network problem, or a bug
in the master's or slave's MySQL code. If you want to check the master's
binary log or slave's relay log, you will be able to know their names
by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log 'mysqld-bin.000780' position 786513508
Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a
FLUSH LOGS; before backing up your binary logs from the original master.
So, when you are recreating the binlog server from the backup, the
final slave arrives to the last statement wich obviously is not
complete/correct and booom, it stops.The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a
STOP SLAVE;
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;
SLAVE START;
and… problem solved!
(remember to adjust file name and position to suite your needs)
Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)
No comments:
Post a Comment