文章目錄

• 開啟二進制日志功能
• 關閉/打開二進制日志記錄
• 刷新二進制日志文件
• 查看二進制日志文件的存儲位置
• 利用二進制日志文件恢復數據的本質
• 二進制日志提取/導出到腳本文件中
• 查看當前二進制日志的最后一個位置
• 查看二進制日志文件的內容
• 執行 SQL 腳本文件以恢復數據
• 二進制日志分析
• 指定恢復時間
• 指定恢復位置
• 數據恢復演示案例
• • 備份數據庫
  • 操作數據
  • 恢復數據

開啟二進制日志功能

在配置文件 /etc/my.cnf 中設置二進制日志文件的保存目錄及文件名前綴：

log-bin=/var/lib/mysql/mybinlog/mysql-bin server-id=1

啟用獨占表空間:

innodb_file_per_table=1

說明：

1.如上的配置，數據庫服務重啟后，就自動生成一個二進制日志文件保存在 /var/lib/mysql/mybinlog 目錄下，而且二進制日志文件的名稱會以 mysql-bin 為前綴，在后面從序號 000001 開始命名。

第一次生成日志文件的名稱為：mysql-bin.000001；如果你刷新了日志，則會生成一個新的日志文件，名稱為：mysql-bin.000002；繼續書刷新日志，則會生成名為：mysql-bin.000003 的日志文件，以此類推。

2.在配置變量 log-bin 的同時必須要配置變量 server-id，否則數據庫服務根本無法重啟。

3.啟用獨占表空間，則每個表都有自己獨立的表空間文件；默認是共享表空間，即所有數據庫使用一個表空間。至于這項功能是否必須開啟，暫時不清楚

關閉/打開二進制日志記錄

mysql> set global sql_log_bin=0; mysql> set global sql_log_bin=1;

刷新二進制日志文件

mysql> flush logs; Query OK, 0 rows affected (0.00 sec)

注：
1.會生成新的二進制日志文件
2.舊日志文件中的數據庫操作日志并不會復制到新日志文件中

查看二進制日志文件的存儲位置

mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mybinlog/mysql-bin | | log_bin_index | /var/lib/mysql/mybinlog/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------+ 6 rows in set (0.01 sec)

看變量 log_bin_basename 的值，得知二進制日志文件存放在目錄 /var/lib/mysql/mybinlog/ 下

利用二進制日志文件恢復數據的本質

二進制日志機制其實就是把一些 DDL、DML 操作語句記錄到二進制日志文件中，而你想利用該文件恢復數據，就必須找到相關數據的命令語句，然后去執行這些命令語句。所以你需要看得懂日志文件的內容，能夠把相關命令語句提取出來。關于提取你想要的命令語句的命令請看下面。

二進制日志提取/導出到腳本文件中

--start-position：指定從哪個位置開始導出日志
--stop-position：指定導出日志的結束位置
--start-datetime：指定從哪個時間開始導出日志，時間格式為：2005-12-25 11:25:56
--stop-datetime：指定導出日志的結束時間

[root@htlwk0001host ~]# mysqlbinlog --no-defaults --start-position=1125 --stop-position=1344 /var/lib/mysql/mybinlog/mysql-bin.000004 > /root/test/001.sql

查看當前二進制日志的最后一個位置

mysql> show master status;

查看日志文件的最后一個位置其實沒有什么意義。

查看二進制日志文件的內容

[root@htlwk0001host ~]# mysqlbinlog --no-defaults /var/lib/mysql/mybinlog/mysql-bin.000004

執行 SQL 腳本文件以恢復數據

[root@htlwk0001host ~]# mysql -uroot -p123456 -D test < /root/test/004.sql;

二進制日志分析

指定恢復時間

對于 MySQL 4.1.4，可以在 mysqlbinlog 語句中通過 --start-date 和 --stop-date 選項指定恢復數據的起止時間。

