Mysqlbinlog type of Log

Source: Internet
Author: User
Tags mixed time interval

First, Mysqlbinlog Introduction

Binlog is also called the binary log file, it will be in MySQL all modify database data query in binary form to the log file, such as: Create,insert,drop,update, etc. (for the select operation is not recorded in the Binlog , because it does not modify the database data). Binlog is generally stored in the data directory, and named: mysql-bin.*** (this can be modified in the configuration file My.cnf:log-bin=mysql-bin, is the name of the prefix; mysqld in each binlog Add a numeric extension after the name. Each time you start the server or refresh the log, the size of the file increases larger than Max_binlog_size, and a transaction is not split.

Binlog is mainly used to ensure the integrity of the data, such as master-slave backup, by reading from the Binlog file to the same operation on the salve machine, to ensure that the master-slave backup, of course, every time from the beginning of the redo, so each record has a time to cut timestamp.

Second, Binglog of type

There are three formats for Binlog, which also reflects the replication technology of MySQL: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), Mixed mode replication (mixed-based replication, MBR). Accordingly, there are three types of Binlog: statement,row,mixed.

Mysql>showvariables like ' Binlog_format ' #查看binlog的格式

Binlog with Mysqlbinlog parsing:

MIXED (STATEMENT):

# at 193 (start position)

#110708 10:03:06 (time truncation) server ID (the service ID that generated the event) 1 End_log_pos (the end of the log) 280 Query (event type) thread_id=10 exec_time=0 Error_code=0

settimestamp=1310090586/*!*/;

Insert INTO Tvalues (17)

/*!*/;

Row mode:

BEGIN

/*!*/;

# at 174

# at 214

#110708 10:49:22server ID 1 end_log_pos 214 table_map: ' Test '. ' t ' mapped to number 14

#110708 10:49:22server ID 1 end_log_pos 248 write_rows:table ID Flags:stmt_end_f

BINLOG '

mnawthmbaaaakaaaanyaaaaaaa4aaaaaaaeabhrlc3qaaxqaaqmaaq==

mnawthcbaaaaigaaapgaaaaaaa4aaaaaaaeaaf/+mgaaaa==

‘/*!*/;

# at 248

#110708 10:49:22server ID 1 end_log_pos 317 Query thread_id=1 exec_time=0 error_code=0

settimestamp=1310093362/*!*/;

COMMIT

Statement is based on the SQL statement level binlog, each SQL that modifies the data is saved to Binlog, Row is based on the line level, he will record each row of records changes, is to record each row of changes to Binlog inside, the record is very detailed, But the SQL statement does not have in the Binlog, in the replication also does not cause the Master-slave data inconsistency because the stored procedure trigger and so on, but has one fatal flaw the log volume is larger. Because you want to record data changes for each row, When executing the UPDATE statement without the WHERE condition or alter TABLE, the amount of log generated is quite large. MIXED: Is statement by default, but in some cases it switches to the row state, such as when a DML updates a NDB engine table, or a function associated with a time user. In the case of the master-slave, if the host is statement mode, then Binlog is directly write now (), however, if so, then the operation from the machine time, also executes now (), but obviously these two times will not be the same, Therefore, in this case, the statement mode must be changed to row mode, because the row mode will write the value directly instead of the write statement (the case is wrong, even if the statement mode can also use the now () function, for specific reasons later analysis). The same row mode can also reduce the calculation of the slave, such as the presence of statistical write operations in the host, the slave can be exempt from the calculation of the value directly written to the slave.

Advantages and disadvantages of the above types:

SBR The advantages of:
long history, mature skills
Binlog files are small
Binlog contains all the database modification information, which can be used to audit the security of the database, etc.
Binlog can be used for real-time restores, not just for replication
Master-slave version can be different from server version can be higher than the primary server version
SBR Disadvantages of:
Not all UPDATE statements can be duplicated, especially if they contain indeterminate operations.
Called with an indeterminate element.UdfWhen copying may also be questionable
Statements that use the following functions cannot be duplicated:
* Load_file ()
* UUID ()
* USER ()
* Found_rows ()
* Sysdate () (unless enabled at startup)–sysdate-is-nowOptions
INSERT ... SELECTwill produce more thanRBRMore row-level locks
Replication needs to be performedFull table Scan (WHEREis not applied to the index in the statement).UPDATE, you need to compare rbr  request more row-level locks
for   for "apple-converted-space" > auto_increment innodb table, Insert  statement will block other  insert  statement
for some complex statements, the consumption of resources from the server will be more serious, and  rbr  mode will only affect the records that have changed. The
Storage function (not the stored procedure  ) is also executed at the time of the call   function, which can be said to be a bad thing or a good thing
determined  udf  also need to be executed from the server
The data table must be almost consistent with the primary server, or it may cause replication error
Execute complex statement If an error occurs, it consumes more resources

