MySQL binary log Format deep understanding

The MySQL two log is used to record changes to the database, which is structured to discuss the format of the log.

Each log contains 4 bytes of magic number and event description package

Log has the first four bytes is Magic number:oxfe ox62 0x69 0x6e = 0xFE ' B "i" N ' is turned into an integer: 1852400382 use is to read 4 byte contrast is not this number, the description is not binary log, you do not have to deal with.

Log_event.sh can be found in the

/* 4 bytes which all binlogs should the begin with/*
#define BINLOG_MAGIC "\xfe\x62\x69\x6e"

The header of each event is probably as follows:

-Each event contains: event type, what time, by which version of MySQL generated
-The size of the event and some change information can be found from the header header

The first event is called: Format Descriptor Event (Event description structure: FDE) to describe the format of the log

Other event is dependent on the description of the structure of different, with different structures to record data

The last event is the log switch event (Log-rotation event) used to guide the file name of a log

The structure of each event is probably as follows:

+===================+
|event Header |
+===================+
|event Data |
+===================+

Now most of the V4 structures are used from MySQL 5.0, specifically as follows:

+=====================================+
|event |timestamp 0:4 |
|header+––––––––––––––+
| |type_code 4:1 |
| +––––––––––––––+
| |server_id 5:4 |
| +––––––––––––––+
| |event_length 9:4 |
| +––––––––––––––+
| |next_position 13:4 |
| +––––––––––––––+
| |flags 17:2 |
| +––––––––––––––+
| |extra_headers 19:x-19|
+=====================================+
|event |fixed Part X:y |
|data +––––––––––––––+
| |variable Part |
+=====================================+
The first event is that the FDE structure has no extra_headers part, so it is fixed to 19 bytes.

The fixed-length portion of the Fde's Event_data is:

2-byte version of the log format, from MySQL 5.0 after all 4

50 bytes used to record MySQL version number such as: 5.6.16-64.2-rel64.2-log Not enough 50 bytes to fill with 0x00

4 bytes of time generated by the log

1-byte header length. Typically 19, if greater than 19, the following event has a Extra_header field
For FDE variable-length sections are generally empty

Other event calculations

Header length = x byte

Data length = (event_lenth-x) byte

Length of fixed length part in data area

Fixed_part=ybyte
Variable_part= (event_length-(x+y)) byte

Add: Binary operation

Open MySQL binary log:
Edit My.cnf, add
Log-bin=/var/log/mysql/mysql-bin.log
When the log is turned on it requires myssqladmin flush logs to take effect.
Note that log-bin specifies that the extension is invalid, and when MySQL creates a binary log file, it first creates a file with "Mysql_log_bin" as the suffix of ". Index", and creates a "Mysql_log_bin" name. 000001 "for the suffix of the file. When the MySQL service restarts a file that has a suffix of ". 000001", it adds one, and the suffix is incremented by 1, and a new log file is created if the log length exceeds the upper limit of the Max_binlog_size (default is 1G); Use flush Logs (MySQL command) or executing mysqladmin–u–p flush-logs (Windows command Prompt) also creates a new log file.
View:
Because the logs are stored in binary form and cannot be read directly, you need to use the Mysqlbinlog tool from MySQL to view
Mysqlbinlog mysql-bin.000002-d Test
Mysqlbinlog There are some options available, a simple description of common options:
-D,--database=name: Specifies the database name, listing only the operations of the specified database.
-D,--disable-log-bin: Disables binary logging when performing a restore. Can prevent the same MySQL plus-t into the dead loop
-O,--offset=n: n-line command ignored before log off
-R,--result-file=name: output log to specified file
-R,--read-from-remote-server: Read binary from a MySQL server
-S,--short-form: Show simple format, omit some information
-S,--socket=name:socket file connection path.
-T,--to-last-log: Used with-R, will not stop at the end of the binary log, but at the end of the MySQL server-generated binlog, if output and input are in a MySQL can cause a dead loop.
--set-charset=char-name: In the output text format, add set names Char-name in the first line.
--start-datetime=#--stop-datetime=#: Log that specifies the starting date of the output.
--start-position=#--stop-position=#: Specifies the location of the start log.
Clean:
Delete all binary logs:
Reset Master
To delete a portion of a log:
PURGE Master LOGS to & PURGE master LOGS before
PURGE MASTER LOGS to ' mysql-bin.****** ' is to delete all logs prior to ' the ' Hu ' number
PURGE MASTER LOGS before ' yyyy-mm-dd hh:mm:ss ' command to delete all logs before the ' Yyyy-mm-dd Hh:mm:ss ' time
To set the log expiration time:
Modify MY.CNF
Expire_log_day=5
Here set the Save 5 days log, more than 5 days of log will be automatically deleted
Recovery:
Full recovery:
Mysqlbinlog mysql-bin.00001|mysql-uroot-p
Point-in-time-based recovery:
If you mistakenly delete a table, the use of full recovery is useless, because the log also retains the deleted SQL statements, so we need to revert to the state before the misoperation, and then skip the wrong operation of the statement.
If I accidentally deleted a table at 20:00, you can use the following statement to restore:
Mysqlbinlog--stop-date= ' 2012-06-05 19:59:59 '/var/log/mysql-bin.000001 | Mysql-uroot-p
Skip the Point-in-time of the accidental deletion and then execute:
Mysqlbinlog--start-date= ' 2012-06-05 20:01:00 '/var/log/mysql-bin.000001 | Mysql-uroot-p
Based on location-point recovery:
More accurate data can be obtained based on the restoration of location points.
Binlog
As shown above, the drop table test starts at 889107 and terminates at 889189, so we can use the following statement to recover:
Mysqlbinlog--stop-position= ' 889107 '/var/lib/mysql/mysql-bin.000001|mysql-uroot-p
Mysqlbinlog--start-position= ' 889189 '/var/lib/mysql/mysql-bin.000001|mysql-uroot-p
Sometimes it may be because of the system version of the problem, the above method does not work, you can export the binary to a SQL file, and then directly based on the SQL statement recovery
Mysqlbinlog mysqlbinlog.000001 >log.sql