Shulou(Shulou.com)06/01 Report--

Statement level mode

Each sql that modifies the data is recorded in the bin-log of the master. When Slave is copied, the sql process parses to the same sql that was executed on the original master side and executes again.

Advantages: first of all, the advantage of statement level is to solve the shortcomings of row level, there is no need to record the changes of each row of data, reduce the number of bin-log logs, save IO, and improve performance. Because he only needs to record the details of the statement executed on the master, as well as the context information when the statement is executed.

Disadvantages: because he is a recorded execution statement, in order for these statements to be executed correctly on the slave side, he must also record some relevant information about the execution of each statement, that is, context information, to ensure that all statements can get the same results when they are executed on the slave side as in the master side. In addition, due to the rapid development of mysql and the continuous addition of many new functions, the replication of mysql has encountered a lot of challenges. The more complex content is involved in natural replication, the easier it is for bug to appear. Under statement level, there are many cases that have been found to cause problems with mysql replication, mainly when modifying data using some specific functions or functions, such as: the sleep () function cannot be copied correctly in some versions, the use of the last_insert_id () function in the stored procedure may cause inconsistent id on slave and master, and so on. Due to row level

Changes are recorded on a line-by-line basis, so there are no similar problems

Row level mode

Each row of data is recorded in the log as modified, and then the same data is modified on the slave side.

The advantage is that in row level mode, context-sensitive information about executed sql statements can be recorded in bin-log, simply by recording which record was modified and how it was modified. So the contents of the row level log will clearly record the details of each line of data modification, which is very easy to understand. And there is no problem that the calls and triggers of stored procedures, or function, and trigger can not be copied correctly in certain cases.

Disadvantages: under row level, when all executed statements are recorded in the log, they will be recorded as changes recorded in each line, which may result in a large number of log contents, such as a update statement: updateproduct set

After the execution of Owner_member_id='b' where owner_member_id='a', what is recorded in the log is not the corresponding event of this update statement (mysql records the bin-log log in the form of events), but the changes of each record updated by this statement, so that many events are recorded as many records are updated. Naturally, the volume of bin-log logs will be very large. Especially when executing statements such as after table, the amount of log generated is staggering, because MYSQL handles table structure change statements such as alter table so that every record of the entire table needs to be changed, in effect rebuilding the entire table. Then every record of the table is recorded in the log.

Mixed mode

In fact, it is a combination of the first two modes. In mixed mode, mysql distinguishes the log form of the record based on each specific sql statement executed, that is, choosing between statement and row. The new version of statement level is the same as before, recording only the statements executed. The new version of mysql Squadron row level mode has also been optimized. Not all changes will be recorded in row level. For example, when table structure changes are encountered, they will be recorded in statement mode. If the sql statement is indeed a statement that modifies data such as update or delete, xx will still record the changes of all rows.

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.