MYSQL Binlog Log content View

Records all operations (DML statements) of the MySQL database that actually make changes, not statements that do not have any data modified, and statements such as Select and show are not recorded.

The role of the binary log:

1, can complete the function of master-slave replication

2, the recovery operation.

The data can be implemented by Binglog logs, using the Mysqlbinlog command, to achieve point-in-time and location-based recovery operations.

View Binglog parameter information:
Mysql> show global variables like '%bin% ';
+-----------------------------------------+-------------------------------------------------+
| variable_name | Value |
+-----------------------------------------+-------------------------------------------------+
| bind_address | * |
| Binlog_cache_size | 4194304 |
| Binlog_checksum | CRC32 |
| Binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | Abort_server |
| Binlog_format | ROW |
| Binlog_group_commit_sync_delay | 0 |
| Binlog_group_commit_sync_no_delay_count | 0 |
| Binlog_gtid_simple_recovery | On |
| Binlog_max_flush_queue_time | 0 |
| Binlog_order_commits | On |
| Binlog_row_image | Full |
| binlog_rows_query_log_events | OFF |
| Binlog_stmt_cache_size | 32768 |
| Innodb_api_enable_binlog | OFF |
| Innodb_locks_unsafe_for_binlog | OFF |
| Log_bin | On |
| Log_bin_basename | /mydata/mysql/mysql3307/logs/mysql-binlog |
| Log_bin_index | /mydata/mysql/mysql3307/logs/mysql-binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| Log_statements_unsafe_for_binlog | On |
| Max_binlog_cache_size | 1073741824 |
| Max_binlog_size | 524288000 |
| Max_binlog_stmt_cache_size | 18446744073709547520 |
| Sync_binlog | 1 |
+-----------------------------------------+-------------------------------------------------+
+ Rows in Set (0.00 sec)

Binary files with cat,head, or tail command is not visible, you need to view the binary log through the Mysqlbinglog command, the log conversion format, output to the custom file.
To view the Binglog log file:

Mysqlbinlog--no-defaults-v-V--base64-output=decode-rows/mydata/mysql/mysql3307/logs/mysql-binlog.000021/root/ Mysqlbinlog21.log

Meaning:
-V represents the specific execution information that can be viewed.
--base64-output converting binary files to formats
The following are the changes to the recorded memory line in row mode, before the change, and the content after the change.
Modify the logging mode to:

Set global binlog_format=statement;
Set global binlog_format=mixed;
Set global Binlog_format=row;

View Production file Contents/root/mysqlbinlog21.log

