Contents
How to monitor if MySQL binlog files get corrupt?
Back on Dec 26, 2011, I wrote about a based-64 number that sits at the top of any binlog (hex value 0xfe 0x62 0x69 0x6e) regardless of MySQL version and regardless of binlog format (STATEMENT,ROW,MIXED). If this hex value is not in the header of the binlog, it is definitely corrupt.
How to use mysqlbinlog to read binary log files?
To execute the internal-use BINLOG statements used by mysqlbinlog, the user requires the BINLOG_ADMIN or SUPER privilege, or the REPLICATION_APPLIER privilege plus the appropriate privileges to execute each log event. You can use mysqlbinlog to read binary log files directly and apply them to the local MySQL server.
Is the magic number for binlog always the same?
Although the BINLOG magic number is always in the same position, you cannot use binary logs from one version to newer versions under certain circumstances. For example, binary logging events could change and not be understood.
How to display the contents of binlog.000003?
For example, to display the contents of the binary log file named binlog.000003, use this command: The output includes events contained in binlog.000003.
Why is MySQL relay log corrupted on slave?
Note that MASTER_LOG_POS is the Exec_Master_Log_Pos, and MASTER_LOG_FILE is the Relay_Master_Log_File ( NOT the first one, which matches relay logs that have been fetched and need to be thrown away) both from the first command. MySQL replication on slave (version 5.XX) has stopped.
How to disable binary logging in MySQL server?
The binary log file base name and any specified path are available as the log_bin_basename system variable. If you want to disable binary logging for a server start but keep the –log-bin setting intact, you can specify the –skip-log-bin or –disable-log-bin option at startup.
Why is MySQL relay log not showing up?
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.
How to check the slave’s binary log or relay log?
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. We tried restarting slave’s mysql service and start and stop the slave replication with no help. After further investigation we found that there was an issue with particularly big size of query.
What happens if I get binlog greater than binlog _ Pos?
You should never get BINLOG_SIZ greater than BINLOG_POS. If you do, binlog must be corrupt. The mysqlbinlog utility will dump a text representation of the binlog. If any of the output is garbled, then it it corrupt.