[MySQL]查看Binlog的內容

        Binlog是MySQL的日誌，通常用作Replication及備份使用，這麼重要的東西當然要了解它囉，預設安裝是不會啟用Binlog的，要啟用它要在my.ini檔加入下列系統參數，然後重啟MySQL生效

#還有其他相關參數就不列了
log-bin=/var/lib/mysql/mysql-bin #必要的，設定位置及檔案名稱
expire_logs_days=14                    #選擇性的，設定保留的期間
binlog-format=statement              #選擇性的，有三種模式，預設是statement，還有row及mixed


        這邊是以全新安裝的MySQL為例子喔，登入MySQL後，查看一下系統變數值，有設定log-bin參數，log_bin變數才會為ON

show variables like '%bin%';

        查看master的狀態，會告訴你目前使用的file及position

show master status;

        查看binlog的事件，binlog是以事件紀錄的，這命令可別直接下在Production喔，要下記得加個limit吧

show binlog events;

        至於實體的binlog檔案得用mysqlbinlog查看喔，這命令有很多參數可加，可以限制時間及位置，我這邊示範方便就沒加了

mysqlbinlog mysql-bin.000001

        現在實際新增資料表，然後插入、修改及刪除資料，最後在刪除資料表看binlog會記錄什，以下是執行的命令

create table t1 (

  id int not null AUTO_INCREMENT,

  title varchar(32) not null,

  description varchar(128) not null,

  upd_time timestamp,

  primary key(id)

  );

  

 insert into t1 (title,description) values ('mysql','mysql1234'),('sql server','sql1234'),('oracle','oracle1234');

 update t1 set description = 'xxxxx' where title = 'oracle';

 delete from t1;

 drop table t1;

        看一下binlog事件吧，幾乎把執行過的命令都記錄了

show binlog events;

        一樣看一下實體binlog內容

$mysqlbinlog  mysql-bin.000001

        因為binlog紀錄所有異動的事件，那Replication就可以取得這些事件，依序回放到Slave去啦

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

        接著看一下binlog為row模式時，紀錄的內容是否有所差別，執行一樣的命令

create table t1 (

  id int not null AUTO_INCREMENT,

  title varchar(32) not null,

  description varchar(128) not null,

  upd_time timestamp,

  primary key(id)

  );

  

 insert into t1 (title,description) values ('mysql','mysql1234'),('sql server','sql1234'),('oracle','oracle1234');

 update t1 set description = 'xxxxx' where title = 'oracle';

 delete from t1;

 drop table t1;

        查看binlog的事件，可以看出row與statement的分別嗎？statement是紀錄執行的命令，row在這邊看出有不一樣，但看還看不出什

        而看實體的binlog內容，配合mysqlbinlog解密輸出參數，就可以看到不一樣的地方囉

mysqlbinlog --start-position=107 --stop-position=332 -v -v --base64-output=DECODE-ROWS mysql-bin.000001

/*!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 107
#141105 17:22:16 server id 1  end_log_pos 332 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1415179336/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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/*!*/;
create table t1 (
  id int not null AUTO_INCREMENT,
  title varchar(32) not null,
  description varchar(128) not null,
  upd_time timestamp,
  primary key(id)
  )
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog --start-position=400 --stop-position=584 -v -v --base64-output=DECODE-ROWS mysql-bin.000001

/*!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 400
# at 448
#141105 17:22:23 server id 1  end_log_pos 448 Table_map: `test`.`t1` mapped to number 34
#141105 17:22:23 server id 1  end_log_pos 557 Write_rows: table id 34 flags: STMT_END_F
### INSERT INTO test.t1
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='mysql' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='mysql1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### INSERT INTO test.t1
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='sql server' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='sql1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### INSERT INTO test.t1
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='oracle' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='oracle1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
# at 557
#141105 17:22:23 server id 1  end_log_pos 584 Xid = 9
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog --start-position=652 --stop-position=779 -v -v --base64-output=DECODE-ROWS mysql-bin.000001

/*!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 652
# at 700
#141105 17:22:32 server id 1  end_log_pos 700 Table_map: `test`.`t1` mapped to number 34
#141105 17:22:32 server id 1  end_log_pos 779 Update_rows: table id 34 flags: STMT_END_F
### UPDATE test.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='oracle' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='oracle1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='oracle' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='xxxxx' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179352 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


mysqlbinlog --start-position=874 --stop-position=1026 -v -v --base64-output=DECODE-ROWS mysql-bin.000001

/*!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 874
# at 922
#141105 17:22:41 server id 1  end_log_pos 922 Table_map: `test`.`t1` mapped to number 34
#141105 17:22:41 server id 1  end_log_pos 1026 Delete_rows: table id 34 flags: STMT_END_F
### DELETE FROM test.t1
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='mysql' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='mysql1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### DELETE FROM test.t1
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='sql server' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='sql1234' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179343 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### DELETE FROM test.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='oracle' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @3='xxxxx' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @4=1415179352 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

        row模式下紀錄的是異動的所有內容，異動那些資料，刪除那些資料都一清二楚，看到這會想到什麼，就可以利用這個作部分的災難還原啦，比如說delete沒加where之類的

        有高人就利用這個原理做了類似Oracle的閃回囉，有興趣可以參考下列連結，自己試試吧
MySQL下实现闪回的设计思路 (MySQL Flashback Feature)