[Mysql] about the binlog format, mysqlbinlog

[Mysql] about the binlog format, mysqlbinlog
Preface

1. mixed is recommended. statement is used by default. It is based on the context set session/global binlog_format = mixed;

2. On a binary day, records the operations performed by the database for changes, such as Insert, Update, and Delete. Operations that do not affect database records, such as Select

3. MySQL logs can be recorded in three modes: STATEMENT, ROW, and MIXED.

4. Main binary functions: Replication and Recovery)

5. The difference between ROW and STATEMENT lies in two aspects: server load/consistency.

Copy history

The mysql-3.2 began to support command-based replication, namely statement-based replication.
The mysql-5.1 begins to support row-based replication and hybrid replication, that is, row-based replication and mixed-based relication.
The mysql-5.5 began to support semi-synchronous replication, also known as semi-synchronous replication, to maintain master-slave consistency in the transaction environment.
The mysql-5.6 started to support parallel replication, but its parallelism was only schema-based
The mysql-5.7 begins to support group-based committed parallel replication

 

The basic process of replication is as follows:

(1) the IO process on the Slave connects to the Master and requests the log content after the specified location of the specified log file (or the log from the beginning ).
(2) After the Master receives a request from the Slave IO process, it reads the log information after the specified location of the log based on the request information by the IO process responsible for replication, the IO process returned to Slave. Besides the information contained in the log, the returned information also includes the name of the bin-log file on the Master end and the location of the bin-log.
(3) After the Slave IO process receives the information, it adds the received log content to the end of the relay-log file on the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file.
(4) After the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log content into the executable content during actual execution on the Master end, and execute

Redolog and undolog

Undolog implements atomicity. Each time before data is operated, the data is first backed up to a place (the place where data backup is stored is called undolog ). Then modify the data. If an error occurs or the user executes the rollback statement, the undolog backup can be used to restore the data to the status before the transaction starts.

Redolog implements persistence. When the data is changed before the operation, the previous operations are written to the redo log. In this way, when the power is down or some accidents cause subsequent tasks to fail, the system can continue to complete these changes to restore data. For example, when the database is down at a certain time, there are two transactions, one transaction has been committed, and the other transaction is processing the database restart, it must be rolled back and rolled back according to the log, write the changes to committed transactions to the data file, and the changes to uncommitted transactions are restored to the status before the start of the transaction.

Row based replication-RBR

The log records each row of data in the modified form, and then plays back the same data on the slave end. This is the same as the replication skills of most other database systems.

Advantages: Bin-log does not record the context-related information of the executed SQL statement. You only need to record the record that has been modified and changed to what kind of information. Therefore, the row-level log content will clearly record the details of each row's data modification.

1. replication can be performed in any situation. This is the safest and most reliable for replication, and there will be no stored procedures or functions in certain circumstances, and trigger calls and triggers cannot be correctly replicated. 2. In most cases, if a table on the slave server has a primary key, the replication will be much faster. Execute INSERT, UPDATE, DELETE statements have fewer locks and fewer row locks when copying the following statements

• INSERT... SELECT
• INSERT containing the AUTO_INCREMENT Field
• There are no conditions attached or the UPDATE or DELETE statements for many records have not been modified

Disadvantages: When all executed statements are recorded in the log, they are recorded with modifications recorded in each line. This may produce a large amount of log Content.

For example, there is an update statement: update product set owner_member_id = 100 where owner_member_id = 1. After execution, what is recorded in the log is not the event corresponding to this update Statement (MySQL records the bin-log as an event), but the change of each record updated by this statement, in this way, many events are recorded and updated.

When executing statements such as alter table, the log volume is astonishing. Because MySQL treats alter table and other table structure change statements in a way that each record of the entire table needs to be changed. In fact, the whole table is rebuilt. Each record of the table is recorded in the log.

• Binlog is much larger, and complex rollback binlog contains a large amount of data.
• When an UPDATE statement is executed, all changed records are written to binlog, which causes frequent concurrent writes to binlog.
• The large BLOB value generated by the UDF causes the replication to slow down.

Note: after using the RBR mode, it can handle many of the original primary key duplication problems Statement based replication-SBR

Each SQL statement that modifies the data is recorded in the bin-log of the master database. It also records the information related to each statement during execution, that is, the context information.

Advantages: You do not need to record changes in each row of data. This reduces the bin-log volume, saves IO, and improves performance. The Master/Slave versions can be different. The slave server version can be higher than the master server version.

Disadvantages: Because the execution statement of the record, in order to make these statements run correctly on the slave end, it must also record some information about each statement during execution, that is, the context information.

In addition, many new features are constantly added, which makes MySQL replication a great challenge. Currently, many cases have been found to cause MySQL replication problems, mainly because some specific functions or functions are used for data modification, such as sleep () in some versions, functions cannot be actually copied. The last_insert_id () function is used in the stored procedure, which may result in inconsistent IDs of slave and master.

