mysql之 binlog維護詳細(xì)解析（開啟、binlog參數(shù)作用、mysqlbinlog解讀、binlog刪除）

binary log 作用：主要實現(xiàn)三個重要的功能：用于復(fù)制，用于恢復(fù)，用于審計。
binary log 相關(guān)參數(shù)：
log_bin
設(shè)置此參數(shù)表示啟用binlog功能，并指定路徑名稱
log_bin_index
設(shè)置此參數(shù)是指定二進制索引文件的路徑與名稱
binlog_format
此參數(shù)控制二進制日志三種格式：STATEMENT，ROW，MIXED
① STATEMENT模式（SBR）
每一條會修改數(shù)據(jù)的sql語句會記錄到binlog中。優(yōu)點是并不需要記錄每一條sql語句和每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點是在某些情況（如非確定函數(shù)）下會導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會出現(xiàn)問題)
② ROW模式（RBR）
不記錄每條sql語句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。而且不會出現(xiàn)某些特定情況下的存儲過程、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點是會產(chǎn)生大量的日志，尤其是alter table的時候會讓日志暴漲。
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式。
binlog_row_image
此參數(shù)控制二進制日志記錄內(nèi)容，有三種選擇full、minimal、noblob，默認(rèn)值是full。
full：在“before”和“after”影像中，記錄所有的列值；
minimal：在“before”和“after”影像中，僅僅記錄被更改的以及能夠唯一識別數(shù)據(jù)行的列值；
noblob：在“before”和“after”影像中，記錄所有的列值，但是BLOB 與 TEXT列除外（如未更改）。
binlog_do_db
此參數(shù)表示只記錄指定數(shù)據(jù)庫的二進制日志
binlog_ignore_db
此參數(shù)表示不記錄指定的數(shù)據(jù)庫的二進制日志
max_binlog_cache_size
此參數(shù)表示binlog使用的內(nèi)存最大的尺寸
binlog_cache_size
此參數(shù)表示binlog使用的內(nèi)存大小，可以通過狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來幫助測試。
       binlog_cache_use：使用二進制日志緩存的事務(wù)數(shù)量
       binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時文件來保存事務(wù)中的語句的事務(wù)數(shù)量
max_binlog_size
Binlog最大值，最大和默認(rèn)值是1GB，該設(shè)置并不能嚴(yán)格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務(wù)時，為了保證事務(wù)的完整性，不可能做切換日志的動作，只能將該事務(wù)的所有SQL都記錄進當(dāng)前日志，直到事務(wù)結(jié)束
sync_binlog
這個參數(shù)直接影響mysql的性能和完整性
sync_binlog=0：
當(dāng)事務(wù)提交后，Mysql僅僅是將binlog_cache中的數(shù)據(jù)寫入Binlog文件，但不執(zhí)行fsync之類的磁盤        同步指令通知文件系統(tǒng)將緩存刷新到磁盤，而讓Filesystem自行決定什么時候來做同步，這個是性能最好的。
sync_binlog=n，在進行n次事務(wù)提交以后，Mysql將執(zhí)行一次fsync之類的磁盤同步指令，同志文件系統(tǒng)將Binlog文件緩存刷新到磁盤。
Mysql中默認(rèn)的設(shè)置是sync_binlog=0，即不作任何強制性的磁盤刷新指令，這時性能是最好的，但風(fēng)險也是最大的。一旦系統(tǒng)繃Crash，在文件系統(tǒng)緩存中的所有Binlog信息都會丟失


