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

This article mainly shows you the "sample analysis of different binlog modes of mysql", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of different binlog modes of mysql".

As we all know, mysql binlog has three formats:

One: Statement: every sql statement that modifies the data is recorded in binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance. (how much performance and log volume can be saved compared with row, which depends on the SQL of the application. The log amount generated by modifying or inserting the row format of the same record is still less than that generated by Statement, but considering that if the conditional update operation, as well as the whole table deletion, alter table and other operations, ROW format will generate a large number of logs, so when considering whether to use ROW format logs should follow the actual situation of the application. How much more logs will be generated, and the resulting IO performance problems.)

Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in slave as they are executed on the masterside. In addition, the replication of mysql, like some specific functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems).

Statements that use the following functions cannot be copied either:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the-- sysdate-is-now option is enabled at startup)

At the same time, INSERT. SELECT produces more row-level locks than RBR.

Second: Row: do not record the context-related information of sql statements, only save which record is modified.

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of rowlevel will clearly record the details of each line of data modification. And there will be no problems that stored procedures, or function, and the calls and triggers of trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

Third, Mixed mode: it is a mixed use of the above two kinds of level. The general statement modification uses statment format to save binlog, and for operations that cannot be copied in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the executed SQL statement.

For example, some functions, statement can not complete the master-slave copy operation, then saving binlog,MySQL in row format will distinguish the log form of records according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode is also optimized. Not all changes are recorded in row level, such as statement mode in the event of table structure changes. Statements that modify data, such as update or delete, still record changes to all rows.

Binlog log format selection:

Mysql uses Statement log format by default, and MIXED is recommended.

Due to some special uses, you can consider using ROWED, such as synchronizing data changes through binlog logs, which will save a lot of related operations. Binlog data processing will be very easy, and parsing will be easy compared to mixed (of course, provided that the IO overhead caused by the increased log volume is tolerated).

There are three ways to generate master-slave replication for the three formats of binlog:

(1): Statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.

(2): row-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

(3): mixed type (mixed) replication: Statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

If binlog uses Mixed mode, the binlog mode will automatically be changed from statement mode to row mode in the following cases (extracted from the network)

1) when the DML statement updates a table of the NDB storage engine

2) when the function contains UUID ()

3) when 2 or more fields are updated, and the table containing the AUTO_INCREMENT field is updated

4) when executing the INSERT DELAYED statement

5) when using UDF (user defined function)

6) when row must be used in the view, for example, the UUID () function is used when creating the view

Experiment 1 binlog adopts Statement mode, which leads to the difference between the master and the slave.

192.168.0.144:

MariaDB [log] > show variables like 'binlog_format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | MIXED |

+-+ +

1 row in set (0.00 sec)

MariaDB [log] > select count (*) from pvlogs where lastmodify > '2017-07-19' and lastmodify insert into pvlogs2 select * from pvlogs where lastmodify >' 2017-07-19' and lastmodify select count (*) from pvlogs2

+-+

| | count (*) |

+-+

| | 1462803 |

+-+

1 row in set (0.00 sec)

192.168.0.143:

MariaDB [log] > select count (*) from pvlogs where lastmodify > '2017-07-19' and lastmodify select count (*) from pvlogs2

+-+

| | count (*) |

+-+

| | 111848 |

+-+

1 row in set (0.00 sec)

Experiment 1 shows that when the master library binlog adopts MIXED mode, Statement mode is used by default, because the Statement mode only records the executed sql into binlog and then passes it to the slave database. However, due to the difference between the master and slave pvlogs tables on both sides, the data of the pvlogs2 table is different.

Experiment 2: because the uuid () function is involved, the mixed mode is automatically converted to row mode to copy accurately.

192.168.0.144:

MariaDB [log] > show variables like 'binlog_format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | MIXED |

+-+ +

1 row in set (0.00 sec)

MariaDB [log] > insert into test_log select uuid () id, member_id,jsession,ip,search_id,info_id,lastmodify,disc,status from pvlogs where lastmodify > '2017-07-19' and lastmodify select count (*) from pvlogs2 where lastmodify >' 2017-07-19' and lastmodify select count (*) from test_log

+-+

| | count (*) |

+-+

| | 1462803 |

+-+

1 row in set (1.29 sec)

192.168.0.143:

MariaDB [log] > select count (*) from pvlogs where lastmodify > '2017-07-19' and lastmodify select count (*) from test_log

+-+

| | count (*) |

+-+

| | 1462803 |

+-+

1 row in set (1.29 sec)

Experiment 2 shows that when the master library binlog adopts the MIXED mode, although the Statement mode is used by default, when the uuid () function is encountered, because the Statement mode cannot be accurately copied, it will be automatically converted to use the row mode, so that the specific changes are written into the binlog and passed to the slave database to achieve accurate replication.

Experiment 3: self-increment causes mixed to be converted to rowed

192.168.0.144:

MariaDB [log] > show variables like 'binlog_format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | MIXED |

+-+ +

1 row in set (0.00 sec)

MariaDB [log] > select count (*) from pvlogs_back where lastmodify > '2017-08-20' and lastmodify insert into test_log select', member_id,jsession,ip,search_id,info_id,lastmodify,disc,status from pvlogs_back where lastmodify > '2017-08-20' and lastmodify select count (*) from test_log

+-+

| | count (*) |

+-+

| | 2638555 |

+-+

1 row in set (0.93sec)

192.168.0.143:

MariaDB [log] > select count (*) from pvlogs_back where lastmodify > '2017-08-20' and lastmodify select count (*) from test_log

+-+

| | count (*) |

+-+

| | 2638555 |

+-+

1 row in set (0.93sec)

Experiment 3 shows that when the master library binlog adopts MIXED mode, although Statement mode is used by default, when the table with two or more fields and containing AUTO_INCREMENT fields is updated, because the Statement schema can not be accurately copied, it will be automatically converted to use row mode, so the specific changes will be written into binlog and passed to the slave database to achieve accurate replication.

Summary: the three binlog modes of mysql have their own advantages and disadvantages. A large amount of log content may be generated under row, but it can be copied in any case, which is the most secure and reliable for replication, and there are fewer locks when executing INSERT,UPDATE,DELETE statements. In most cases, the binlog generated in Statement mode is relatively small, but in some cases, it is recommended to use MIXED mode.

The above is all the content of the article "sample Analysis of different binlog modes of mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.