一乖仇、二進制日志及其作用
定義：
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é)構變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表斥难，那么該表的每一條記錄的變更都會被記錄到日志中枝嘶。
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）寫入的時間也不同债鸡，二進制日志文件是在事務提交前進行記錄的江滨，而在事務進行的過程中，不斷有重做日志條目被寫入重做日志文件中厌均。