/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#180521 1:09:56 server ID 3307101 end_log_pos 123 CRC32 0x79f0cd1c Start:binlog v 4, Server v 5.7.20-log created 180521 1 : 09:56 at startup
# Warning:this Binlog is either on use or was not closed properly.
rollback/*!*/;
# at 123
#180521 1:09:56 Server ID 3307101 end_log_pos 194 CRC32 0x96ca2d5f Previous-gtids
# d6a9d222-5282-11e8-bac7-080027691c44:1-15
# at 194
#180521 9:47:53 Server ID 3307101 end_log_pos 259 CRC32 0x737b7bad GTID last_committed=0 sequence_number=1 rbr_only=no
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:16 '/*!*/;
# at 259
#180521 9:47:53 server ID 3307101 end_log_pos 388 CRC32 0x9eba0300 Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1526867273/*!*/;
SET @ @session. pseudo_thread_id=3/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;
SET @ @session. sql_mode=1436549152/*!*/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
/*!\c UTF8 *//*!*/;
SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=45/*!*/;
SET @ @session. lc_time_names=0/*!*/;
SET @ @session. collation_database=default/*!*/;
SET @ @session. explicit_defaults_for_timestamp=1/*!*/;
CREATE TABLE Test.zs (a timestamp,b timestamp,c timestamp)
/*!*/;
# at 388
#180521 9:49:28 Server ID 3307101 end_log_pos 453 CRC32 0x8a902dce GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:17 '/*!*/;
# at 453
#180521 9:49:28 Server ID 3307101 end_log_pos 521 CRC32 0xfe907eed Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1526867368/*!*/;
BEGIN
/*!*/;
# at 521
#180521 9:49:28 Server ID 3307101 end_log_pos 571 CRC32 0x528b2287 table_map: ' Test '. ' ZS ' mapped to number 219
# at 571
#180521 9:49:28 Server ID 3307101 end_log_pos 607 CRC32 0x2bb11644 write_rows:table ID 219 flags:stmt_end_f
# # # INSERT into ' test '. ' Zs '
# # SET
# # @1=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 607
#180521 9:49:28 Server ID 3307101 end_log_pos 638 CRC32 0xf1b37ef5 Xid = 26
commit/*!*/;
# at 638
#180521 9:49:30 Server ID 3307101 end_log_pos 703 CRC32 0x8d1451b6 GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:18 '/*!*/;
# at 703
#180521 9:49:30 Server ID 3307101 end_log_pos 771 CRC32 0x17f458cd Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1526867370/*!*/;
BEGIN
/*!*/;
# at 771
#180521 9:49:30 Server ID 3307101 end_log_pos 821 CRC32 0x8608f3f8 table_map: ' Test '. ' ZS ' mapped to number 219
# at 821
#180521 9:49:30 Server ID 3307101 end_log_pos 857 CRC32 0x61faa6a3 write_rows:table ID 219 flags:stmt_end_f
# # # INSERT into ' test '. ' Zs '
# # SET
# # @1=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 857
#180521 9:49:30 Server ID 3307101 end_log_pos 888 CRC32 0xc555dada XID = 27
commit/*!*/;
# at 888
#180521 9:49:31 Server ID 3307101 end_log_pos 953 CRC32 0x152275a2 GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:19 '/*!*/;
# at 953
#180521 9:49:31 Server ID 3307101 end_log_pos 1021 CRC32 0x68fadf15 Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1526867371/*!*/;
BEGIN
/*!*/;
# at 1021
#180521 9:49:31 Server ID 3307101 end_log_pos 1071 CRC32 0x999fd973 table_map: ' Test '. ' ZS ' mapped to number 219
# at 1071
#180521 9:49:31 server ID 3307101 end_log_pos 1107 CRC32 0x0c1bb219 write_rows:table ID 219 flags:stmt_end_f
# # # INSERT into ' test '. ' Zs '
# # SET
# # @1=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 1107
#180521 9:49:31 Server ID 3307101 end_log_pos 1138 CRC32 0x0e581441 Xid = 28
commit/*!*/;
# at 1138
#180521 9:51:29 Server ID 3307101 end_log_pos 1203 CRC32 0x6c5d54df GTID last_committed=4 sequence_number=5 rbr_only=no
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:20 '/*!*/;
# at 1203
#180521 9:51:29 Server ID 3307101 end_log_pos 1290 CRC32 0x93e104d9 Query thread_id=4 exec_time=0 error_code=0
SET timestamp=1526867489/*!*/;
SET @ @session. time_zone= ' SYSTEM '/*!*/;
Flush Privileges
/*!*/;
# at 1290
#180521 10:02:20 Server ID 3307101 end_log_pos 1355 CRC32 0x07b66739 GTID last_committed=5 sequence_number=6 rbr_only=no
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:21 '/*!*/;
# at 1355
#180521 10:02:20 Server ID 3307101 end_log_pos 1520 CRC32 0x48415c32 Query thread_id=7 exec_time=0 error_code=0
Use ' Test '/*!*/;
SET timestamp=1526868140/*!*/;
SET @ @session. explicit_defaults_for_timestamp=0/*!*/;
CREATE Table Zs1 (a timestamp,b timestamp null default null,c timestamp null default NULL)
/*!*/;
# at 1520
#180521 10:05:36 Server ID 3307101 end_log_pos 1585 CRC32 0x749f7319 GTID last_committed=6 sequence_number=7 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:22 '/*!*/;
# at 1585
#180521 10:05:36 server ID 3307101 end_log_pos 1657 CRC32 0x381adad5 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1526868336/*!*/;
BEGIN
/*!*/;
# at 1657
#180521 10:05:36 Server ID 3307101 end_log_pos 1708 CRC32 0x8b75b8d0 table_map: ' Test '. ' ZS1 ' mapped to number 220
# at 1708
#180521 10:05:36 Server ID 3307101 end_log_pos 1748 CRC32 0x6f7e879b write_rows:table ID flags:stmt_end_f
# # # INSERT into ' test '. ' Zs1 '
# # SET
# # @1=1526868336/* TIMESTAMP (0) meta=0 nullable=0 is_null=0 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 1748
#180521 10:05:36 server ID 3307101 end_log_pos 1779 CRC32 0x8f7b45df Xid = 74
commit/*!*/;
# at 1779
#180521 10:06:01 server ID 3307101 end_log_pos 1844 CRC32 0x0857208f GTID last_committed=7 sequence_number=8 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:23 '/*!*/;
# at 1844
#180521 10:06:01 server ID 3307101 end_log_pos 1916 CRC32 0xf8b57357 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1526868361/*!*/;
BEGIN
/*!*/;
# at 1916
#180521 10:06:01 server ID 3307101 end_log_pos 1967 CRC32 0x71f69634 table_map: ' Test '. ' ZS1 ' mapped to number 220
# at 1967
#180521 10:06:01 server ID 3307101 end_log_pos CRC32 0x8398dbd6 write_rows:table ID flags:stmt_end_f
# # # INSERT into ' test '. ' Zs1 '
# # SET
# # @1=1526868361/* TIMESTAMP (0) meta=0 nullable=0 is_null=0 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 2007
#180521 10:06:01 server ID 3307101 end_log_pos 2038 CRC32 0xae8d3064 Xid = 77
commit/*!*/;
# at 2038
#180521 10:06:02 server ID 3307101 end_log_pos 2103 CRC32 0x50fdabc1 GTID last_committed=8 sequence_number=9 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:24 '/*!*/;
# at 2103
#180521 10:06:02 server ID 3307101 end_log_pos 2175 CRC32 0x38a21fb3 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1526868362/*!*/;
BEGIN
/*!*/;
# at 2175
#180521 10:06:02 server ID 3307101 end_log_pos 2226 CRC32 0x4a1b7b27 table_map: ' Test '. ' ZS1 ' mapped to number 220
# at 2226
#180521 10:06:02 server ID 3307101 end_log_pos 2266 CRC32 0x397de1b3 write_rows:table ID flags:stmt_end_f
# # # INSERT into ' test '. ' Zs1 '
# # SET
# # @1=1526868362/* TIMESTAMP (0) meta=0 nullable=0 is_null=0 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 2266
#180521 10:06:02 server ID 3307101 end_log_pos 2297 CRC32 0xe2727b87 Xid = 78
commit/*!*/;
# at 2297
#180521 10:06:03 server ID 3307101 end_log_pos 2362 CRC32 0x3cadd4ea GTID last_committed=9 sequence_number=10 rbr_only=yes
/*!50718 SET TRANSACTION Isolation level READ committed*//*!*/;
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:25 '/*!*/;
# at 2362
#180521 10:06:03 server ID 3307101 end_log_pos 2434 CRC32 0x06a0be65 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1526868363/*!*/;
BEGIN
/*!*/;
# at 2434
#180521 10:06:03 server ID 3307101 end_log_pos 2485 CRC32 0xea1f5733 table_map: ' Test '. ' ZS1 ' mapped to number 220
# at 2485
#180521 10:06:03 server ID 3307101 end_log_pos 2525 CRC32 0xba9bf9de write_rows:table ID flags:stmt_end_f
# # # INSERT into ' test '. ' Zs1 '
# # SET
# # @1=1526868363/* TIMESTAMP (0) meta=0 nullable=0 is_null=0 * *
# # @2=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# # @3=null/* TIMESTAMP (0) meta=0 nullable=1 is_null=1 * *
# at 2525
#180521 10:06:03 server ID 3307101 end_log_pos 2556 CRC32 0x1961d7b0 Xid = 79
commit/*!*/;
# at 2556
#180521 10:09:00 server ID 3307101 end_log_pos 2621 CRC32 0xe802a496 GTID last_committed=10 sequence_number=11 rbr_only=no
SET @ @SESSION. gtid_next= ' d6a9d222-5282-11e8-bac7-080027691c44:26 '/*!*/;
# at 2621
#180521 10:09:00 server ID 3307101 end_log_pos 2712 CRC32 0x1b99ba5c Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1526868540/*!*/;
Flush Privileges
/*!*/;
Mysqlbinlog:file '/root/mysqlbinlog21.log ' not found (Errcode:2-No such File or directory)
SET @ @SESSION. gtid_next= ' AUTOMATIC '/* Added by Mysqlbinlog *//*!*/;
DELIMITER;
# End of log file
/*!50003 SET [email protected]_completion_type*/;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

MYSQL Binlog Log content View