RBR The advantages of:
Any situation can be replicated, which is the safest and most reliable for replication
Same replication skills as most other database systems
In most cases, replication will be much faster if you have a primary key from a table on the server.
There are fewer row locks when copying the following statements:
* INSERT ... SELECT
*ContainsAuto_incrementof the fieldINSERT
*There are no strings attached or modifications to many recordsUPDATEOrDELETEStatement
PerformInsert,update,deleteFewer locks when you make a statement
Possible to perform replication from a server with multithreading
RBR Disadvantages of:
BinlogA lot bigger.
When a complex rollbackBinlogWill contain a lot of data
Execution on the primary serverUPDATEstatement, all changed records are written to theBinlogIn, andSbrwill only be written once, which will cause frequent occurrencesBinlogof concurrent Write queries
UdfThe resulting largeBlob  value causes replication to slow
cannot   See what statements are copied (encrypted)
When executing a stacked SQL statement on a non-transactional table, it is best to use the  sbr  mode, Otherwise it is very easy to cause the data inconsistency of the master-slave server occurs
In addition, for the system library  mysql   the table changes in the following guidelines:
If you are using   Insert,update,delete  the Direct Action table, the log format is based on  binlog_format  settings and records
If you are using  grant,revoke,set password  all adopt   SBR mode recording.

MBR combines the advantages of two recording modes. Therefore, it is recommended to use MBR mode for personal comparison.

three , simple to use Binlog and view modify its type

Show binary logs; #显示binlog文件

Purge binary logsto ' mysql-bin.** ' #删除到 * * file

Bin/mysqlbinlog Binlogfile #解析binlog文件

You can view the Help file for specific use, or refer to the official usage:

Http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html#mysqlbinlog

Recover data with Binlog:

Bin/mysqlbinlog--start-datetime= ' 2011-7-7 18:0:0 '--stop-datetime= ' 2011-7-7 20:07:13 ' data/mysql-bin.000008 |mysql- U root

Viewing and modifying the type of Binlog can be done by looking at the my.cnf file, such as hybrid settings as follows:

Log-bin=mysql-bin

#binlog_format =statement
#binlog_format =row

Binlog_format=mixed

Alternatively, you can view the contents of the Binlog log file, which is explained in detail above. You can also view the SQL status by looking at:

Mysql> Show variables like '%log% ';
+-----------------------------------------+--------------------------------+
| variable_name | Value |
+-----------------------------------------+--------------------------------+
| Back_log | 50 |
| Binlog_cache_size | 32768 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | MIXED

Without restarting MySQL reload the my.cnf file, modify the method of the Mysqlbinlog type:

mysql> SET SESSION binlog_format = ' STATEMENT ';
mysql> SET SESSION binlog_format = ' ROW ';
mysql> SET SESSION binlog_format = ' MIXED ';
mysql> SET GLOBAL binlog_format = ' STATEMENT ';
mysql> SET GLOBAL binlog_format = ' ROW ';
mysql> SET GLOBAL binlog_format = ' MIXED ';

Four, Binlog format of the record

The start of each Binlog is 4 bytes: Fe 6e, and the next three bytes is the bin.

And then the next one is the content of a record it includes: Common-header, this part of the different versions of the size, more than 4.0 is 19 bytes. After this is the body.

Common-header Format: (units: bytes)

Timestamp (4)

Type (1)

SERVER_ID (4)

Total_size (4)

End_log_pos (4)

Flag (2)

Timestamp: Starting from 1970

Type: This log event type such as Format_description_event, QUERY, Load_event, and so on, where each binlog's first record is of type format_description_event, It records information about the Binlog, such as the version, which is useful for post-order analysis of Binlog Records, so the first record must first be read by the tool for the task to read Binlog content. Query includes our regular operations such as: Create,drop,update,insert and so on.

SERVER_ID: Creates the server ID for this event. Prevents loops from being written by master-slave. The master ' s server ID (is preserved in therelay log; used to prevent from infinite loops in circular replication).

Total_size: The size of the record, including Common_header and body.

End_log_pos: The start position of this next record. is also the previous byte at the end of this record.

Flag: The Mark bit.

Record of query type:

A record of type query in addition to the beginning of the Common-header, at the beginning of the body is a post-header, and then the real body content.

Query Post-header: (per byte)

THREAD_ID (4)

Exec_time (4)

Db_len (1)

Error_code (2)

Status_var_len (2)

Thread_id:is used to distinguish temporary tables, belong to Differentclients.

Exec_time:the time from Whenthe query started to when it is logged in the Binlog, in seconds. Query to reach the time interval generated by this binlog event.

Db_len: The name length of the current database.

Error_code: Error number to execute error.

Mysqlbinlog type of Log

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.