舉例說明，假設在今天上午10:00（今天是2005年4月20日），不小心刪除一個表。要想恢復表和數據，你可以先恢復前晚上的備份（假設你有定時每天凌晨對數據庫進行備份，備份后自動生成一個新的二進制日志文件 mysql-bin.00004），然后執行下面的語句：

[root@htlwk0001host ~]# mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000004 | mysql -uroot -p123456

這條命令可能有問題，因為 2005-04-20 9:59:59 之前有很多數據庫操作語句，這些操作語句不僅僅是前晚備份時間開始到 2005-04-20 9:59:59 之間的操作語句，前晚備份時間之前也有無數的操作語句，這些操作語句也包括在內吧，難道都執行嗎？都執行肯定會報錯，所以感覺有問題。

接著你可能要恢復 2005-04-20 10:01:00 之后產生的數據，你可以用起使日期和時間再次運行 mysqlbinlog：

[root@htlwk0001host ~]# mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -uroot -p123456

指定恢復位置

也可以不指定日期和時間，而使用 mysqlbinlog 的選項 --start-position 和 --stop-position 來指定日志位置。它們的作用與起止日期選項相同，不同的是給出日志內容中的位置號。使用日志位置是更準確的恢復方法，特別是執行破壞性SQL語句的同時發生許多事務的時候。要想確定位置號，你必須查看日志文件的內容以尋找執行了不期望的事務的時間范圍，建議你將這個時間范圍的日志數據導出到文本文件中以便進行檢查，你可以執行下面的語句：

[root@htlwk0001host ~]# mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/mysql-bin.000004 > /tmp/mysql_restore.sql

執行到停止位置為止的所有事務，即將數據恢復到指定的停止位置：

[root@htlwk0001host ~]# mysqlbinlog --stop-position="368312" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd

將恢復從給定的起始位置直到二進制日志結束的所有事務：

[root@htlwk0001host ~]# mysqlbinlog --start-position="368315" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd

數據恢復演示案例

備份數據庫

先對某個數據庫進行完整的備份：

[root@htlwk0001host ~]# mysqldump -h10.6.208.183 -uroot -p123456 -P3306 --single-transaction --master-data=2 test > test.sql

執行上述的命令語句后，會在當前工作目錄下產生一個備份腳本文件 test.sql。

在 test.sql 文件中我們會看到：

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;

指備份后所有的更改將會保存到 bin-log.000002 二進制文件中，備份數據在日志文件中對應的位置是 107。

操作數據

接著往 test 數據庫下的 student 表中插入兩條記錄，然后執行 flush logs 命令產生一個新的二進制日志文件 bin-log.000003。在 flush logs 之前，數據庫的更改操作日志是保存在文件 bin-log.000002 中，既往表 student 增加記錄的操作日志是保存在文件 bin-log.00002 中。

接著再往 test 數據庫下 teacher 表中增加兩條記錄，然后刪除 student 表和 teacher 表。執行命令 flush logs 之后，增加記錄和刪除表的操作日志都記錄在新的二進制日志文件 bin-log.000003 中。

恢復數據

接著我們來恢復數據，首先導入全備數據：

[root@htlwk0001host ~]# mysql -h10.6.208.183 -uroot -p123456 -P3306 < test.sql

恢復 bin-log.000002：

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/bin-log.000002 | mysql -h10.6.208.183 -uroot -p123456 -P3306

恢復部分 bin-log.000003：

需要查看文件 bin-log.000003 中的內容，找到開始刪除數據的位置，然后將這個位置之前的日志數據導出到 SQL 腳本文件中，然后執行該腳本文件以恢復被刪除的數據；當然你找到恢復數據的位置點之后，也可以直接指定這個位置點，通過下面的語句來恢復數據：

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000003 --stop-position=208 | mysql -h10.6.208.183 -uroot -p123456 -P3306

總結

以上是生活随笔為你收集整理的MySQL二进制日志文件的用法_数据恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。