Three modes of MYSQ Binlog

MYSQ Binlog three modes and settings method 1.1 row level row mode log records the form of each row of data being modified, then modifies the same data on the slave side Advantages:In row level mode, Bin-log can not record the context-sensitive information of the executed SQL statement, only the one that is required to be recorded is modified. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures or function in certain situations, and trigger calls and triggers cannot be copied correctly

Disadvantages:Row level, where all executed statements are logged in the log, are recorded with the modification of each row, resulting in a large amount of log content. 1.2 Statement level (default) each SQL that modifies the data is recorded in the Bin-log of master. Slave when copying the SQL process will parse sing Woo original master side executed the same SQL to perform the advantages again: the advantages of statement level first is to solve the disadvantage of the row level, do not need to record each row of data changes, Reduce the Bin-log log volume, save IO, improve performance, because it only requires the details of the statement executed in the Master lock, as well as the context of the execution statement. Cons: Because only record statements, so, at the statement level has been found that there are many situations will cause the MySQL replication problems, mainly when the data is modified using certain functions or functions will appear. 1.3 Mixed Automatic Mode in Mixed mode, MySQL distinguishes between the log formats that are treated for records based on each specific SQL statement executed, that is, choosing between statement and row. If the SQL statement is really a statement that modifies data such as update or delete, then all rows are changed.the difference between a row pattern and a statement pattern1. Statement mode: 1 million records only 1 delete * from test; Delete 1 million records 2.row mode 1 million record 1 million delete command 1.4 Enterprise scenario How to choose Binlog Mode 1, internet company, Using MySQL with relatively few features (stored procedures, triggers, functions) Select the default statement mode, Statement level (default) 2, if the company uses the special features of MySQL (Stored procedures, triggers, functions) Select Mixed Mode 3, If the company uses the special features of MySQL (Stored procedures, triggers, functions) and wants the data to be maximized, it is best to choose the difference between row level mode and statement mode 1. Statement mode: 1 million records only 1 delete * from test ; You can delete 1 million records 2.row mode 1 million record 1 million delete command 1.5 How to configure Binlog View Binlog mode in the database

1. [email protected] 11:28:05->show global variables like ‘%binlog_format%‘;
2. +---------------+-----------+
3. | Variable_name | Value     |
4. +---------------+-----------+
5. | binlog_format | STATEMENT |
6. +---------------+-----------+
7. 1 row in set (0.00 sec)

The configuration file parameters are as follows:

1. [myslqd]
2. log-bin=mysql-bin
3. #binlog_format="STATEMENT"
4. #binlog_format="ROW"
5. #binlog_format="MIXED"

Run-time online modification (temporary);

1. mysql>SET GLOBAL binlog_format = ‘ROW‘;
2. mysql>show global variables like ‘%binlog_format%‘;
3. The other mode commands are:
4. SET GLOBAL binlog_format = ' MIXED ';
   
5. SET GLOBAL binlog_format = ' STATEMENT ';

Explanation: Global= Global Session session= Current session Verify Binlog logging effect in row mode

2. [[email protected] 3306]# mysqlbinlog --base64-output="decode-rows" --verbose mysql-bin.000248
4. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
6. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
8. /*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
10. DELIMITER /*!*/;
12. # at 4
14. #160628 11:06:52 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.49-log created 160628 11:06:52 at startup
16. # Warning: this binlog is either in use or was not closed properly.
18. ROLLBACK/*!*/;
20. # at 107
22. #160628 11:07:09 server id 1  end_log_pos 177   Query   thread_id=1     exec_time=0     error_code=0
24. SET TIMESTAMP=1467083229/*!*/;
26. SET @@session.pseudo_thread_id=1/*!*/;
28. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
30. SET @@session.sql_mode=0/*!*/;
32. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
34. /*!\C utf8 *//*!*/;
36. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
38. SET @@session.lc_time_names=0/*!*/;
40. SET @@session.collation_database=DEFAULT/*!*/;
42. BEGIN
44. /*!*/;
46. # at 177
48. # at 223
50. #160628 11:07:09 server id 1  end_log_pos 223   Table_map: `oldboy`.`sc` mapped to number 33
52. #160628 11:07:09 server id 1  end_log_pos 785   Update_rows: table id 33 flags: STMT_END_F
54. ### UPDATE `oldboy`.`sc`
56. ### WHERE
58. ###   @1=1
60. ###   @2=1001

Three modes of MYSQ Binlog