一、binlog 介紹

　　伺服器的二進位制日誌記錄著該資料庫的所有增刪改的操作日誌(前提是要在自己的伺服器上開啟binlog)，還包括了這些操作的執行時間。為了顯示這些二進位制內容，我們可以使用mysqlbinlog命令來檢視。

　　用途1：主從同步

　　用途2：恢復資料庫（也是線上出現一次資料庫檔案丟失後，才對這個有所瞭解並學習的）

　　mysqlbinlog命令用法：shell> mysqlbinlog [options] log_file ...

1) mysqlbinlog 選項示例

常見的選項有以下幾個：

--start-datetime

從二進位制日誌中讀取指定等於時間戳或者晚於本地計算機的時間。取值如：="1470733768" 或者="2016-08-09 5:09:28"

示例:

?

12	`[root@hcloud ~]# mysqlbinlog` `--start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001` `--stop-datetime`

從二進位制日誌中讀取指定小於時間戳或者等於本地計算機的時間取值和上述一樣

--start-position

從二進位制日誌中讀取指定position 事件位置作為開始。取值：="2698"

示例：

?

12	`[root@hcloud ~]# mysqlbinlog` `--start-position="2698" /var/lib/mysql/mysql-bin.000001` `--stop-position`

從二進位制日誌中讀取指定position 事件位置作為事件截至。取值：="2698"

二、環境準備以及備份恢復

　　1) 安裝好mysql後，檢查開啟binlog

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging ：上面提示說明沒有伺服器開啟binlog

　　修改/etc/my.cnf

　　在mysqld選項中新增一行內容如下:

　　log-bin=mysql-bin

　　預設如果不給值的話，log-bin 的會以mysqld-bin 為索引，建立mysqld-bin.00001等

　　重啟mysqld即可。

　　2) 檢查下binlog

?

　　3) 先建立一些原始資料。

?

123456789 mysql> create database Test_DB;Query OK, 1 row affected (0.00 sec)mysql> use Test_DB;Database changedmysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10));Query OK, 0 rows affected (0.00 sec)mysql> insert into OneTb values (1,'user1',18);mysql> insert into OneTb values (2,'user2',19);insert into OneTb values (3,'user3',20);

　　檢查下資料：

?

123456789 mysql> select * from OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 |+----+-------+------+3 rows in set (0.00 sec)

　　4) 備份還原 (完整備份以及還原)

　　這裡我們模擬一下做下每天的完整備份資料庫任務。

?

12	`[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sqlEnter` `password:`

　　模擬下操作失誤,將資料修改錯誤了。

?

123456789101112 mysql> update OneTb set age = 15;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 15 || 2 | user2 | 15 || 3 | user3 | 15 |+----+-------+------+3 rows in set (0.00 sec)

　　現在我們使用傳統的方式來進行恢復還原。

?

1	`[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16:50.sql`

再次查詢一下：

?

123456789 mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 |+----+-------+------+3 rows in set (0.00 sec)

　　可以看到資料都已經還原回來。

　　5) 利用binlog模擬還原

　　在原表的基礎上在建立幾條資料。

?

123456789101112131415 mysql> insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 || 4 | user4 | 21 || 5 | user5 | 22 || 6 | user6 | 23 |+----+-------+------+6 rows in set (0.00 sec)

　　如果這個時候我們把資料不小心修改了或者把庫刪除掉了，導致資料全部丟失，這個時候如果再用之前最新的備份檔案 Test_DB_0809-16:50.sql，去恢復資料的話，那麼將會丟掉備份之後新插入的資料。

　　注意：如果真的使用最近的一次備份檔案去做的話，一定是在萬不得已的情況（比如binlog 被刪除，整個硬碟掛掉、、、想想都可怕。。。）。

　　模擬誤操作，批次更改下使用者的名字。

?

123	`mysql>` `update` `Test_DB.OneTb` `set` `name='user10';Query OK, 6` `rows` `affected (0.00 sec)Rows` `matched: 6 Changed: 6 Warnings: 0`

　　不行，上一步不夠狠，這裡再狠一點，把表都給刪除

?

123456 mysql> drop table Test_DB.OneTb;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 3Current database: *** NONE ***Query OK, 0 rows affected (0.00 sec)

　　由於之前我們一開始開啟了binlog 日誌選項，用binlog恢復資料庫。下面從binlog入手，先檢查一下binlog 檔案，目前我的mysql 服務自開啟binlog 後重啟了兩次，所以有2個binlog檔案（每重啟一次，便會重新生成一個binlog檔案，還有一種情況就是執行了FLUSH LOGS命令也會重建一個）;

