In MySQL high-availability architectures, primary database replication is a very common type.
When the primary database goes down, you can upgrade a slave database as a new primary database to ensure service availability. At the same time, the QPS of the entire cluster can be improved by extending the slave library.
Under the master-slave replication architecture, MySQL uses binlog to achieve master-slave data consistency.
As shown in the figure above, MySQL master-slave replication mainly has the following steps
1. master logs the changes to the binary log
2. The slave io_thread request the binlog of the main library and write the resulting binlog log to the relay log
3. Slave sql_thread redo events in the relay log
In addition to being a link for MySQL master-slave replication, binlog also serves other purposes. Like what:
1. Use the mysqlbinlog tool to parse the binlog file to perform point-in-time database recovery.
2. Flashback of the database based on binlog events (MariaDB can directly use mysqlbinlog for flashback)
3. Github's open-source online table change tool gh-ost is also implemented through binlog
4. You can also incrementally subscribe & consume by parsing binlogs
binlog is so useful, but it is inevitable that you will encounter some problems in the daily operation and maintenance process. Here are some binlog-related errors.
One of the frequently asked questions
phenomenon
mysqlbinlog5.5 parses the mysql5.7 binlog file appears
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 31, event_type: 35ERROR: Could not read entry at offset 123: Error in log format or read error.
Cause analysis
MySQL 5.6 and other higher versions of binlog files have added new binlog events, such as GTID events.
mysqlbinlog in mysql5.5 does not recognize such binlog events.
Workaround
Use the higher version of mysqlbinlog to resolve the binlog generated by the lower version of mysql
Frequently asked question two
phenomenon
A healthy mysql server show slave status appears
Last_SQL_Error: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.
Cause analysis
The entries in the relay log cannot be read due to binlog errors in the master library, relay log errors in the slave library, or network problems and bugs. It is usually caused by a network failure or excessive pressure on the slave library, resulting in an incorrect relay-log format.
Workaround
After finding the current synchronization time point and resetting the master-slave synchronization, a new relay log will be generated and the master-slave synchronization will be restored.
From the output of "show slave status\G", find the following information:
Relay_Master_Log_File: mysql-bin.002540 // The binlogExec_Master_Log_Pos of master read by the slave library: 950583017 // The position position point that has been executed on the slave
Stop the slave and set the synchronization again starting from the binlog file that the slave has read and the position that has been executed.
Relay_Master_Log_File: mysql-bin.002540 // The binlogExec_Master_Log_Pos of master read by the slave library: 950583017 // The position position point that has been executed on the slave
Frequently asked question three
phenomenon
Restore show slave status after downtime error:
Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL
Cause analysis
Downtime, such as power failure, motherboard burning, etc., or illegal shutdown, resulting in corruption of the relay-bin file
Workaround
Same question two.
relay_log_recovery = 1 can also be set.
When the slave goes down from the library, if the relay-log is corrupted and part of the relay log is not processed, the relay log is automatically abandoned, and the log is re-retrieved from the master, completing the recovery of the relay log.
Frequently asked question four
phenomenon
Appears when changing master to after a reboot from the library machine goes down
Error (Code 1201): Could not initialize master info structure; more error messages can be found in the MySQL error log
or
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
Cause analysis
Downtime, such as power failure, motherboard burning, etc., or illegal shutdown, causing damage to master.info or realy-log.info files
Workaround
slave> reset slave all, change master to
Preventive measures
Profile settings
relay_log_info_repository=table
master_info_repository=table
The storage engine of MySQL 5.6.5 mysql.slave_master_info and mysql.slave_relay_log_info is set to MyISAM by default, and you need to change it to the storage engine of InnoDB
ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
mysql.slave_master_info table will be updated after sync_master_info events.
mysql.slave_relay_log_info table will be updated with each transaction commit.
Frequently asked question 5
phenomenon
The master slave binlog_format originally a statement, after changing the main database binlog_format to row, show slave status appears from the library:
Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
Cause analysis
When the main database binlog_format is row, and the slave library binlog_format is statement, the above error will appear.
But the main library binlog_format is statement, and the slave library binlog_format row;
Or if the main database binlog_format is row, the error will not be reported if the database binlog_format is mixed.
If the your SQL thread is indeed configured with
binlog_format=STATEMENT once it receives a ROW event it will stop. The
reason is that it would be unable to log that ROW event in STATEMENTformat (sometimes we refer to this as ROW injection, which is either a
BINLOG statement or a ROW event executed by the slave's SQL thread)
Detailed reason reference:https://bugs.mysql.com/bug.php?id=69095
Workaround
SLAVE> STOP SLAVE;
SLAVE> SET GLOBAL binlog_format=MIXED;
SLAVE> START SLAVE;
Frequently asked question number 6
phenomenon
Error when syncing mysql5.6 to mysql5.5
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.000001' at 4, the last event read from 'mysql-bin.000001' at 120, the last byte read from 'mysql-bin.000001' at 120.'
Cause analysis
In order to solve the problem that the SQL statements running on the primary server are inconsistent with the SQL statements running on the server (called event corrupt) due to software and hardware or network transmission errors, MySQL 5.6 adds the Replication Event Checksum function. When an event is written to the binary log, checksum is also written to the binary log, and then after the event is transmitted to the slave through the network, it is verified on the slave and written to the slave's relay log. Since events and checksums are logged at each step, we can quickly figure out what the problem is.
In mysql5.6.5 and later versions binlog_checksum the default value is crc32,
The default value binlog_checksum previous version was none
solution
Slave> set global binlog_checksum=none
Frequently asked question 7
phenomenon
After the disk is full, manually clean up the binlog file and the mysql-bin.index file
show binary logs are empty, but show master status is normal.
mysql> show binary logs; Empty set (0.00 sec)mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.001385 | 987114584 | | |
+------------------+-----------+--------------+------------------+
Cause analysis
After checking the mysql-bin.index file, I found the first blank line.
In the mysql source code rpl_master.cc:show_binlogs() you will find the following code:
/* The file ends with EOF or empty line */
while ((length=my_b_gets(index_file, fname, sizeof(fname))) > 1)
Blank lines are considered the end of the document
(Reference.)https://yq.aliyun.com/articles/213657Article)
Preventive measures
Don't manually delete binlog, don't manually edit the mysql-bin.index file, unless you know what you're doing, otherwise you may be laying mines for yourself!
summary
DBAs need to pay attention to the improvements to binlog in each new version of MySQL (such as the gtid feature added in version 5.6, Enhanced Multi-threaded Slaves in version 5.7), and understand the meaning of each parameter in detail, so that they can know what they mean when they encounter errors and solve problems easily.
|
Posted on 9/25/2018 10:31:40 AM
|
|
|
|
