mysql中relay log和binlog有什么用

這篇文章主要為大家展示了“mysql中relay log和binlog有什么用”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“mysql中relay log和binlog有什么用”這篇文章吧。

突然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有什么用”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！