1.開啟二進制日志  
mysql>show variables like '%log_bin%';  
+---------------------------------+-------+  
| Variable_name                   | Value |  
+---------------------------------+-------+  
| log_bin                         | OFF   | --該參數(shù)用于設(shè)定是否啟用二進制日志  
| log_bin_trust_function_creators | OFF   |  
| sql_log_bin                     | ON    |  
+---------------------------------+-------+  
[root@mysql ~]# service mysql stop
Shutting down MySQL....                                    [  OK  ]
[root@mysql ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql ~]# vi /etc/my.cnf
說明: 在/etc/my.cnf 文件中添加 log_bin=/var/lib/mysql/binarylog/binlog 
[root@mysql ~]#  mkdir -p /var/lib/mysql/binarylog
[root@mysql ~]# chown -R mysql:mysql /var/lib/mysql/binarylog 
[root@mysql mysql]# service mysql start
Starting MySQL.                                            [  OK  ]
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /var/lib/mysql/binarylog/binlog       |
| log_bin_index                   | /var/lib/mysql/binarylog/binlog.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)
[root@mysql mysql]# ll /var/lib/mysql/binarylog/
total 8
-rw-rw---- 1 mysql mysql 120 May 30 16:57 binlog.000001
-rw-rw---- 1 mysql mysql  39 May 30 16:57 binlog.index
2. 切換二進制日志
mysql> flush logs;  
Query OK, 0 rows affected (0.05 sec)
[root@mysql mysql]# ll /var/lib/mysql/binarylog/
total 12
-rw-rw---- 1 mysql mysql 164 May 30 17:09 binlog.000001
-rw-rw---- 1 mysql mysql 120 May 30 17:09 binlog.000002
-rw-rw---- 1 mysql mysql  78 May 30 17:09 binlog.index
3.查看 binary log 個數(shù)
mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       164 |
| binlog.000002 |       164 |
4. 查看正在使用的 binary log
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000010 |      120 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
5.查看二進制日志事件
5.1 
mysql> show binlog events in 'binlog.000010';
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000010 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.25-log, Binlog ver: 4 |
| binlog.000010 | 120 | Query       |         1 |         219 | use `test`; create table andy(id int) |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec) 
5.2
mysql>  show binlog events in 'binlog.000010' from 120 limit 2; 
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type | Server_id | End_log_pos | Info                                  |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| binlog.000010 | 120 | Query      |         1 |         219 | use `test`; create table andy(id int) |
| binlog.000010 | 219 | Query      |         1 |         298 | BEGIN                                 |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
6. 用 mysqlbinlog 工具查看 二進制日志
6.1
[root@mysql ~]# mysqlbinlog /var/lib/mysql/binarylog/binlog.000016
# at 199
#170530 19:42:52 server id 1  end_log_pos 306 CRC32 0x64d982e4 
Query thread_id= exec_time=0
error_code=0
use `test`/*!*/;
SET TIMESTAMP=1496144572/*!*/;
insert into name values('陶葉')
/*!*/;
# at 306
#170530 19:42:52 server id 1  end_log_pos 337 CRC32 0xf75d46d1 
Xid = 36
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
內(nèi)容解析：
位置>          位于文件中的位置，“at 199”說明“事件”的起點，是以第199字節(jié)開始；“end_log_pos 306”說明以第306字節(jié)結(jié)束，下一個事件將以上一個事件結(jié)束位置為起點，周而復(fù)始。
時間戳>        事件發(fā)生的時間戳：“170530 19:42:52”
事件執(zhí)行時間>  事件執(zhí)行花費的時間:"exec_time=0"
錯誤碼>        錯誤碼為：“error_code=0”
服務(wù)器的標(biāo)識>  服務(wù)器的標(biāo)識id：“server id 1”
6.2 用 mysqlbinlog 工具查看 指定時間戳 binlog
[root@mysql ~]# mysqlbinlog --start-datetime="2017-05-30 19:42:52"  /var/lib/mysql/binarylog/binlog.000016
6.3 用 mysqlbinlog 工具查看 指定position 的binlog
[root@mysql ~]# mysqlbinlog --start-position=199  --stop-position=306  /var/lib/mysql/binarylog/binlog.000016
7. 刪除 binary log
7.1 自動刪除 ， my.cnf中 添加 expire_logs_days
expire_logs_days = X     # X為指定天數(shù)
7.2 手動刪除（ 自動在操作系統(tǒng)層面把 os file 刪除了）
mysql> reset master;   //刪除master的binlog
mysql> reset slave;    //刪除slave的中繼日志
mysql> purge master logs before '2017-05-30 18:27:00';  //刪除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000011'; //刪除binlog.000011之前的，不包含binlog.000011