今天突然mysql資料庫的一個表的資料少了很多，我想著透過檢視日誌來判斷針對這個表到底做了那些操作，然後再有針對性的去恢復，可是直接看binlog(二進位制的形式)發現很亂，看不清楚，於是上網查詢得知，需要先透過mysqlbinlog進行格式化，具體如下：

主庫binlog ：mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000058 > binlog

從庫relay log：mysqlbinlog --base64-output=decode-rows -v -v mysql-relay-bin.000031 > relaylog

然後檢視變成很規整的形式：就是一些具體的sql語句。

[root@S243 mybinlog]# more binlog28

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#161125 22:17:23 server id 20 end_log_pos 120 Start: binlog v 4, server v 5.6.18-enterprise-commercial-advanced-log created 161125 22:17:23

# at 120

#161125 22:17:23 server id 20 end_log_pos 204 Query thread_id=3498328 exec_time=0 error_code=0

SET TIMESTAMP=1480083443/*!*/;

SET @@session.pseudo_thread_id=3498328/*!*/;

。

SET TIMESTAMP=1480083443/*!*/;

insert into mailer.khgz_coreseek_result (info_id, khgz_id, last_modify, result, status) values ('1', '1', '2016-11-25 22:17:24', '29803096:8d78ac82-e746-423a-9615-971485e8', 0)

/*!*/;

# at 487

#161125 22:17:23 server id 20 end_log_pos 572 Query thread_id=3498328 exec_time=0 error_code=0

SET TIMESTAMP=1480083443/*!*/;

COMMIT

/*!*/;

# at 572

關於binlog和relay log的rotate機制：

Binary Log rotate機制：

?Rotate：每一條binary log寫入完成後，都會判斷當前檔案是否超過 max_binlog_size(預設1g)，如果超過則自動生成一個binlog file

?Delete：expire-logs-days 只在例項啟動時和 flush logs 時以及檔案檔案是否超過 max_binlog_size時判斷是否有過期的binlog檔案，如果檔案訪問時間早於設定值，則purge file
Relay Log rotate 機制：

Rotate：每從Master fetch一個events後，判斷當前檔案是否超過 max_relay_log_size（預設1g) 如果超過則自動生成一個新的relay-log-file

Delete：purge-relay-log 在SQL Thread每執行完一個events時判斷，如果該relay-log 已經不再需要則自動刪除

Delete：expire-logs-days 只在例項啟動時和 flush logs 時判斷，如果檔案訪問時間早於設定值，則purge file （同Binlog file） (updated: expire-logs-days和relaylog的purge沒有關係)

關於binlog和relay log的引數：

binlog的相關引數：

mysql> show variables like '%bin%';

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

| Variable_name | Value |

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

| bind_address | * |

| binlog_cache_size | 1048576 |

| binlog_checksum | NONE |

| binlog_direct_non_transactional_updates | OFF |

| binlog_format | MIXED |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_image | FULL |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| innodb_api_enable_binlog | OFF |

| innodb_locks_unsafe_for_binlog | OFF |

| log_bin | ON |

| log_bin_basename | /mysql/mybinlog/mysql-bin |

| log_bin_index | /mysql/mybinlog/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| sql_log_bin | ON |

| sync_binlog | 0 |

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

22 rows in set (0.00 sec)

log_bin

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

log_bin_index

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

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

sync_binlog”：這個引數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的效能損耗，而且還影響到MySQL中資料的完整性。對於“sync_binlog”引數的各種設定的說明如下：

sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。

sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。

在MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟重新整理指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失。而當設定為“1”的時候，是最安全但是效能損耗最大的設定。因為當設定為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際資料沒有任何實質性影響。

從以往經驗和相關測試來看，對於高併發事務的系統來說，“sync_binlog”設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。

relay log的相關引數：

透過語句：show variables like '%relay%'，檢視先骨幹的relay的所有相關引數

mysql> show variables like '%relay%';

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

| Variable_name | Value |

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

| max_relay_log_size | 0 |

| relay_log | |

| relay_log_index | |

| relay_log_info_file | relay-log.info |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| sync_relay_log | 0 |

| sync_relay_log_info | 0 |

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

9 rows in set (0.08 sec)

2.1 max_relay_log_size：標記relay log 允許的最大值，如果該值為0，則預設值為max_binlog_size(1G)；如果不為0，則max_relay_log_size則為最大的relay_log檔案大小；

2.2 relay_log：定義relay_log的位置和名稱，如果值為空，則預設位置在資料檔案的目錄，檔名為host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；

2.3relay_log_index：同relay_log，定義relay_log的位置和名稱；

2.4relay_log_info_file：設定relay-log.info的位置和名稱（relay-log.info記錄MASTER的binary_log的恢復位置和relay_log的位置）

2.5relay_log_purge：是否自動清空不再需要中繼日誌時。預設值為1(啟用)。

2.6relay_log_recovery：當slave從庫當機後，假如relay-log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay-log，並且重新從master上獲取日誌，這樣就保證了relay-log的完整性。預設情況下該功能是關閉的，將relay_log_recovery的值設定為 1時，可在slave從庫上開啟該功能，建議開啟。

2.7relay_log_space_limit：防止中繼日誌寫滿磁碟，這裡設定中繼日誌最大限額。但此設定存在主庫崩潰，從庫中繼日誌不全的情況，不到萬不得已，不推薦使用；

2.8sync_relay_log：這個引數和sync_binlog是一樣的，當設定為1時，slave的I/O執行緒每次接收到master傳送過來的binlog日誌都要寫入系統緩衝區，然後刷入relay log中繼日誌裡，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁碟的大量I/O。當設定為0時，並不是馬上就刷入中繼日誌裡，而是由作業系統決定何時來寫入，雖然安全性降低了，但減少了大量的磁碟I/O操作。這個值預設是0，可動態修改，建議採用預設值。

2.9sync_relay_log_info：這個引數和sync_relay_log引數一樣，當設定為1時，slave的I/O執行緒每次接收到master傳送過來的binlog日誌都要寫入系統緩衝區，然後刷入relay-log.info裡，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁碟的大量I/O。當設定為0時，並不是馬上就刷入relay-log.info裡，而是由作業系統決定何時來寫入，雖然安全性降低了，但減少了大量的磁碟I/O操作。這個值預設是0，可動態修改，建議採用預設值。

關於binlog和relay log的刪除方法：

binlog 太多的解決方法如下：

PURGE MASTER LOGS手動刪除用法及示例,MASTER和BINARY是同義詞

> PURGE {MASTER | BINARY} LOGS TO 'log_name'

> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

例項：

> PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日誌

> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //清除2008-06-22 13:00:00前binlog日誌 > PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日誌BEFORE，變數的date自變數可以為'YYYY-MM-DD hh:mm:ss'格式。

或者直接：

進入資料庫，檢視一下當前使用的binlog日誌是哪個，除了這個以外的，其它都可以使用rm -rf 刪除！

relay log 可以透過修改一個引數自動刪除，前面已經介紹過引數：relay_log_purge=on 即可，自動清空不再需要中繼日誌。

總結：

mysql資料庫的binlog和relay log日誌有著舉足輕重的作用，並且relay log僅僅存在於mysql 的slave庫，它的作用就是記錄slave庫中的io程式接收的從主庫傳過來的binlog,然後等待slave庫的sql程式去讀取和應用，保證主從同步，但是binlog主庫和從庫（slave）都可以存在，記錄對資料發生或潛在發生更改的SQL語句，並以二進位制的形式儲存在磁碟，所以可以透過binlog來實時備份和恢復資料庫。

mysql relay log和binlog 小結

相關文章