MySQL運維之二進位日誌

MySQL二進位日誌裡保存會造成或可能造成資料變化的SQL語句。透過二進位日誌可以完成如即時異地災難備份、讀寫分離、資料復原等功能。下面，我們一起來看看Mysql二進位日誌。

開啟bin-log日誌

Mysql預設是沒有開啟bin-log日誌的，需要我們自己去新增設定。

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

log-bin 設定了該項目後，即表示開啟二進位日誌功能。 mysql-bin是bin-log日誌檔名。

expire_logs_days = 10 表示只存放最近10天的bin-log日誌。

一般bin-log日誌存放在mysql安裝路徑/var/下面

運維提示：二進位日誌檔案與資料庫資料檔案最好不要放在同一塊硬碟上，如果存放資料檔案的硬碟壞了，可以用另一塊硬碟的二進位日誌來還原資料

#幾個有用的指令

• flush logs:產生新的bin-log日誌

• #show master status：查看最後一個bin-log日誌狀態。

• reset master:清空所有bin-log檔

#mysql > show master status

#Mysql日誌的檢視

因為日誌是二進位日誌，所以用一般的指令cat或vim查看會是一推亂碼。 Mysql為我們提供了工具mysqlbinlog。用它就可以來查看了。

./mysqlbinlog ../var/mysql-bin.000015
……
# at 123
#200601  8:35:19 server id 1  end_log_pos 154 CRC32 0xd25b404e  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
……

• at:sql開始時pos節點

• server_id：資料庫主機的服務號碼；

• end_log_pos 154：sql結束時的pos節點

mysqlbinlog常見的選項有以下幾個：

• --start-datetime：從二進位日誌中讀取指定等於時間戳記或晚於本機的時間

• --stop-datetime：從二進位日誌中讀取指定小於時間戳記或等於本機電腦的時間取值和上述一樣

• --start-position：從二進位日誌中讀取指定position 事件位置作為開始。

• --stop-position：從二進位日誌中讀取指定position 事件位置為事件截至

• -d,--database= name：只查看指定資料庫的日誌操作

使用bin-log日誌來恢復資料

• 匯出sql文件指令：mysqldump 資料庫名字[資料表名字1[ 資料表名字2...]] > 外部檔案目錄(建議使用.sql) 

• sql檔案匯入資料庫：mysql - u** -p**  資料庫名稱

現在模擬一種場景：一資料庫每晚3點定時備份,隔天網站正常運作了半天，突然在下午5點的時候，程式設計師小A不小心，DELETE 時候沒有加WHERE 條件，然後其中某張表資料全沒了。然後小A找到技術總監大聖，讓大聖幫忙恢復資料。

binlog_test 資料庫只有一張user表

凌晨三點時沒備份前的資料如下：

+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

凌晨3點到了，備份資料

mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql
ll /root/sql_backup/
总用量 4
-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql
=======数据备份完成=========

網站正常運行一段時間，有許多用戶註冊

INSERT INTO `user` (username) values('user1'),('user2'),('user3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============新增了3个用户user1 user2 及user3==============

到了下午5點鐘,小A開始犯傻了

DELETE FROM user;
Query OK, 6 rows affected (0.00 sec)
=========没where条件，数据全没了===========

小A找到大聖幫忙恢復數據,大聖先把昨晚凌晨三點資料給恢復了

service nginx stop;  # 大圣先关闭了nginx，使网站用户暂时访问不了数据库
Stoping nginx...  done 
MariaDB [binlog_test]> flush logs;  #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1536 |              |                  |
+------------------+----------+--------------+------------------+
mysql -v -f binlog_test < /root/sql_backup/20180706.sql

這時候大聖已經把昨晚凌晨3點時候資料恢復了

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+
=============昨晚凌晨三点数据恢复完成===============

接下來恢復凌晨三點到DELETE間段資料

先找到delete的pos點,備份之後log是000002 刪除之後也flush logs為000003，所以只要找000002 delete之前的pos即可

# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >
'mysql-bin.000002' >
| mysql binlog_test;

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============数据都回来了========================

以上是MySQL運維之二進位日誌的詳細內容。更多資訊請關注PHP中文網其他相關文章！