The major version number of the test is 5.7, and the minor version is 5.7.24. binlog is not enabled by default
Modify my.ini and add two new configurations:
# Open bin log server-id=1 log-bin=mysql-bin
Table and data tested:
create table T(ID int primary key, c int); update T set c=c+1 where ID=2;
Restart MySQL and find two more MySQL bin. Header files. Use the MySQL command to query:
mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------------------------+ | log_bin | ON | | log_bin_basename | D:\Program\hecg\mysql-5.7.24-winx64\data\mysql-bin | | log_bin_index | D:\Program\hecg\mysql-5.7.24-winx64\data\mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec)
-
Use show binary log; to view binary information:
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 421 | +------------------+-----------+ 1 row in set (0.00 sec) -
Use show master status; to view the binlog file currently being written:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -
Use show binlog events; to view the events recorded by binlog:
mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000001 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000001 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000001 | 390 | Xid | 1 | 421 | COMMIT /* xid=36 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 7 rows in set (0.00 sec)When you need to pay attention to one thing, show master status; the Position in it is just the end of the last Event.
-
If there are multiple binlogs, you can specify the binlog name when viewing events:
mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000001 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000001 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000001 | 390 | Xid | 1 | 421 | COMMIT /* xid=36 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 7 rows in set (0.00 sec) -
Use flush logs; to generate a binlog file with a new number: generally, after the backup work is completed, a new binlog is generated to record the subsequent incremental records
mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 468 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec) ## Re check the Event of the previous binlog file, and one more line of record is found mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ ... | mysql-bin.000001 | 421 | Rotate | 1 | 468 | mysql-bin.000002;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 8 rows in set (0.00 sec) ## Check the current binlog again mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ### View the records in the latest binlog mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) -
Use reset master; to clear all binlog logs:
mysql> reset master; Query OK, 0 rows affected (0.02 sec) ## Check again and restore to the original state mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ## The previous binlog cannot be found mysql> show binlog events in 'mysql-bin.000002'; ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log ## The data in binlog is also initialized mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) -
Execute the update statement twice to view the binlog event:
update T set c=c+1 where ID=2; mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 486 | Query | 1 | 558 | BEGIN | | mysql-bin.000002 | 558 | Table_map | 1 | 603 | table_id: 108 (test.t) | | mysql-bin.000002 | 603 | Update_rows | 1 | 657 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 657 | Xid | 1 | 688 | COMMIT /* xid=69 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 12 rows in set (0.00 sec) -
Specify location query Event:
mysql> show binlog events in 'mysql-bin.000002' from 154; +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 486 | Query | 1 | 558 | BEGIN | | mysql-bin.000002 | 558 | Table_map | 1 | 603 | table_id: 108 (test.t) | | mysql-bin.000002 | 603 | Update_rows | 1 | 657 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 657 | Xid | 1 | 688 | COMMIT /* xid=69 */ | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ 10 rows in set (0.00 sec) -
Specify location query, offset 2, query 4 data:
mysql> show binlog events in 'mysql-bin.000002' from 154 limit 2,4; +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000002 | 291 | Table_map | 1 | 336 | table_id: 108 (test.t) | | mysql-bin.000002 | 336 | Update_rows | 1 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 1 | 421 | COMMIT /* xid=62 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 1 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ 4 rows in set (0.00 sec)