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)
[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 個數
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*/;
內容解析：
位置> 位於檔案中的位置，“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
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)
[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 個數
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*/;
內容解析：
位置> 位於檔案中的位置，“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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2140055/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql之 binlog維護詳細解析（開啟、binlog相關引數作用、mysqlbinlog解讀、binlog刪除）MySql
- mysql binlog 刪除master- binlogMySqlAST
- MySQL Binlog 解析工具 Maxwell 詳解MySql
- mysql binlog日誌刪除MySql
- mysql binlog詳細介紹MySql
- 使用mysqlbinlog備份binlog 搭建Binlog ServerMySqlServer
- MySQL binlog_ignore_db 引數最全解析MySql
- mysqlbinlog檢視binlogMySql
- MySQL binlog日期解析MySql
- MySQL 正確刪除 binlog 日誌MySql
- [MySQL binlog]徹底解析Mixed日誌格式的binlogMySql
- MySQL通過Binlog恢復刪除的表MySql
- MySQL binlog超過binlog_expire_logs_seconds閾值沒有刪除案例MySql
- mysql的sync_binlog引數實驗MySql
- mysql檢視binlog日誌詳解MySql
- 解析MYSQL BINLOG二進位制格式(9)--infobin解析binlog幫助文件MySql
- MySQL資料庫binlog解析神器-binlog2sql應用MySql資料庫
- 如何安全刪除mysql下的binlog日誌薦MySql
- MySQL：Redo & binlogMySql
- mysql binlog管理MySql
- 【工具】批量刪除binlog 的指令碼指令碼
- 3分鐘部署mysql並開啟binlogMySql
- mysql binlog日誌自動清理及手動刪除MySql
- mysql binlog_do_db引數設定的坑MySql
- “mysqlbinlog”工具做binlog server靠譜嗎？MySqlServer
- Mysql的binlog原理MySql
- MySQL Binlog 介紹MySql
- MySql Binlog 初識MySql
- mysql的binlog格式MySql
- 【Mysql】從binlog中找出單個表的binlog資訊MySql
- MySQL使用binlog2sql閃回誤刪除資料MySql
- MySQL系列：binlog日誌詳解（引數、操作、GTID、最佳化、故障演練）MySql
- MySQL運維之binlog_gtid_simple_recovery(GTID)MySql運維
- MySQL閃回技術之binlog2sql恢復binlog中的SQLMySql
- 十一：引數binlog_row_image（筆記）筆記
- MySQL 的日誌：binlogMySql
- mysql清除binlog日誌MySql
- MySQL的binlog日誌MySql