一、二進制日志及其作用

定義：

MySQL的二進制日志記錄的是所有使mysql數(shù)據(jù)庫的數(shù)據(jù)發(fā)生變更的操作信息（事件），即記錄用戶對數(shù)據(jù)庫執(zhí)行更改的所有sql語句。

1、數(shù)據(jù)庫的復制

配置了主從復制的時候，主服務器（Master）會將其產(chǎn)生的二進制日志發(fā)送到從服務器（Slave），從服務器會利用這個二進制日志的信息在本地重做，實現(xiàn)主從同步。

2、數(shù)據(jù)庫的恢復

MySQL可以在全備和差異備份的基礎上，利用二進制日志進行基于時間點或者事物Id的恢復操作，原理等同于主從復制的日志重做。

3、分析數(shù)據(jù)庫發(fā)生的變更（審計）

基于二進制日志本身的特征，即記錄數(shù)據(jù)庫發(fā)生變更的操作，從而可以通過分析特定時間段的二進制日志，來分析某一時間內(nèi)對數(shù)據(jù)庫的操作，或者查詢一些變更發(fā)生的時間，判斷是否有對數(shù)據(jù)庫進行注入的攻擊。

二、二進制日志的配置

在MySQL配置文件my.cnf中[mysqld] 選項處添加二進制日志的配置參數(shù)：

log-bin=mysql-bin

重啟MySQL服務生效，注意該變量是只讀的，不能動態(tài)修改。

log_bin是生成的bin-log的文件名，未指定位置，默認為MySQL數(shù)據(jù)目錄，文件后綴則是6位數(shù)字的編碼，從000001開始。按照上面的配置，生成的文件為： mysql_bin.000001、mysql_bin.000002......

三、二進制日志相關的變量

1、常用binlog相關變量

查看部分二進制日志相關變量的命令：

show global variables like '%binlog%';

max_binlog_size：單個二進制日志文件的最大值，如果超過該值，則生成一個新的二進制日志文件，后綴名加1，并記錄到.index文件。

binlog_cache_size：二進制日志緩存大小，當使用事務的表存儲引擎（如InnoDB）時，所有未提交的二進制日志會提交到一個緩存中，當該事務提交時，直接將緩存中的二進制日志寫入到二進制日志文件，需要主要的是該變量是基于會話（session）的，所有該值的設置需要謹慎。可以通過show global status命令查看binlog_cache_use、binlog_cache_disk_use的狀態(tài)，來判斷當前binlog_cache_size的設置是否合適，其中binlog_cache_use記錄了使用緩存寫入二進制日志的次數(shù)，binlog_cache_disk_use記錄了使用臨時文件寫入二進制日志的次數(shù)。

sync_binlog：二進制日志每寫緩沖多少次就同步到磁盤，默認為0，表示使用操作系統(tǒng)的緩沖來寫二進制日志；如果為1，表示采用同步寫磁盤的方式來寫二進制日志，該方式能提高數(shù)據(jù)庫的高可用性，但是會對數(shù)據(jù)庫的IO性能帶來影響。

binlog_format：記錄二進制日志的格式，非常重要，它是動態(tài)參數(shù)，可以在數(shù)據(jù)庫運行環(huán)境下進行更改，但注意更改的時候可能會引起復制出現(xiàn)問題，需要謹慎操作并更改后觀察復制是否正常。

主要有三種格式：

1）STATEMENT：二進制日志文件記錄的是邏輯SQL語句

優(yōu)點：在 STATEMENT 模式下，不需要記錄每一行數(shù)據(jù)的變化，減少了 binlog 日志量，節(jié)省 I/O 以及存儲資源，提高性能。

缺點：在 STATEMENT 模式下，不是所有的 UPDATE 語句都能被復制。目前已經(jīng)發(fā)現(xiàn)的就有不少情況會造成 MySQL 的復制出現(xiàn)問題，主要是修改數(shù)據(jù)的時候使用了某些特定的函數(shù)或者功能的時候會出現(xiàn)，比如：sleep() 函數(shù)在有些版本中就不能被正確復制，在存儲過程中使用了 last_insert_id() 函數(shù)，可能會使 slave 和 master 上得到不一致的 id等。

2）ROW：二進制日志記錄的是表的行更改情況

優(yōu)點：在 ROW 模式下，binlog 中可以不記錄執(zhí)行的 SQL 語句的上下文相關的信息，僅僅只需要記錄哪一條記錄被修改了，修改成什么樣了。所以 ROW 的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)，非常容易理解，而且不會出現(xiàn)某些特定情況下的存儲過程或 function ，以及 trigger 的調(diào)用和觸發(fā)無法被正確復制的問題，為數(shù)據(jù)庫的恢復和復制帶來了更好的可靠性。

缺點：在 ROW 模式下，所有的執(zhí)行的語句記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容，尤其是當執(zhí)行 alter table 之類的語句的時候，產(chǎn)生的日志量是驚人的。因為 MySQL 對于 alter table 之類的表結(jié)構(gòu)變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表，那么該表的每一條記錄的變更都會被記錄到日志中。

3）MIXED：默認采用STATEMENT格式進行二進制日志文件的記錄，在一些特定情況下會使用ROW格式，可能的情況有：

a.表的存儲引擎為NDB；

b.使用的UUID()、USER()、CURRENT_USER()、NOW()等不確定的函數(shù)；

c.使用了用戶自定義函數(shù)（UDF）;

d.使用了INSERT DELAY語句；

e.使用了臨時表。

expire_logs_days：二進制日志的過期時間，超過該時間的日志文件會自動刪除。