　　mysql-bin.index 檔案中記錄的是：自log-bin選項開啟後，記錄的所有的二進位制日誌清單列表。

　　注意：在實際生產環境中，如果遇到需要恢復資料庫的情況，不要讓使用者能訪問到資料庫，以避免新的資料插入進來，以及在主從的環境下，關閉主從。

　　使用mysqlbinlog 命令可以檢視binlog檔案.我們看下最新的檔案mysql-bin.00002

　　從最後可以看出有刪除的操作。但是我們不能完全的恢復，因為最後還有刪除的操作。

　　現在我的思路就是，先將第一個binlog 和第二個binlog 檔案匯出來à利用指定的position位置的方式（過濾掉刪除表操作和update Test_DB.OneTb set name='user10';這條語句），匯出2個sql 語句，最後我們將2個sql 合成一個sql，匯入到資料庫中即可。

　　我們先用mysqlbinlog命令找到update 那條語句的位置，然後指定position 將mysql-bin.00001 匯出來。

?

123456789101112131415161718192021 [root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001….#160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0SET TIMESTAMP=1470733768;SET @@session.foreign_key_checks=1, @@session.unique_checks=1;SET @@session.sql_mode=0;;SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8;insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23);# at 2698#160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0SET TIMESTAMP=1470734389;update Test_DB.OneTb set name='user10';# at 2795#160809 5:30:38 server id 1 end_log_pos 2814 StopDELIMITER ;# End of log fileROLLBACK ;;

　　從上面可以看到我們在做插入正常資料後的position 是2698，那麼使用下面的命令匯出sql

?

1	`[root@hcloud ~]# mysqlbinlog` `--stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql`

　　然後匯出mysql-bin.00002的sql 語句（注：由於演示操作，該檔案只有一個drop 表操作，所以不做處理，但是在實際環境中，由於中途可能會有重啟資料庫操作，那時就需要檢測最新的binlog有沒有業務需要的語句。）

　　sql 語句已經匯出來了。我們可以利用該語句直接恢復所有正常的資料。

　　注：本次恢復沒有利用到之前的完整備份，因為我是開啟binlog後，然後才做的所有建庫建表操作，第一個binlog檔案裡已經記錄了所有的資料庫操作，所以不需要使用之前的完整備份（另外：實際的生產環境，還是需要利用到完整備份的，因為線上環境可能會有N多個binlog檔案，所以需要利用到完整備份和最新的binlog檔案來結合恢復）

　　開始恢復前，我們將原有的Test_DB資料庫也給幹掉吧。畢竟我們的binlog中有建立操作

?

12	`mysql>` `DROP` `DATABASE` `Test_DB;Query OK, 0` `rows` `affected (0.03 sec)`

　　恢復資料庫時還可以利用在登陸mysql 後，用source 命令匯入sql語句，這裡暫不介紹

?

1	`[root@hcloud ~]# mysql -uroot -p < Backup_1.sql`

Enter password:

　　恢復完成後，我們檢查下表的資料是否完整

?

123456789101112131415161718192021 mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || Test_DB || mysql |+--------------------+3 rows in set (0.00 sec)mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 || 4 | user4 | 21 || 5 | user5 | 22 || 6 | user6 | 23 |+----+-------+------+6 rows in set (0.00 sec)

　　Ok完整的都恢復過來了。

三、總結

　　1) 恢復方式

　　　　a) 利用最新一次的完整備份加binlog 指定事件起始時間和終止時間或者position恢復資料庫

　　　　b) 利用所有binlog指定事件起始位置和終止時間來合併sql檔案恢復資料庫（此方法要確保binlog檔案的完整）

　　　　c) 利用mysqldump 使用完整恢復。（在確保最新一次的完整備份後的資料不重要，允許丟掉的情況下，直接恢復。該方法最簡單、效率最高）

　　2) 附:官方建議的備份原則（為了能睡個好覺….嗯，是的）

　　　　a) 在mysql安裝好並執行時，就始終開啟 log-bin選項，該日誌檔案位於datadir目錄下，也要確保該目錄所在儲存介質是安全的。

　　　　b) 定期做完整的mysql 備份。

　　　　c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs ，該操作會關閉當前的二進位制日誌檔案，並新建一個binlog日誌檔案。(和重啟mysql後新建的binlog操作一樣)。以備份binlog日誌，利用binlog日誌也可以做增量備份。

以上所述是小編給大家介紹的Linux上透過binlog檔案恢復mysql資料庫詳細步驟，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回覆大家的。在此也非常感謝大家對指令碼之家網站的支援！

Linux上透過binlog檔案恢復mysql資料庫詳細步驟

相關文章