二进制日志的作用是记录数据库的更改，然后用于复制和PITR(即时恢复)，对那些不改变数据库的语句则不会写入二进制日志，如果一些常用的查询语句等。二进制日志按照master上事务提交的顺序记录他们，取决于事务提交时间。我们通过具体的实例查看一下二进制日志具体的记录内容：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database oldboy;
Query OK, 0 rows affected (0.01 sec)
mysql> create database oldboy;
Query OK, 1 row affected (0.00 sec)
mysql> use oldboy;
Database changed
mysql> create table tbl (text TEXT);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tbl values("hello,everyone!");
Query OK, 1 row affected (0.01 sec)
mysql> select * from tbl;
+-----------------+
| text|
+-----------------+
| hello,everyone! |
+-----------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql>
flushlogs命令强制轮换（rotate）二进制文件，从而得到一个完整的二进制文件。下面我们使用show binlog events命令查看该文件。
mysql> show binlog events in 'master-bin.000002'\G;
*************************** 1. row***************************
Log_name:master-bin.000002
Pos: 4
Event_type:Format_desc
Server_id: 165
End_log_pos: 120
Info: Serverver: 5.6.35-log, Binlog ver: 4
*************************** 2. row***************************
Log_name:master-bin.000002
Pos: 120
Event_type: Query
Server_id: 165
End_log_pos: 334
Info: GRANTREPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.10.%' IDENTIFIED BY PASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
*************************** 3. row***************************
Log_name:master-bin.000002
Pos: 334
Event_type: Query
Server_id: 165
End_log_pos: 434
Info: createdatabase oldboy
*************************** 4. row***************************
Log_name:master-bin.000002
Pos: 434
Event_type: Query
Server_id: 165
End_log_pos: 528
Info: createdatabase test
*************************** 5. row***************************
Log_name:master-bin.000002
Pos: 528
Event_type: Query
Server_id: 165
End_log_pos: 613
Info: dropdatabase test
*************************** 6. row***************************
Log_name:master-bin.000002
Pos: 613
Event_type: Query
Server_id: 165
End_log_pos: 702
Info: dropdatabase oldboy
*************************** 7. row***************************
Log_name:master-bin.000002
Pos: 702
Event_type: Query
Server_id: 165
End_log_pos: 802
Info: createdatabase oldboy
*************************** 8. row ***************************
Log_name:master-bin.000002
Pos: 802
Event_type: Query
Server_id: 165
End_log_pos: 908
Info: use`oldboy`; create table tbl (text TEXT)
*************************** 9. row***************************
Log_name:master-bin.000002
Pos: 908
Event_type: Query
Server_id: 165
End_log_pos: 991
Info: BEGIN
*************************** 10. row***************************
Log_name:master-bin.000002
Pos: 991
Event_type: Query
Server_id: 165
End_log_pos: 1111
Info: use`oldboy`; insert into tbl values ("hello,everyone!")
*************************** 11. row***************************
Log_name:master-bin.000002
Pos: 1111
Event_type: Xid
Server_id: 165
End_log_pos: 1142
Info: COMMIT/* xid=378 */
*************************** 12. row***************************
Log_name:master-bin.000002
Pos: 1142
Event_type: Rotate
Server_id: 165
End_log_pos: 1190
Info:master-bin.000003;pos=4
12 rows in set (0.01 sec)
ERROR:
No query specified
mysql>
Event_type:事件类型，Server_id:服务器ID, Log_name:存储事件的文件名，Pos：事件在文件中的开始位置，即事件的第一个字节。End_log_pos，事件在文件中的结束位置，也是下一个事件的开始位置。Info，事件信息的可读文本。
网友评论