时间：2021-07-01 10:21:17 帮助过：8人阅读
测试的大版本号为5.7，小版本为5.7.24，默认是没有开启binlog的
修改 my.ini ，新增两个配置：
# 开启bin log
server-id=1
log-bin=mysql-bin测试的表和数据：
create table T(ID int primary key, c int);
update T set c=c+1 where ID=2;重启MySQL，发现多了两个 mysql-bin. 打头的文件，使用MySQL命令查询：
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)使用 show binary log; 查看二进制文件信息：
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 421 |
+------------------+-----------+
1 row in set (0.00 sec)使用 show master status; 查看当前正在写入的binlog文件：
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)使用 show binlog events; 查看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)有一点需要注意的时候，show master status; 里面的Position刚好是最后一个Event的End_log_pos。
如果有多个binlog，查看事件可以指定binlog名称：
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)使用 flush logs; 产生一个新编号的binlog文件：一般是在备份工作完成之后，产生一个新的binlog记录后续的增量记录
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)
## 重新查看之前binlog文件的Event，发现多了一行记录
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)
## 重新查看当前的binlog是哪个
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)
### 查看最新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)使用 reset master; 清空所有的binlog日志：
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
## 重新查看，还原成了初始状态
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)
## 找不到之前的binlog了
mysql> show binlog events in 'mysql-bin.000002';
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
## binlog中的数据也变成了初始化的数据
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)执行两遍更新语句，查看binlog事件：
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)指定位置查询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)指定位置查询，偏移2条，查询4条数据：
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)MySQL重新学之binlog
标签：block tar ESS erro server-id key lock dex master