MySQL運維之二進制日志

276

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運維之二進制日志

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]&gt;?flush?logs;??#生成新的binlog日志 MariaDB?[binlog_test]&gt;?show?master?status; +------------------+----------+--------------+------------------+ |?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?| +------------------+----------+--------------+------------------+ |?mysql-bin.000003?|?????1536?|??????????????|??????????????????| +------------------+----------+--------------+------------------+ mysql?-v?-f?binlog_test?<p>這時候大圣已經把昨晚凌晨3點時候數據恢復了</p><pre class="brush:sql;toolbar:false">MariaDB?[binlog_test]&gt;?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??&gt; 'mysql-bin.000002'?&gt; |?mysql?binlog_test;  MariaDB?[binlog_test]&gt;?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?| +---------+----------+---------------------+ ==============數據都回來了========================

? 版權聲明
THE END
數據庫
# 數據庫# mysql# sql# 事件# var# delete# database# vim# position
喜歡就支持一下吧
點贊6 分享