In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.