|
ead_id=1 exec_time=0 error_code=0
use `tempdb`/*!*/;
SET TIMESTAMP=1412316576/*!*/;
create table tb1(id smallint,val varchar(10))
/*!*/;
# at 304
#141003 14:09:56 server id 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316596/*!*/;
BEGIN
/*!*/;
# at 374
#141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316596/*!*/;
insert into tb1 values(1,'jack')
/*!*/;
# at 471
#141003 14:09:56 server id 1 end_log_pos 498 Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--从以上的内容可以看出二进制日志记录了所有操作的DML语句及其开销，以及一些系统环境变量的信息。
d、管理二进制日志
--对于二进制日志，应尽可能保存在安全的位置，与数据分开存储
--使用show binary logs获取二进制日志相关信息
root@localhost[(none)]> help show binary logs;
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [HELP PURGE BINARY LOGS], that shows
how to determine which logs can be purged.
root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 147 |
| binlog.000002 | 147 |
| binlog.000003 | 147 |
| binlog.000004 | 498 |
+---------------+-----------+
show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。
root@localhost[(none)]> help show binlog events; --获取帮助信息
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.
root@localhost[(none)]> show binlog events;
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
| binlog.000001 | 107 | Rotate | 1 | 147 | binlog.000002;pos=4 |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
root@localhost[(none)]> show binlog events in 'binlog.000005'; --binlog.000005不存在，所以报错
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
--下面的这个查询中，前面执行的DML在这里均可以看到
root@localhost[tempdb]> show binlog events in 'binlog.000004';
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| binlog.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
| binlog.000004 | 107 | Query | 1 | 194 | create database tempdb |
| binlog.000004 | 194 | Query | 1 | 304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) |
| binlog.000004 | 304 | Query | 1 | 374 | BEGIN |
| binlog.000004 | 374 | Query | 1 |