Mysql二進制恢複詳解

Mysql二進制日志

特點：

1：二進制日志以一種更有效的格式，并且是事務安全的方式包含更新日志中可用的所有資訊。

2：二進制日志包含了所有更新了資料或者已經潛在更新了資料（例如，沒有比對任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改。

3：二進制日志還包含關于每個更新資料庫的語句的執行時間資訊。它不包含沒有修改任何資料的語句。如果你想要記錄所有語句（例如，為了識别有問題的查詢），你應使用一般查詢日志。

4：二進制日志的主要目的是在恢複使能夠最大可能地更新資料庫，因為二進制日志包含備份後進行的所有更新。

5：二進制日志還用于在主複制伺服器上記錄所有将發送給從伺服器的語句。

6：運作伺服器時若啟用二進制日志則性能大約慢1%。但是，二進制日志的好處，即用于恢複并允許設定複制超過了這個小小的性能損失。

Windows 啟用mysql二進制日志：

mysql有以下幾種日志：

錯誤日志： -log-err

查詢日志： -log

慢查詢日志: -log-slow-queries 

更新日志: -log-update

二進制日志： -log-bin

如圖看出log_bin沒有啟動：

啟動mysql二進制檔案：

更改my.ini：

[mysqld]

#binlog

server-id=1

log-bin=c:/mysqlbin/binlog.log

log-bin-index=c:/mysqlbin/binlog.index

重新開機mysql

net stop mysql

net start mysql

再檢視mysql二進制status：

如圖看出log_bin已經啟動：

檢視二進制檔案：

執行mysqlbinlog指令檢視日志：

為了友善檢視日志内容 可以導出到.sql檔案：

然後建立資料庫，建立一個表，并添加記錄。

mysql>flush logs;  --産生第一個日志檔案

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'); 

mysql> insert into test(val,data) values(20,'jia');

mysql> insert into test(val,data) values(30,'hui');

mysql> flush logs; --産生第二個日志檔案 

mysql> insert into test(val,data) values(40,'aaa'); 

mysql> insert into test(val,data) values(50,'bbb');

mysql> insert into test(val,data) values(60,'ccc');

mysql> delete from test where id between 4 and 5;  --删除記錄

mysql> insert into test(val,data) values(70,'ddd');

mysql> flush logs; --産生第三個檔案檔案

mysql> insert into test(val,data) values(80,'dddd');

mysql> insert into test(val,data) values(90,'eeee');

mysql> drop table test; --删除表

二進制恢複表：

mysqlbinlog c:\mysqlbin\binlog.000002 | mysql -uroot -proot

mysqlbinlog c:\mysqlbin\binlog.000003 --stop-pos=861 | mysql -uroot -proot

mysqlbinlog c:\mysqlbin\binlog.000003 --start-pos=992 | mysql -uroot -proot

mysqlbinlog c:\mysqlbin\binlog.000004 --stop-pos=564 | mysql -uroot -proot

舉例：當隻是需要恢複部分記錄的時候，可以使用時間點或位置點來定位

shell> mysqlbinlog --start-date="2009-04-20 9:01:00" --stop-date="2009-04-20 10:00:01" mysql-bin.000001 | mysql -uUser -pUserPWD 

shell> mysqlbinlog --start-position="368301" --stop-position="368312" mysql-bin.000001 | mysql -uUser -pUserPWD

如果MySQL伺服器上有多個要執行的二進制日志，安全的方法是在一個連接配接中處理它們。

原因：假設第1個日志檔案包含一個CREATE TEMPORARY TABLE語句，第2個日志檔案包含一個使用該臨時表的語句，則會造成問題。

恢複多個二進制日志檔案的方法：

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql -uUser -pUserPWD

另一個方法：

shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql

shell> mysqlbinlog hostname-bin.000002 > /tmp/statements.sql

shell> mysql -uUser -pUserPWD -e "source /tmp/statements.sql"

連接配接mysql伺服器檢視和删除二進制日志檔案檢視二進制日志檔案

mysql> SHOW BINLOG EVENTS \G;

mysql> SHOW MASTER LOGS;

清除二進制日志檔案

mysql> PURGE { MASTER|BINARY } LOGS TO 'log_name';

mysql> PURGE { MASTER|BINARY } LOGS BEFORE 'datetime';

例如：

mysql> PURGE MASTER LOGS TO 'mysql-bin.000001'; 

mysql> PURGE MASTER LOGS BEFORE '2009-12-20 13:00:00';

另一種清除二進制日志方法

mysql > flush logs;

mysql > reset master;

本文轉自 Art_Hero 51CTO部落格，原文連結：http://blog.51cto.com/curran/723891，如需轉載請自行聯系原作者