max_binlog_cache_size：二進制日志能夠使用的最大cache內(nèi)存大小。當執(zhí)行多語句事務時，max_binlog_cache_size 如果不夠大，系統(tǒng)可能會報出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的錯誤。

max_binlog_stmt_cache_size：針對非事務語句。

binlog_checksum?：用作復制的主從校檢， NONE表示不生成checksum，CRC-32表示使用這個算法做校檢。

log_bin_trust_function_creators：默認為OFF，這個參數(shù)開啟會限制存儲過程、函數(shù)以及觸發(fā)器的創(chuàng)建。

四、二進制日志的管理

1、查看所有的日志文件

MariaDB [(none)]> show binary logs;

或者

MariaDB [(none)]> show master logs;

2、查看正在寫入的日志文件

MariaDB [(none)]> show master status;

3、查看當前binlog文件內(nèi)容

MariaDB [(none)]> show binlog events;

或者

MariaDB [(none)]> show binlog events in 'mysql-bin.xxxxxx';

或者

MariaDB [(none)]> show binlog events in 'mysql-bin.xxxxxx' from xxx;

Log_name：此條log存在哪個文件中

Pos：log在bin-log中的開始位置

Event_type：log的類型信息

Server_id：可以查看配置中的server_id,表示log是哪個服務器產(chǎn)生

End_log_pos：log在bin-log中的結(jié)束位置

Info：log的一些備注信息，可以直觀的看出進行了什么操作

4、手動啟用新的日志文件,一般備份完數(shù)據(jù)庫后執(zhí)行

MariaDB [(none)]> flush logs;

5、手動刪除二進制日志

1）刪除指定fileName之前的日志文件

purge binary logs to fileName; ? ?

2）刪除指定時間之前的文件

purge binary logs before?'2017-12-31 23:59:59'; ? ?

3）刪除指定日志

purge binary logs before date_sub( now( ), interval 7 day);?

或者

purge master logs before date_sub( now( ), interval 7 day);?

4）刪除所有二進制日志，并重新開始記錄。

MariaDB [(none)]> reset master;

6、二進制日志文件導出及查看

1）按時間點導出

mysqlbinlog?--start-datetime="2018-01-01 00:00:00"?--stop-datetime="2018-01-02 00:00:00"?mysql-bin.000005 >?/data/tmp/date_20180101.log?

2）按事件位置點導出

mysqlbinlog?--start-position=1000 ?--stop-position=2000 mysql-bin.000005 >?/data/tmp/posi_1000-2000.log?

7、恢復部分數(shù)據(jù)

強烈建議：做任何恢復之前都給數(shù)據(jù)庫做一個完整備份，新建庫進行恢復。

?binlog是記錄著mysql所有事件的操作，可以通過binlog做完整恢復，基于時間點的恢復，以及基于位置點的恢復。

1）完整恢復，先執(zhí)行上次完整備份恢復，再執(zhí)行自上次備份后產(chǎn)生的二進制日志文件恢復。

mysqlbinlog?mysql-bin.000005 |?mysql?-uroot?-p

這樣數(shù)據(jù)庫就可以完全的恢復到崩潰前的完全狀態(tài)。

2）基于時間點的恢復，如果確認誤操作時間點為2018-01-20 10:00:00，執(zhí)行如下

mysqlbinlog?--stop-date='2018-01-20 9:59:59'?mysql-bin.000005 |?mysql?-uroot?-p

然后跳過誤操作的時間點，繼續(xù)執(zhí)行后面的binlog。

mysqlbinlog?--start-date='2018-01-20 10:01:00'?mysql-bin.000005 |?mysql?-uroot?-p

3）取兩個時間點之間的事件

mysqlbinlog?--start-datetime="2018-01-20 11:00:00"?--stop-datetime="2018-01-20 12:00:00"?mysql-bin.000001?|?mysql?-u?root?-p

4）基于位置點恢復

如果兩個時間點之間可能涉及到的不只是誤操作，也有可能有正確的操作也被跳過去了，那么可以執(zhí)行位置點恢復。

通過查看日志文件信息，確認1122-1133為誤操作點。

首先執(zhí)行從1開始至1122之間的事件，不包括位置點為1122的事件，

mysqlbinlog?--stop-position=1122 mysql-bin.000005 |?mysql?-uroot?-p?

然后執(zhí)行從1134開始的事件。

mysqlbinlog?--start-position=1134 mysql-bin.000005 |?mysql?-uroot?-p?

5）取兩個位置點之間的事件

mysqlbinlog?--start-position=1001 --stop-position=2000 mysql-bin.000001?|?mysql?-uroot?-p

五、思考：

1、開啟二進制日志影響性能嗎？

有一些性能損耗，但是性能開銷非常小（slightly slower），另外，開啟binlog帶來的好處要遠遠超過帶來的性能開銷。官方文檔的介紹如下所示：Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

2、二進制日志與重做日志的區(qū)別？

1）二進制日志是數(shù)據(jù)庫級別的文件，會記錄所有與mysql有關的日志記錄，包括InnoDB等其他存儲引擎的日志，主要用于恢復數(shù)據(jù)庫和建立集群；重做日志是InnoDB級別的文件，用來記錄Innodb存儲引擎本身的事務日志，主要用于數(shù)據(jù)恢復，保證事務的持久性和可靠性。

2）記錄的內(nèi)容不同，二進制日志文件記錄的都是關于一個事務的具體操作內(nèi)容，即邏輯變化情況；InnoDB存儲引擎的重做日志文件記錄的關于每個頁的更改的物理情況；

3）寫入的時間也不同，二進制日志文件是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日志條目被寫入重做日志文件中。