I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
There are four(4) things to look for to see if a binlog is corrupt on not:
BINLOG MAGIC NUMBER
Back on Dec 26, 2011, I wrote about a based-64 number that sits at the top of any binlog (hex value
For any binlog position, there is a a corresponding filesize. In other words, whenever mysqld records a SQL command, it does the following:
In light of this, you can run
If there are no writes going on (low writes,high reads), you could compare the filesize of the binlog with the reported binlog position.
Get both values and compare.
If the binlogs are in /var/lib/mysql
If there are no writes going on,
If there are writes going on,
You should never get
The mysqlbinlog utility will dump a text representation of the binlog. If any of the output is garbled, then it it corrupt. However, don't jump to any conclusions if you see this:
BINLOG ' WFxKTRNJAAAAPwAAAKY/YwAAABsAAAAAAAEACHdlYmVkaTMwAA1QYXJ0bmVyQ29uZmlnAAQICA8P BC0AYwAA WFxKTRhJAAAAXAAAAAJAYwAAABsAAAAAAAEABP//8BoSAAAAAAAAggMAAAAAAAAJUEFDS0NPVU5U Azc3MfAaEgAAAAAAAIIDAAAAAAAACVBBQ0tDT1VOVAM3NzI= '/*!*/;
That is actually a normal signature at the beginning of a mysqlbinlog output. I wrote about this back on Feb 04, 2011 : MySQL master binlog corruption. This is the only exception. Any garbled output from mysqlbinlog that does not look like this is a definite sign of binlog corruption.
This is a double-edged sword because of how replication works. Nevertheless, it can still be helpful in two aspects. Keep in mind that a relay log has the exact same characteristics as a binary log.
On a Slave, the IO Thread would parse the incoming binlog entries from the Master and record them in the Slave's current relay log. It is actually running a process like mysqlbinlog. Thus, the mechanism used by the IO Thread is simple:
If Step 3 ever fails, this means the IO thread could not parse in the next incoming SQL.
The Slave will reveal this failure by writing in its error log something like this:
The fact that error number 1236 is a registered error condition shows that this
The Slave knows what position it needed from the header (Binlog Comments) it read before expecting the incoming SQL. There are two causes for this:
If there was any network transmission noise or dropped packets, that slight "burp" interrupts the reading of the SQL statement. Thus, it is possible for the header comments to be written and not the SQL that follows.
If the binlog on the Master was corrupt to begin with, the IO thread on the Slave will fail for a more obvious reason: GARBAGE IN, GARBAGE OUT !!!
The error message example says
When you run
replication is fine. It was
but if you see
BANG !!!...The master binlog was corrupt anyway (
You would need to mysqldump the Master, load it onto the Slave, and setup replication from scratch.
It should go without saying that binlogs don't normally get corrupted. If this is a recurring problem, you likely have an underlying problem with your system's stability.
Here's a snippet I use to validate logs after sending them to an offsite archive:
If the output matches this pattern, if the size displayed is correct, and if the name of the next log is correct, then this is reasonable confirmation that your log is intact.
This test is only valid, of course, for log files that the server has finished writing... logs that are still open won't have a rotate event at the end.