MySQL常用工具（二）：日誌分析工具mysqlbinlog
1、刷新日誌
mysql> flush logs;
2、建庫
mysql> create database tws;
3、使用庫
mysql> use tws
4、建表
mysql> CREATE TABLE class_info(`id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',`class` int(11) NOT NULL COMMENT '班級',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分數',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8;
Advertisements
5、寫入數據
mysql> insert into class_info(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85);
6、使用test庫
mysql> use test
7、創建測試表
mysql> CREATE TABLE class_info_test(`id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',`class` int(11) NOT NULL COMMENT '班級',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分數',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8;
Advertisements
8、寫入數據
mysql> insert into class_info_test(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85);
使用mysqlbinlog不加任何參數，顯示日誌（黃色背景的為剛剛的操作語句）
# mysqlbinlog mysql-bin.000017
------------------開始---------------------
;
;
DELIMITER ;
# at 4
#161213 9:27:05 server id 185 end_log_pos 107 Start: binlog v 4, server v 5.5.15-log created 161213 9:27:05
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
OQVQWA+5AAAAZwAAAGsAAAABAAQANS41LjE1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
';
# at 107
#161213 9:27:27 server id 185 end_log_pos 188 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1481639247;
SET @@session.pseudo_thread_id=33;
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;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
create database tws
;
# at 188
#161213 9:27:36 server id 185 end_log_pos 523 Query thread_id=33 exec_time=0 error_code=0
use tws;
SET TIMESTAMP=1481639256;
CREATE TABLE class_info(`id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',`class` int(11) NOT NULL COMMENT '班級',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分數',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8
;
# at 523
#161213 9:27:43 server id 185 end_log_pos 590 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1481639263;
BEGIN
;
# at 590
#161213 9:27:43 server id 185 end_log_pos 618 Intvar
SET INSERT_ID=1;
# at 618
#161213 9:27:43 server id 185 end_log_pos 790 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1481639263;
insert into class_info(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85)
;
# at 790
#161213 9:27:43 server id 185 end_log_pos 817 Xid = 1765
COMMIT;
# at 817
#161213 9:27:51 server id 185 end_log_pos 1158 Query thread_id=33 exec_time=0 error_code=0
use test;
SET TIMESTAMP=1481639271;
CREATE TABLE class_info_test(`id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',`class` int(11) NOT NULL COMMENT '班級',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分數',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8
;
# at 1158
#161213 9:27:56 server id 185 end_log_pos 1226 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1481639276;
BEGIN
;
# at 1226
#161213 9:27:56 server id 185 end_log_pos 1254 Intvar
SET INSERT_ID=1;
# at 1254
#161213 9:27:56 server id 185 end_log_pos 1432 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1481639276;
insert into class_info_test(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85)
;
# at 1432
#161213 9:27:56 server id 185 end_log_pos 1459 Xid = 1769
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
----------------------------結束-----------------------
9、只顯示單個庫的操作日誌
# mysqlbinlog mysql-bin.000017 -d tws
10、忽略掉前3個操作
# mysqlbinlog mysql-bin.000017 -o 3
11、將結果導入某個文件(-r)
# mysqlbinlog mysql-bin.000017 -d tws -r /tmp/tws.sql
12、簡化顯示(-s)
# mysqlbinlog mysql-bin.000017 -d tws -s
13、導出指定時間段的操作日誌
# mysqlbinlog mysql-bin.000017 --start-datetime='2016-12-13 09:27:43' --stop-datetime='2016-12-13 09:27:56'
14、導出指定位置範圍的操作日誌
# mysqlbinlog mysql-bin.000017 --start-position=618 --stop-position=790