mysql之 binlog維護詳細解析（開啟、binlog相關參數作用、mysqlbinlog解讀、binlog删除）

binary log 作用：主要實作三個重要的功能：用于複制，用于恢複，用于審計。

binary log 相關參數：

log_bin

設定此參數表示啟用binlog功能，并指定路徑名稱

log_bin_index

設定此參數是指定二進制索引檔案的路徑與名稱

binlog_format

此參數控制二進制日志三種格式：STATEMENT，ROW，MIXED

① STATEMENT模式（SBR）

每一條會修改資料的sql語句會記錄到binlog中。優點是并不需要記錄每一條sql語句和每一行的資料變化，減少了binlog日志量，節約IO，提高性能。缺點是在某些情況（如非确定函數）下會導緻master-slave中的資料不一緻(如sleep()函數， last_insert_id()，以及user-defined functions(udf)等會出現問題)

② ROW模式（RBR）

不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。而且不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正确複制的問題。缺點是會産生大量的日志，尤其是alter table的時候會讓日志暴漲。

③ MIXED模式（MBR）

以上兩種模式的混合使用，一般的複制使用STATEMENT模式儲存binlog，對于STATEMENT模式無法複制的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇日志儲存方式。

binlog_row_image

此參數控制二進制日志記錄内容，有三種選擇full、minimal、noblob，預設值是full。

full：在“before”和“after”影像中，記錄所有的列值；

minimal：在“before”和“after”影像中，僅僅記錄被更改的以及能夠唯一識别資料行的列值；

noblob：在“before”和“after”影像中，記錄所有的列值，但是BLOB 與 TEXT列除外（如未更改）。

binlog_do_db

此參數表示隻記錄指定資料庫的二進制日志

binlog_ignore_db

此參數表示不記錄指定的資料庫的二進制日志

max_binlog_cache_size

此參數表示binlog使用的記憶體最大的尺寸

binlog_cache_size

此參數表示binlog使用的記憶體大小，可以通過狀态變量binlog_cache_use和binlog_cache_disk_use來幫助測試。

binlog_cache_use：使用二進制日志緩存的事務數量

binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時檔案來儲存事務中的語句的事務數量

max_binlog_size

Binlog最大值，最大和預設值是1GB，該設定并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，隻能将該事務的所有SQL都記錄進目前日志，直到事務結束

sync_binlog

這個參數直接影響mysql的性能和完整性

sync_binlog=0：

當事務送出後，Mysql僅僅是将binlog_cache中的資料寫入Binlog檔案，但不執行fsync之類的磁盤 同步指令通知檔案系統将緩存重新整理到磁盤，而讓Filesystem自行決定什麼時候來做同步，這個是性能最好的。

sync_binlog=n，在進行n次事務送出以後，Mysql将執行一次fsync之類的磁盤同步指令，同志檔案系統将Binlog檔案緩存重新整理到磁盤。

Mysql中預設的設定是sync_binlog=0，即不作任何強制性的磁盤重新整理指令，這時性能是最好的，但風險也是最大的。一旦系統繃Crash，在檔案系統緩存中的所有Binlog資訊都會丢失

1.開啟二進制日志 

mysql>show variables like '%log_bin%'; 

+---------------------------------+-------+ 

| Variable_name | Value | 

| log_bin | OFF | --該參數用于設定是否啟用二進制日志 

| 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)

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 個數

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; 

+---------------+-----+------------+-----------+-------------+---------------------------------------+

| 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*/;

内容解析：

位置> 位于檔案中的位置，“at 199”說明“事件”的起點，是以第199位元組開始；“end_log_pos 306”說明以第306位元組結束，下一個事件将以上一個事件結束位置為起點，周而複始。

時間戳> 事件發生的時間戳：“170530 19:42:52”

事件執行時間> 事件執行花費的時間:"exec_time=0"

錯誤碼> 錯誤碼為：“error_code=0”

伺服器的辨別> 伺服器的辨別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為指定天數

7.2 手動删除（ 自動在作業系統層面把 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

文章可以轉載，必須以連結形式标明出處。

本文轉自 張沖andy 部落格園部落格，原文連結：  http://www.cnblogs.com/andy6/p/6921308.html ，如需轉載請自行聯系原作者