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

This article is about how to set up binlog_format=MIXED in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Binlog_format comes in three formats, STATEMENT, ROW, and MIXED. Statement records the actual SQL statements in the binary log, while ROW records the changes to the actual lines in the binary log.

In binary logs, MIXED is recorded in STATEMENT format by default, but it is converted to ROW format in the following six cases:

In the first case: DNB engine, the DML operation of the table is recorded in ROW format.

The second case: the SQL statement contains the UUID () function.

The third case: the self-growth field has been updated.

The fourth case: contains the INSERT DELAYED statement.

Fifth case: user-defined functions (UDF) are used.

The sixth case: the use of temporary tables.

Let's take a look at a case where both the master and slave MySQL5.6,binlog_format are set to MIXED format to see what kind of problem there will be.

Main library:

Click (here) to collapse or open

Mysql > select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | steven |

| | 2 | steven |

| | 3 | steven |

| | 4 | steven |

| | 5 | steven |

| | 6 | steven |

+-+ +

From the library:

Click (here) to collapse or open

Mysql > select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | steven |

| | 2 | steven |

| | 3 | steven |

| | 4 | steven |

| | 5 | steven |

+-+ +

Perform the following SQL on master:

Click (here) to collapse or open

Mysql > update test01 set name='StevenBJ' where id=6

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | steven |

| | 2 | steven |

| | 3 | steven |

| | 4 | steven |

| | 5 | steven |

| | 6 | StevenBJ |

+-+ +

Viewing the binary log does not report an error because it is in mixed mode, but it is recorded in statement format by default.

After modifying the binlog_format=row, execute the statement in master, and then view the binary log in master:

[root@mysql data] # mysqlbinlog-- no-defaults mysql-bin.000004

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 170808 11:32:02 server id 1 end_log_pos 120 CRC32 0x078ce9f0 Start: binlog v 4, server v 5.6.36-log created 170808 11:32:02 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACKUPUBERBACKUBUBUBUR

BINLOG'

SjCJWQ8BAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAACyMIlZEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfDp

JAc=

'/ *! * /

# at 120

# 170808 11:45:20 server id 1 end_log_pos 192 CRC32 0x736e3ccf Query thread_id=2 exec_time=1 error_code=0

SET timestamp 1502163920

SET @ @ session.pseudoplastics thread readreadable cards idler / session.

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1075838976 Universe

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C latin1 * /! * /

SET @ @ session. Session. Session setting client.session. Session. Collationalization connections8 session. Session. Collationalization server33.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 192

# 170808 11:45:20 server id 1 end_log_pos 244 CRC32 0xda92178e Table_map: `test`.`test01` mapped to number 81

# at 244

# 170808 11:45:20 server id 1 end_log_pos 310 CRC32 0x65485ceb Update_rows: table id 81 flags: STMT_END_F

BINLOG'

0DOJWRMBAAANAAAAPQAAAAAAFEAAAAAAAAEABHRlc3QABnRlc3QwMQACAw8CHgADjheS2g

0DOJWR8BAAAAQgAAADYBAAAAAFEAAAAAAAEAAgAC///8BgAAAAhTdGV2ZW5CSvwGAAAAClN0ZXZl

BkJlaWrrXEhl

'/ *! * /

# at 310

# 170808 11:45:20 server id 1 end_log_pos 341 CRC32 0xbeced65f Xid = 55

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

[root@mysql data] # date

Tue Aug 8 11:47:01 CST 2017

Thank you for reading! This is the end of the article on "how to set up binlog_format=MIXED in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.