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

This article introduces the relevant knowledge of "how to save the binary log of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Description

1. Statement is based on the SQL statement, and each sql involving modification is recorded in binlog.

Disadvantages: the amount of logs is too large, such as sleep () function, last_insert_id () >, user-definedfuctions (udf), master-slave replication and so on.

2. ROW is based on rows and only records the changes, not the context of the sql.

Cons: if you encounter update..set..wheretrue,binlog, the amount of data will increase.

3. It is recommended to use MIXED and statement is used in the statement.

The function is stored in ROW.

Example

# lee @ acer in / var/log/mysql [14:45:44] $mysqlbinlog-vv mysql SET bin.000076 Universe / 50530 SET @ @ SESSION.PSEUDOtellSLAVEMODEN1Universe OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / / # at 4 170713 14:35:53 server id 1 end_log_pos 123 CRC32 0xbf52e415 Start: binlog v 4, server v 5.7.18-0ubuntu0.16.04.1-log created 170713 1435 Warning: this binlog is either in use or was not closed properly.BINLOG 'yRnWQ8BAAAdAAAHsAABAQANS43LjE4LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 123 "170713 14:35:53 server id 1 end_log_pos 154CRC32 0xafecf53d Previous-GTIDs# [empty] # at 154" 170713 14:43:03 server id 1 end_log_pos 219 CRC32 0x491b5a7e Anonymous_GTID last_committed=0 sequence_number=1SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSThe server id 1 end_log_pos 318 CRC32 0x4acf1cd1 Query thread_id=8 exec_time=0 error_code=0use `test` / *! * /; SET timestamp 1499928183 bank SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session.sqlharmmode1436549152, @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment offset1 Universe utf8 * / *! / SET @ @ session.session. Collationalization timestamps namespace namespaces @ @ session.session .collationalization databases database databases default clientholders SET @ @ session.collationalization databaseholders default clientbooks, alter table t add c2 textbooks, alter session c2 textbooks, alter session.collationalization connection33 CRC32 0x8437eaf9 Anonymous_GTID last_committed=1 sequence_number=2SET @ @ SESSION.GTID_NEXT= 'session. Collationalization timestamps server id 1 session 383 CRC32 0x8437eaf9 Anonymous_GTID last_committed=1 sequence_number=2SET @ @ SESSION.GTID_NEXT=' session. # at 383 "170713 14:44:02 server id 1 end_log_pos 455 CRC32 0x57adaf15 Query thread_id=8 exec_time=0 error_code=0SET timestamp 1499928242" at 455 "170713 14:44:02 server id 1 end_log_pos 504 CRC32 0xdba65133 Table_map: `test`.t` mapped to number 49zinc at 504" 170713 14:44:02 server id 1 end_log_pos 556 CRC32 0x19769a28 Write_rows: table id 498 flags: STMT_END_F BINLOG 'shZnWRMBAAAMAQAAPgBAAAAPIBAAAAAEABHRlc3QAAXQAAAwMPAAAMeAAIHM1Gm2Gm2WAR4BAAAANAAACwCAAAAAAAAAAAAAAAAgADADAAGAGYxG2GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPgBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # INSERT INTO `test`.`t` # # SET### @ 1 end_log_pos 3 / * INT meta=0 nullable=1 is_null=0 * / # @ 2 room / * VARSTRING (30) meta=30 nullable=1 is_null=0 * / # # @ 3 roomroomllala` / * BLOB/TEXT meta=2 nullable=1 is_null=0 * / # at 556 "170713 14:44:02 server id 1 end_log_pos 587 CRC32 0x6184a9a1 Xid = 47COMITUR # at 587 "170713 14:44:16 server id 1 end_log_pos 652 CRC32 0xc585d273 Anonymous_GTID last_committed=2 sequence_number=3SET @ @ SESSION.GTID_NEXT= 'ANONYMOSAccording to SESSION.GTID_NEXT=" at 652 "170713 14:44:16 server id 1 end_log_pos 724 timeshot 1499928256" # at 724 years 170713 14:44:16 server id 1 end_log_pos 773 CRC32 0x2c52eaa1 Table_map: `test`.`t` mapped to number 49 examples at 773 "170713 14:44:16 server id 1 end_log_pos 816 CRC32 0xa80bd454 Delete_rows: table id 498 flags: STMT_END_F BINLOG 'wBZnWRMBAAAMAAMAQAAUDAAAAPIBAAAAAAAAAWHRlc3QAXAAwMPGAMMeAAIHoepSLABAABZnWSABAAAKwAADAAAAPIBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # DELETE FROM `test`.`t` # # WHERE### @ 1cm 1 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2roomdd' / * VARSTRING (30) meta=30 nullable=1 is_null=0 * / # @ 3=NULL / * BLOB/TEXT meta=2 nullable=1 is_null=1 * / # at 816 "170713 14:44:16 server id 1 end_log_pos 847 CRC32 0x9d03bcd0 Xid = 48COMIT Universe purposeset @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * / DELIMITER This is the end of the introduction of # End of log files to 50003 SET completed logs. Thank you for your reading. Thank you for your reading. This is how the binary logs of mysql are saved. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.