MySQlStudy学习之--MySQl二进制日志管理 - 数据库编程

AAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=
'/*!*/;
# at 114
#150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1436341184/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 189
#150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5 exec_time=0 error_code=0
use prod/*!*/;
SET TIMESTAMP=1436341184/*!*/;
insert into t1 values (4,'rose')
/*!*/;
# at 291
#150708 15:39:44 server id 2 end_log_pos 318 Xid = 23
COMMIT/*!*/;

# at 318
#150708 15:42:47 server id 2 end_log_pos 393 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
BEGIN
/*!*/;
# at 393
#150708 15:42:47 server id 2 end_log_pos 495 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
insert into t1 values (5,'john')
/*!*/;
# at 495
#150708 15:42:47 server id 2 end_log_pos 522 Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@mysrv mysql]#

查看生成的二进制日志：
mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       114 |
| mysql-bin.000002 |     63438 |
| mysql-bin.000003 |   1096670 |
| mysql-bin.000004 |       252 |
| mysql-bin.000005 |       114 |
| mysql-bin.000006 |       133 |
| mysql-bin.000007 |       114 |
| mysql-bin.000008 |       114 |
| mysql-bin.000009 |       157 |
| mysql-bin.000010 |       157 |
| mysql-bin.000011 |       157 |
| mysql-bin.000012 |       157 |
| mysql-bin.000013 |       157 |
| mysql-bin.000014 |       522 |
+------------------+-----------+
14 rows in set (0.00 sec)

查看日志记录的事件：

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
1 row in set (0.06 sec)


mysql> show binlog events in 'mysql-bin.000014';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000014 |   4 | Format_desc |         2 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4      |
| mysql-bin.000014 | 114 | Query       |         2 |         189 | BEGIN                                        |
| mysql-bin.000014 | 189 | Query       |         2 |         291 | use `prod`; insert into t1 values (4,'rose') |
| mysql-bin.000014 | 291 | Xid         |         2 |         318 | COMMIT /* xid=23 */                          |
| mysql-bin

MySQlStudy学习之--MySQl二进制日志管理(七)