萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL使用二進制日志來恢復數據二種方法

MySQL使用二進制日志來恢復數據二種方法

MySQL使用二進制日志來恢復數據二種方法

如果MySQL服務器啟用了二進制日志,你可以使用mysql教程binlog工具來恢復從指定的時間點開始 (例如,從你最後一次備份)直到現在或另一個指定的時間點的數據。關於啟用二進制日志的信息,參見5.11.3節,“二進制日志”。對於 mysqlbinlog的詳細信息,“mysqlbinlog:用於處理二進制日志文件的實用工具”。

  要想從二進制日志恢復數據,你需要知道當前二進制日志文件的路徑和文件名。一般可以從選項文件(即my.cnf or my.ini,取決於你的系統)中找到路徑。如果未包含在選項文件中。

mysqlbinlog工具的使用,大家可以看MySQL的幫助手冊。裡面有詳細的用,

在這個例子中,重點是--start-position參數和--stop-position參數的使用。

 

·--start-position=N

 

從二進制日志中第個位置等於N參量時的事件開始讀。

 

·--stop-position=N

 

從二進制日志中第個位置等於和大於N參量時的事件起停止讀。

 

 

 

OK,現在開始,要啟動二進制日志記錄,要先在my.cnf / my.ini文件的mysqld裡添加

 

log-bin=日志名

 

在這裡,偶是的設置是log-bin=liangck

 

然後再啟動mysql服務,因為偶是用windows系統,所以執行net start mysql命令即可。

 

 

 

然後在一測試數據庫教程裡,創建一個表,並添加記錄。

 

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)

 

??????????????????????????????????

 

OK,現在測試數據已經建好了,要求是什麼呢?

 

就是將test表的數據全部恢復出來。

 

先用mysqlbinlog工具將日志文件生成txt文件出來分析。

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000001 > G:/001.txt

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002 > G:/002.txt

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000003 > G:/003.txt

 

通過這三個命令,可以在G盤下生成個文件,裡面分別記錄了日志文件的內容,也就是用戶操作的步驟。

 

因為我們需要重做第一個日志文件的所有操作,所以這裡只需要將第一個日志文件全恢復就行了。

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000001 | mysql -uroot –p

 

Ok,接著,我們需要分析的是第二個日志文件。為什麼要分析它呢,因為它中途執行了一個操作是DELETE,因為我們要做的是恢復全部數據,也就是我們不希望去重做這個語句。所以在這裡我們要想辦法去繞開它。

 

我們先打開.txt文件來分析一下。

 

/*

 

 

/*!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.ps教程eudo_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 liangck.000003  pos: 4

 

DELIMITER ;

 

# End of log file

 

ROLLBACK /* added by mysqlbinlog */;

 

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

 

 

?????????????????????????????????????

 

*/

 

 

在這個文件中,我們可以看到DELETE的操作的起始位置是,終止位置是.那麼我們只要重做第二個日志文件的開頭到的操作,然後再從到末尾的操作,我們就可以把數據給恢復回來,而不會DELETE數據。所以執行兩個命令:

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002 --stop-pos=875 | mysql -uroot -p

 

 

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002 --start-pos=1008 | mysql -uroot -p mytest

 

 

 

OK,現在第二個日志文件的數據了。

 

第三個日志文件也是同理,只要找到DROP TABLE的位置,就可以了。

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.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)


方法二

要想從二進制日志恢復數據,你需要知道當前二進制日志文件的路徑和文件名。一般可以從選項文件(即my.cnf or my.ini,取決於你的系統)中找到路徑。如果未包含在選項文件中,當服務器啟動時,可以在命令行中以選項的形式給出。啟用二進制日志的選項為-- log-bin。要想確定當前的二進制日志文件的文件名,輸入下面的MySQL語句:

SHOW BINLOG EVENTS G

你還可以從命令行輸入下面的內容:

mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G'

將密碼my_pwd替換為服務器的root密碼。

1. 指定恢復時間

對於MySQL 4.1.4,可以在mysqlbinlog語句中通過--start-date和--stop-date選項指定DATETIME格式的起止時間。舉例說明,假設在今天上午10:00(今天是2006年4月20日),執行SQL語句來刪除一個大表。要想恢復表和數據,你可以恢復前晚上的備份,並輸入:

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456

  mysql -u root -pmypwd

該命令將恢復截止到在--stop-date選項中以DATETIME格式給出的日期和時間的所有數據。如果你沒有檢測到幾個小時後輸入的錯誤的SQL語句,可能你想要恢復後面發生的活動。根據這些,你可以用起使日期和時間再次運行mysqlbinlog:

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456

  mysql -u root -pmypwd

在該行中,從上午10:01登錄的SQL語句將運行。組合執行前夜的轉儲文件和mysqlbinlog的兩行可以將所有數據恢復到上午10:00前一秒鐘。你應檢查日志以確保時間確切。下一節介紹如何實現。

2. 指定恢復位置

也可以不指定日期和時間,而使用mysqlbinlog的選項--start-position和--stop-position來指定日志位置。它們的作用與起止日選項相同,不同的是給出了從日志起的位置號。使用日志位置是更准確的恢復方法,特別是當由於破壞性SQL語句同時發生許多事務的時候。要想確定位置號,可以運行mysqlbinlog尋找執行了不期望的事務的時間范圍,但應將結果重新指向文本文件以便進行檢查。操作方法為:

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

該命令將在/tmp目錄創建小的文本文件,將顯示執行了錯誤的SQL語句時的SQL語句。你可以用文本編輯器打開該文件,尋找你不要想重復的語句。如果二進制日志中的位置號用於停止和繼續恢復操作,應進行注釋。用log_pos加一個數字來標記位置。使用位置號恢復了以前的備份文件後,你應從命令行輸入下面內容:

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456

  mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456

  mysql -u root -pmypwd

上面的第1行將恢復到停止位置為止的所有事務。下一行將恢復從給定的起始位置直到二進制日志結束的所有事務。因為mysqlbinlog的輸出包括每個SQL語句記錄之前的SET TIMESTAMP語句,恢復的數據和相關MySQL日志將反應事務執行的原時間

 

copyright © 萬盛學電腦網 all rights reserved