mysqlbinlog
mysql 日誌檔匯出
可以檢視過去 mysql server 跑過的 sql 命令
若有資料庫、table 損毀，可以由日誌檔調出、重建資料
大致語法︰
mysqlbinlog --start-date="2011-01-02 9:00:00" --stop-date="2011-02-01 20:00:00" /usr/local/mysql/var/mysql-bin.000001
匯出的檔案內容類似︰
/*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #091102 11:11:38 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 091102 11:11:38 at startup ROLLBACK/*!*/; # at 98 #091105 22:17:50 server id 1 end_log_pos 126 Intvar SET INSERT_ID=873/*!*/; # at 126 #091105 22:17:50 server id 1 end_log_pos 275 Query thread_id=4 exec_time=0 error_code=0 use icdb/*!*/; SET TIMESTAMP=1257430670/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; INSERT INTO tLogin VALUES (0,1,'2009-11-05 22:17:50',0,'ccccccccc909',0,3232235670)/*!*/; # at 275 #091105 22:17:50 server id 1 end_log_pos 428 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1257430670/*!*/; UPDATE tMember SET logincount=logincount+1,last_login='2009-11-05 22:17:50' WHERE kid=1/*!*/; # at 428 #091105 22:17:50 server id 1 end_log_pos 456 Intvar SET INSERT_ID=874/*!*/; # at 456 #091105 22:17:50 server id 1 end_log_pos 605 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1257430670/*!*/; INSERT INTO tLogin VALUES (0,1,'2009-11-05 22:17:50',0,'ccccccccc910',0,3232235670)/*!*/; # at 605 #091105 22:18:30 server id 1 end_log_pos 739 Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1257430710/*!*/; alter table tBorder add privates tinyint(1) unsigned default 0/*!*/; # at 739 #091105 22:24:23 server id 1 end_log_pos 767 Intvar SET INSERT_ID=5/*!*/; : : : : : : : : : #091105 23:37:22 server id 1 end_log_pos 8133 Rotate to mysql-bin.000015 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
