mysql日志文件的使用、數據恢復 一、 mysql日志類型 MYSQL有不同類型的日志文件(各自存儲了不同類型的日志)，從它們當中可以查詢到MYSQL裡都做了些什麼，對於MYSQL的管理工作，這些日志文件是不可缺少的。 1. 錯誤日志(The error log)：記錄了數據庫啟動、運行以及停止過程中錯誤信息； 2. ISAM操作日志(The isam log)：記錄了所有對ISAM表的修改，該日志僅僅用於調試ISAM模式； 3. SQL執行日志(The query log)：記錄了客戶端的連接以及所執行的SQL語句； 4. 更新日志(The update log)：記錄了改變數據的語句，已經不建議使用，由二進制日志替代； 5. 二進制日志(The binary log)：記錄了所有對數據庫數據的修改語句； 6. 超時日志(The slow log)：記錄所有執行時間超過最大SQL執行時間(long_query_time)或未使用索引的語句； 7. relay log：如果你是在用mysql的復制、備份功能，那麼從服務器還提供了一種叫做relay log的日志文件。 默認情況下所有日志文件會記錄在MYSQL的數據目錄下，你可以通過強制mysql去關閉並重新打開一個文件進行日志記錄，當然系統會自動加後綴 (如.00001, .00002)，方式有在mysql環境下執行語句 mysql>flush logs; 或者通過mysqladmin管理程序執行 #mysqladmin flush-logs 或 #mysqladmin refresh 二、 mysql日志的配置 這些日志的啟動方式可以在mysqld_safe方式啟動數據庫的時候，後面跟選項參數，也可以在配置文件裡配置，推薦采用第二種方式，配置方法很簡單，我只配置了三種日志： [mysqld] log=/var/log/mysqld_common.log log-error=/var/log/mysqld_err.log log-bin=/var/log/mysqld_bin.bin 三、 mysql日志的查看 日志的查看很簡單，大部分都是文本，直接用vim、less、more之類的工具看就可以了，值得說明的是二進制文件的查看： 1. 首先確定是否開啟了二進制文件記錄功能 Sql代碼 mysql>show variables like 'log_bin'; 2. 如果你想知道現在記錄二進制數據的文件具體信息，你可以通過下列語句看到現在正在記錄哪個文件，以及記錄的當前位置： Sql代碼 mysql>show master status; 3. 查看二進制數據需要借助程序mysqlbinlog，看看它支持哪些選項，根據自己需要來使用。 Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040; 4. 將二進制日志文件導出txt文本文件查看 Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040 >/var/log/mysql/000040.txt; 5. 查詢某個時間范圍的可以執行下列語句，如果記錄很多可以將結果定向到一個文件裡自己慢慢看:-) ： Sql代碼 mysql>mysqlbinlog --start-datetime='2008-01-01 00:00:00' --stop-datetime='2008-08-08 00:00:00' /var/log/mysql/mysql-bin.000040 > ./tmp.log 四、 使用二進制日志恢復數據 mysqlbinlog工具的使用,大家可以看MySQL的幫助手冊，裡面有詳細的用。在這個例子中，重點是--start-position參數和--stop-position參數的使用。 --start-position=N ：從二進制日志中位置等於N參量時的事件開始讀。 --stop-position=N：從二進制日志中位置等於和大於N參量時的事件起停止讀。 1. 創建實驗環境 在一測試數據庫裡，創建一個表，並添加記錄，然後產生日志文件。 Sql代碼 mysql> create table test(id int auto_increment not null primary key,val int,data varchar(20)); mysql> insert into test(val,data) values(10,'liang'); Query OK, 1 row affected (0.03 sec) mysql> insert into test(val,data) values(20,'jia'); Query OK, 1 row affected (0.08 sec) mysql> insert into test(val,data) values(30,'hui'); Query OK, 1 row affected (0.03 sec) mysql> flush logs; --產生第二個日志文件 Query OK, 0 rows affected (0.09 sec) mysql> insert into test(val,data) values(40,'aaa'); Query OK, 1 row affected (0.05 sec) mysql> insert into test(val,data) values(50,'bbb'); Query OK, 1 row affected (0.03 sec) mysql> insert into test(val,data) values(60,'ccc'); Query OK, 1 row affected (0.03 sec) mysql> delete from test where id between 4 and 5; --刪除記錄 Query OK, 2 rows affected (0.05 sec) mysql> insert into test(val,data) values(70,'ddd'); Query OK, 1 row affected (0.03 sec) mysql> flush logs; --產生第三個文件文件 Query OK, 0 rows affected (0.11 sec) mysql> insert into test(val,data) values(80,'dddd'); Query OK, 1 row affected (0.05 sec) mysql> insert into test(val,data) values(90,'eeee'); Query OK, 1 row affected (0.03 sec) mysql> drop table test; --刪除表 Query OK, 0 row affected (0.05 sec) 2. 恢復數據 先用mysqlbinlog工具將日志文件生成txt文件出來分析。 Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001 > /var/log/mysql/000001.txt; mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 > /var/log/mysql/000002.txt; mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 > /var/log/mysql/000003.txt; 通過這三個命令，可以生成分別記錄了日志文件的內容，也就是用戶操作的步驟。 因為我們需要重做第一個日志文件的所有操作，所以這裡只需要將第一個日志文件全恢復就行了。 Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -uroot –p Ok,接著，我們需要分析的是第二個日志文件。為什麼要分析它呢，因為它中途執行了一個操作是DELETE，因為我們要做的是恢復全部數據，也就是我們不希望去重做這個語句。所以在這裡我們要想辦法去繞開它。 我們先打開.txt文件來分析一下。 Sql代碼 /* /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #090427 15:27:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.32-community-log created 090427 15:27:56 BINLOG ' fF71SQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMyLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #090427 15:28:37 server id 1 end_log_pos 176 Query thread_id=1 exec_time=0 error_code=0 use mytest/*!*/; SET TIMESTAMP=1240817317/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!/C gbk *//*!*/; SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 176 #090427 15:28:37 server id 1 end_log_pos 204 Intvar SET INSERT_ID=4/*!*/; # at 204 #090427 15:28:37 server id 1 end_log_pos 312 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817317/*!*/; insert into test(val,data) values(40,'aaa') /*!*/; # at 312 #090427 15:28:37 server id 1 end_log_pos 339 Xid = 12 COMMIT/*!*/; # at 339 #090427 15:28:46 server id 1 end_log_pos 409 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817326/*!*/; BEGIN /*!*/; # at 409 #090427 15:28:46 server id 1 end_log_pos 437 Intvar SET INSERT_ID=5/*!*/; # at 437 #090427 15:28:46 server id 1 end_log_pos 545 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817326/*!*/; insert into test(val,data) values(50,'bbb') /*!*/; # at 545 #090427 15:28:46 server id 1 end_log_pos 572 Xid = 13 COMMIT/*!*/; # at 572 #090427 15:29:35 server id 1 end_log_pos 642 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817375/*!*/; BEGIN /*!*/; # at 642 #090427 15:29:35 server id 1 end_log_pos 670 Intvar SET INSERT_ID=6/*!*/; # at 670 #090427 15:29:35 server id 1 end_log_pos 778 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817375/*!*/; insert into test(val,data) values(60,'ccc') /*!*/; # at 778 #090427 15:29:35 server id 1 end_log_pos 805 Xid = 14 COMMIT/*!*/; # at 805 #090427 15:30:21 server id 1 end_log_pos 875 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817421/*!*/; BEGIN /*!*/; # at 875 #090427 15:30:21 server id 1 end_log_pos 981 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817421/*!*/; delete from test where id between 4 and 5 /*!*/; # at 981 #090427 15:30:21 server id 1 end_log_pos 1008 Xid = 15 COMMIT/*!*/; # at 1008 #090427 15:30:34 server id 1 end_log_pos 1078 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817434/*!*/; BEGIN /*!*/; # at 1078 #090427 15:30:34 server id 1 end_log_pos 1106 Intvar SET INSERT_ID=7/*!*/; # at 1106 #090427 15:30:34 server id 1 end_log_pos 1214 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817434/*!*/; insert into test(val,data) values(70,'ddd') /*!*/; # at 1214 #090427 15:30:34 server id 1 end_log_pos 1241 Xid = 16 COMMIT/*!*/; # at 1241 #090427 15:30:41 server id 1 end_log_pos 1282 Rotate to mysql-bin.000003 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; ――――――――――――――――――――――――――――――――――――― */ 在這個文件中，我們可以看到DELETE的操作的起始位置是875，終止位置是1008,那麼我們只要重做第二個日志文件的開頭到的操作，然後再從到末尾的操作，我們就可以把數據給恢復回來，而不會DELETE數據。所以執行兩個命令： Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 --stop-pos=875 | mysql -uroot –p mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-pos=1008 | mysql -uroot -p OK,現在第二個日志文件的數據了。 第三個日志文件也是同理，只要找到DROP TABLE的位置，就可以了。 Sql代碼 mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 --stop-pos=574 | mysql -uroot –p 現在我們再查一下數據看看: mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 2 | 20 | jia | | 3 | 30 | hui | | 4 | 40 | aaa | | 5 | 50 | bbb | | 6 | 60 | ccc | | 7 | 70 | ddd | | 8 | 80 | dddd | | 9 | 90 | eeee | +----+------+-------+ 9 rows in set (0.00 sec) 可以看到，全部數據都回來了。
