MySQL 二进制日志(Binary Log)(二)

b、切换日志
suse11b:~ # mysql -uroot -pxxx
root@localhost[(none)]> flush logs;
Query OK, 0 rows affected (0.04 sec)

root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 78 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000002 #切换后产生了000002
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001

root@localhost[(none)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 117 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000003 #切换后产生了000003
-rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002

root@localhost[(none)]> system mysqladmin refresh #使用mysqladmin命令行工具refresh方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002
-rw-rw---- 1 mysql mysql 156 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000004 #切换后产生了000004
-rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003

c、模拟产生二进制日志及查看内容
root@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.00 sec)

root@localhost[(none)]> use tempdb
Database changed
root@localhost[tempdb]> create table tb1(id smallint,val varchar(10));
Query OK, 0 rows affected (0.00 sec)

root@localhost[tempdb]> insert into tb1 values(1,'jack');
Query OK, 1 row affected (0.01 sec)

root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004
bin 8.T
5.5.39-log
z=.T
tempdb
create database tempdb
tempdb
create table tb1(id smallint,val varchar(10))
tempdb
BEGIN
tempdb
insert into tb1 values(1,'jack')

root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index
/var/lib/mysql/binarylog/binlog.000001
/var/lib/mysql/binarylog/binlog.000002
/var/lib/mysql/binarylog/binlog.000003
/var/lib/mysql/binarylog/binlog.000004

--使用命令行工具mysqlbinlog直接提取二进制日志的内容
root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141003 13:46:39 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#141003 14:08:58 server id 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316538/*!*/;
SET @@session.pseudo