MySQL writes the statement into the binlog binary log, mysqlbinlog

MySQL writes the statement into the binlog binary log, mysqlbinlog

Because binary logs are public resources, binary logs must be written to all threads, so you must avoid two threads updating binary logs at the same time. Therefore, when writing a binary log to an event group, the binary log will obtain a mutex LOCK_log and be released after the event group is written. Because all sessions on the server write transactions to the binary log, so this lock often blocks some session threads.

1. Write DML statements

Usually refers to the DELETE/INSERT/UPDATE statement. To ensure consistency, MySQL obtains the transaction-Level Lock while writing binary logs, and releases the lock after writing.

Before locking and releasing a table, you must write the statement into the binary log before submitting the statement. This ensures that the binary log is consistent with the changes made to the table in the statement, if the record log is not part of the statement, other statements may be inserted in the middle of the statement's modification to the database and the recording of binary actions.

2. Write DDL statements

It usually refers to the create table/alter table statement. DDL statements CREATE or change objects in the file system. To protect the internal data structure (. frm) in the table from being updated, you must first obtain an internal lock LOCK_open when modifying the table definition.

3 Write Query

For the SBR, the most common binlog event is the QUERY event. It writes the statements executed on the master node to the log, except for the actually executed statements, this statement also contains additional information required for executing the statement.

 

When executing statements on the server, you must understand the implicit, implicit, including

1) Current Database

Add a special field to the QUERY event to record the current database. This field is also available for events that process the load data infile statement.

2) Current Time

Five functions need to use the time now curdate curtime UNIX_TIMESTAMP SYSDATE, the first four return start time, And SYSDATE returns the time when the function is executed.

[root@localhost][(none)]> SELECT SYSDATE(),NOW(),SLEEP(2),SYSDATE(),NOW();+---------------------+---------------------+----------+---------------------+---------------------+| SYSDATE()           | NOW()               | SLEEP(2) | SYSDATE()           | NOW()               |+---------------------+---------------------+----------+---------------------+---------------------+| 2017-03-29 08:08:37 | 2017-03-29 08:08:37 |        0 | 2017-03-29 08:08:39 | 2017-03-29 08:08:37 |+---------------------+---------------------+----------+---------------------+---------------------+1 row in set (2.05 sec)

 

In order to correctly process the time, the event stores a timestamp, indicating when the event starts to be executed. Then copy the timestamp value from the event to the SLAVE execution process. When calculating the time function, use the timestamp as the time when the event starts to be executed.

SYSDATE directly obtains the time from the operating system. This is not safe for the SBR, and may lead to different return values executed on the master and slave. Use this function with caution.

3) Context

Some statements also contain Implicit Information and must satisfy some conditions.

A) The statement contains references to User-Defined variables.

B) contains RAND function calls

C) call with LAST_INSERT_ID

D) insert fields of the AUTO_INCREMENT type to the table.

In either case, one or more context events need to be written to the binary log before the QUERY time is included. Because a QUERY event may have multiple context times before, therefore, binary logs must process Multiple User-Defined variables and RAND functions at the same time. Binary logs store necessary context information through the following events

User_var records the variable name and value of a single user-defined variable

Rand records the random seed of the RAND function. The seed is taken from the session temporal state.

Intvar: if the statement is to insert a field of the AUTO_INCREMENT type, when the insert statement is executed, this event sets the value of the automatic incremental calculator in the table. If the statement contains the call of the LAST_INSERT_ID function, this event records the return value of this function in this statement.

[root@localhost][(none)]> set @foo=12;Query OK, 0 rows affected (0.00 sec)[root@localhost][(none)]> set @bar = 'fdjkfjdjfkdjfkdf';Query OK, 0 rows affected (0.00 sec)[root@localhost][(none)]> use bossDatabase changed[root@localhost][boss]> create table t1(b int auto_increment primary key,c varchar(20));Query OK, 0 rows affected (0.43 sec)[root@localhost][boss]> insert into t1(b,c) values(@foo,@bar),(RAND(),'random');Query OK, 2 rows affected (0.11 sec)Records: 2  Duplicates: 0  Warnings: 0[root@localhost][boss]> insert into t1(b) values(LAST_INSERT_ID());ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'[root@localhost][boss]> insert into t1(b) values(LAST_INSERT_ID()+1);Query OK, 1 row affected (0.07 sec)[root@localhost][boss]> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql_bin.000023 |     2878 |              |                  | a0c06ec7-fef0-11e6-9f85-525400a7d662:1-193 |+------------------+----------+--------------+------------------+--------------------------------------------+1 row in set (0.00 sec)[root@localhost][boss]>  show binlog events in 'mysql_bin.000023'\G;