1. Not all UPDATE statements can be copied, especially when there are uncertain operations.

2. Replication may also cause problems when calling udfs with uncertainties. The identified udfs must also be executed on the slave server.

3. Statements using the following functions cannot be copied: LOAD_FILE (), UUID (), USER (), FOUND_ROWS (), SYSDATE () (unless the -- sysdate-is-now option is enabled at startup)

4. INSERT... SELECT will generate more row-level locks than RBR. When performing a full table scan (The WHERE statement is not applied to the index) UPDATE, you need more row-level locks than RBR requests.

6. For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements.

7. For some complex statements, resource consumption on the slave server will be more serious, while in RBR mode, only the changed record will be affected.

8. When a stored function (not a Stored Procedure) is called, it also executes the NOW () function. This can be said to be a bad thing or a good thing.

10. Data Tables must be basically the same as those on the master server. Otherwise, a replication error may occur.

11. Only the repeatable read and the preceding isolation levels are supported.

Mixed based replication-MBR

TRIGGER. There are master nodes and slave nodes. replication is normal and data is normal. STATEMENT/MIXED is available on the primary node. It does not exist on the top of the primary node. The replication is normal, the data is abnormal, and the SQL STATEMENT in the trigger is not executed. There are master nodes and slave nodes. replication is normal and data is normal. FUNCTION. The log records the UDF converted results. There are master nodes and slave nodes. replication is normal and data is normal. STATEMENT/MIXED is available on the master node. If the master node does not exist, an error is returned during replication. UDF is not recognized. There are master nodes and slave nodes. replication is normal and data is normal. Stored procedures (stored procedures) are STORED on the primary node of the ROW. The data is copied normally from the primary node. Instead of calling sp, the SQL statement in SP is recorded. There are master nodes and slave nodes. replication is normal and data is normal. STATEMENT/MIXED is available on the master node. If the master node does not exist, the replication is normal and the data is normal. Instead of calling sp, the SQL statement in SP is recorded. There are master nodes and slave nodes. replication is normal and data is normal. Event: There is a ROW on the primary node. There is no ROW on the top. The replication is normal and the data is normal. The SQL statement in the EVENT is recorded instead of the plan. There are master nodes and slave nodes. replication is normal and data is normal. (Default, disable on slave), alter event event_name ENABLE/DISABLESTATEMENT/mixed on the master, from top to bottom, replication is normal, data is normal. The SQL statement in the EVENT is recorded instead of the plan. There are master nodes and slave nodes. replication is normal and data is normal. (Default, disable on slave), alter event event_name ENABLE/DISABLE

Starting from version 5.1.8, MySQL provides the third replication mode except Statement Level and Row Level: Mixed, which is actually the combination of the first two modes. In Mixed mode, MySQL will differentiate the log format of the Record Based on each specific SQL Statement executed, that is, select one between Statement and Row. The Statment level in the new version is the same as before, and only the statements executed are recorded. The row level mode of the new MySQL squadron has also been optimized. Not all modifications are recorded by row level. For example, when the table structure is changed, it will be recorded in statement mode, if the SQL statement is indeed a statement that modifies data such as update or delete, changes to all rows will be recorded.

In the following situations, the binlog mode is automatically changed from the SBR mode to the RBR mode.

• When the DML statement updates An NDB table
• When the function contains uncertain functions such as uuid (), user (), current_user (), found_rows (), row_count (),
• When two or more tables with AUTO_INCREMENT fields are updated
• When any insert delayed statement is row
• When a user-defined function (UDF) is used
• When RBR must be used in a view, for example, the UUID () function is used to create a view.
• Temporary table used (temporary table)

The new row-based mode uses binlog-row-image = minimal to set this option. Only the changed fields in the DML operation are saved to the binlog, (insert, delete, or full fields ). This improves the replication throughput of the master and slave, reduces the disk usage, network resources, and memory usage of the binlog. In addition, the processing rules for table changes in the system database mysql are as follows: 1. If INSERT, UPDATE, and DELETE are used to operate the table directly, the log format is recorded according to the binlog_format setting. 2. If you use the GRANT, REVOKE, set password, and other management statements, in any case, we adopt the SBR mode record. 3. The blockhole engine does not support the row format, and the ndb engine does not support the statement format.

References

Https://dev.mysql.com/doc/refman/5.7/en/binary-log.htmlhttps://dev.mysql.com/doc/refman/5.7/en/binary-log-formats.htmlhttps://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/https://www.percona.com/blog/2010/07/20/estimating-replication-capacity/http://my.oschina.net/zijian1315/blog/202599http://www.360doc.com/content/14/1107/14/12904276_423333021.shtmlhttps://www.percona.com/blog/2011/12/16/statement-based-replication-with-stored-functions-triggers-and-events/http://www.tuicool.com/articles/vuUbyy