The principle of high-availability MySQL MHA

* See Https://code.google.com/p/mysql-master-ha/wiki/HowMHAWorks

-the founder's PPT document describes the principles of design http://www.slideshare.net/matsunobu/automated-master-failover Although typography is not particularly appealing, it does cover internal design notes, But it may not be up to date. -source See Https://github.com/yoshinorim/mha4mysql-manager/tree/master/lib/MHAperl write Operation Binlog interface https://github.com/ Yoshinorim/mha4mysql-node/tree/master/lib/mhahttps://github.com/ovaistariq/mha-helperpython Packaged Tool scripts, The command interface used to invoke Perl writes.    * How to identify the offset from which to start the relay log compensation?!    A long time ago, the author of MHA used Mysqlbinlog to identify the offset of the relay log, but now it is not necessary to parse the binary file's event header on its own, to discern the starting offset of the event. There are several reasons why Mysqlbinlog is not applicable, mostly because mysqlbinlog always prints all the contents of all events, not just the head of the event, but what we just need is to analyze the head. Because the necessary positioning information is in the header, it includes the event type, Master ID, event length, and the offset of the next event.    Event Body We do not care, then, the use of Mysqlbinlog not only to read the binary log when the performance cost increased, but also we need to be particularly wary of some of the logs with the keyword "hit shirt" SQL statements, such as end_log_pos\# at and so on. To avoid erroneous identification of the starting offset information, the Mysqlbinlog plus--base64-output=always option is helpful, but this option is only supported in MySQL 5.1 and MySQL 5.5, 5.0 is not supported, and 5.6 is removed.    And this option might miss some events. The above does not work, can only reverse the search, the Rotate event is a good landmark. * Fast Trunk Log positioning assumes that the target to be recovered from the Master_log_file:pos in the library is mysqld-bin.000001:504810023, the latest read header from the IO thread in the library mysqld-bin.000001 : 504810689, and the latest trunk log file is mysqld-relay-bin.000001 and exceeds 500M. Target fromThe library and the latest IO thread read header from the library only differ by hundreds of bytes. If MHA starts parsing from the log header, the failover time is too long. $latest _MLF = master_log_file on the latest SLAVE$TARGET_MLF = Master_log_file on the recovery target SLAVE$LATEST_RMLP = Read_master_log_pos on the latest slave $target _RMLP = read_master_log_pos on the recovery target slave$offset = $latest _r mlp-$target _rmlp$filesize = File size of the latest relay log File on the latest slaveif ($latest _MLF eq $target _mlf) &A mp;& ($filesize > $offset), then MHA can decide that the starting recovery point is up-to-date from the latest relay file in the library ($filesize-$offset) offset, * How to generate Relaylog compensation and binlog compensation from a library for a target to be recovered in general, the compensation is done in three sections:-Target from the exec_master_log_pos of the library to the target from the library Read_master_log_ POS because the events to be played back in the trunk log must consist of a complete transaction, all content between Begin and commit, if the main library is down from the library without receiving the full transaction event, then Exec_master_log_pos < Read_master_log_ POS, they are just part of the transaction, and do not get replay execution. -the target from the library's Read_master_log_pos to the latest from the library's read_master_log_pos target from the library's trunk log may lag behind the latest from the library. -the latest from the Read_master_log_pos of the library to the end of Binlog of the crash Main Library, the author of MHA is Mysqlbinlog output for each segment, which brings two questions:- Rollback statements are added at the beginning of each Binlog file and at the end of each mysqlbinlog output. An explicit filter is required. -If the row-based event is just fragmented into two files, then the output of the Mysqlbinlog may not beIndeed Now, instead of three paragraphs are directly parse the binary file of the event header to locate and search, then the three segment into a large binary file, and then to the Mysqlbinlog to output, only output once, convenient filtering rollback, there is no problem of splitting row event.

　　

The principle of high-availability MySQL MHA