一、binlog 介紹
伺服器的二進位日誌記錄著該資料庫的所有增刪改的操作日誌(前提是要在自己的伺服器上開啟binlog)，也包含了這些操作的執行時間。為了顯示這些二進位內容，我們可以使用mysqlbinlog指令來查看。
用途1：主從同步
用途2：恢復資料庫（也是線上出現一次資料庫檔案遺失後，才對這個有所了解並學習的）
mysqlbinlog指令用法：shell> mysqlbinlog [options] log_file ...
1) mysqlbinlog 選項範例
常見的選項有以下幾個：
--start-datetime
從二進位日誌中讀取指定等於時間戳記或晚於本機電腦的時間。取值如：="1470733768" 或="2016-08-09 5:09:28"
範例:
[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001 --stop-datetime
從二進位日誌中讀取指定小於時間戳記或等於本機電腦的時間 取值和上述一樣
--start-position
從二進位日誌中讀取指定position 事件位置作為開始。取值：="2698"
範例：
[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
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 106 | +------------------+-----------+ 1 row in set (0.00 sec)
3) 先建立一些原始資料。
mysql> create database Test_DB; Query OK, 1 row affected (0.00 sec) mysql> use Test_DB; Database changed mysql> 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);
檢查下數據：
mysql> select * from OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 18 | | 2 | user2 | 19 | | 3 | user3 | 20 | +----+-------+------+ 3 rows in set (0.00 sec)
4) 備份還原 (完整備份以及還原)
這裡我們模擬一下做下每天的完整備份資料庫任務。
[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sql Enter password:
模擬下操作失誤,將資料修改錯誤了。
mysql> update OneTb set age = 15; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 15 | | 2 | user2 | 15 | | 3 | user3 | 15 | +----+-------+------+ 3 rows in set (0.00 sec)
現在我們使用傳統的方式來進行還原還原。
[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql
再查詢一次：
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模擬還原
在原表的基礎上在建立幾個資料。
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: 0 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)
如果這個時候我們把資料不小心修改了或把庫刪除掉了，導致資料全部遺失，這時候如果再用之前最新的備份檔Test_DB_0809-16:50.sql，去恢復資料的話，那麼將會丟掉備份之後新插入的資料。
注意：如果真的使用最近的一次備份文件去做的話，一定是在萬不得已的情況（比如binlog 被刪除，整個硬碟掛掉、、、 想想都很可怕。。）。
模擬誤操作，批量更改下用戶的名字。
mysql> update Test_DB.OneTb set name='user10'; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0
不行，上一步不夠狠，這裡再狠一點，把手錶都給刪除
mysql> drop table Test_DB.OneTb; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current 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 导出来。
[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=0 SET TIMESTAMP=1470733768/*!*/; SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C latin1 *//*!*/; 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=0 SET TIMESTAMP=1470734389/*!*/; update Test_DB.OneTb set name='user10' /*!*/; # at 2795 #160809 5:30:38 server id 1 end_log_pos 2814 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
从上面可以看到我们在做插入正常数据后的position 是2698，那么使用下面的命令导出sql
[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中有创建操作
mysql> DROP DATABASE Test_DB; Query OK, 0 rows affected (0.03 sec)
恢复数据库时还可以利用在登陆mysql 后，用source 命令导入sql语句，这里暂不介绍
[root@hcloud ~]# mysql -uroot -p < Backup_1.sql
Enter password:
恢复完成后，我们检查下表的数据是否完整
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数据库详细步骤，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
