Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the format of MySQL's binlog and its advantages and disadvantages?

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article to share with you is about MySQL binlog format and advantages and disadvantages are what, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

MySQL binlog logs come in three formats: Statement,MiXED and ROW.

Statement: Every sql that modifies data is recorded in the binlog.

Pros:

binlog file is smaller

Log is the original SQL that contains user execution for statistical and auditing purposes

Early appearance, better compatibility

Disadvantages:

There are potential safety hazards, which may lead to inconsistency between master and slave

Some system functions cannot be copied accurately or cannot be copied

ROW does not record sql statement context-related information, only save which record was modified.

Pros:

Safer copy format than statement

Replication is faster in some cases (SQL complex, tables have primary keys)

Special functions of the system can also be copied

Fewer locks.

Update and delete statements check whether there is a primary key, if there is, execute directly, if there is no, see if there is a secondary index, if there is no more, scan the whole table

Disadvantages:

binlog is large (myql5.6 supports binlog_row_image)

Too many rows in a single statement update (delete) table will form a large number of binlogs

Cannot see user executing SQL from binlog (binlog_row_query_log_events added in 5.6 to record user queries)

3.Mixed: It is a mixture of the above two levels. General statement modification uses statement format to save binlog. For example, some functions, statement cannot complete the operation of master-slave replication, so binlog is saved in row format. MySQL will distinguish the log form to be treated according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode has also been optimized, not all changes will be recorded in row level, such as table structure changes will be recorded in statement mode. As for statements that modify data, such as update or delete, all changes to rows are recorded.

Pros:

Mixed use of row and statement format, for DDL record statement, row format for row operation record in table.

If innodb tables are used, transaction levels use READ_COMMITTED or READ_UMCOMMITTED log levels can only use row format.

However, DDL statements in ROW format are still recorded in statement format.

Disadvantages:

In mixed mode, binlog mode is automatically changed from SBR mode to RBR mode in the following cases.

When a DML statement updates an NDB table

When a function contains a UUID

When a table with 2 or more auto_increment fields is updated

Line any insert delayed statement

When using UDF

When RBR is required in a view, for example, the UUID() function is used to create the view

The above is MySQL binlog format and what are the advantages and disadvantages, Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 281

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report