mysql中relay log和binlog有什么用

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

突然mysql數(shù)據(jù)庫(kù)的一個(gè)表的數(shù)據(jù)少了很多，我想著通過查看日志來判斷針對(duì)這個(gè)表到底做了那些操作，然后再有針對(duì)性的去恢復(fù)，可是直接看binlog(二進(jìn)制的形式)發(fā)現(xiàn)很亂，看不清楚，于是上網(wǎng)查詢得知，需要先通過mysqlbinlog進(jìn)行格式化，具體如下：

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

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

然后查看變成很規(guī)整的形式：  就是一些具體的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

關(guān)于binlog和relay log的rotate機(jī)制：

Binary Log rotate機(jī)制：

?Rotate：每一條binary log寫入完成后，都會(huì)判斷當(dāng)前文件是否超過 max_binlog_size(默認(rèn)1g)，如果超過則自動(dòng)生成一個(gè)binlog file

?Delete：expire-logs-days 只在 實(shí)例啟動(dòng)時(shí) 和 flush logs 時(shí)以及文件文件是否超過 max_binlog_size時(shí)判斷是否有過期的binlog文件，如果文件訪問時(shí)間早于設(shè)定值，則purge file
Relay Log rotate 機(jī)制：

Rotate：每從Master fetch一個(gè)events后，判斷當(dāng)前文件是否超過 max_relay_log_size（默認(rèn)1g) 如果超過則自動(dòng)生成一個(gè)新的relay-log-file

Delete：purge-relay-log 在SQL Thread每執(zhí)行完一個(gè)events時(shí)判斷，如果該relay-log 已經(jīng)不再需要?jiǎng)t自動(dòng)刪除

Delete：expire-logs-days 只在 實(shí)例啟動(dòng)時(shí) 和 flush logs 時(shí)判斷，如果文件訪問時(shí)間早于設(shè)定值，則purge file  （同Binlog file）  (updated: expire-logs-days和relaylog的purge沒有關(guān)系)

關(guān)于binlog和relay log的參數(shù)：

binlog的相關(guān)參數(shù)：

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

設(shè)置此參數(shù)表示啟用binlog功能，并指定路徑名稱

log_bin_index

設(shè)置此參數(shù)是指定二進(jìn)制索引文件的路徑與名稱

binlog_do_db

此參數(shù)表示只記錄指定數(shù)據(jù)庫(kù)的二進(jìn)制日志

binlog_ignore_db

此參數(shù)表示不記錄指定的數(shù)據(jù)庫(kù)的二進(jìn)制日志

max_binlog_cache_size

此參數(shù)表示binlog使用的內(nèi)存最大的尺寸

binlog_cache_size

此參數(shù)表示binlog使用的內(nèi)存大小，可以通過狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來幫助測(cè)試。

binlog_cache_use：使用二進(jìn)制日志緩存的事務(wù)數(shù)量

binlog_cache_disk_use:使用二進(jìn)制日志緩存但超過binlog_cache_size值并使用臨時(shí)文件來保存事務(wù)中的語句的事務(wù)數(shù)量

max_binlog_size

Binlog最大值，最大和默認(rèn)值是1GB，該設(shè)置并不能嚴(yán)格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個(gè)比較大事務(wù)時(shí)，為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束

sync_binlog

sync_binlog”：這個(gè)參數(shù)是對(duì)于MySQL系統(tǒng)來說是至關(guān)重要的，他不僅影響到Binlog對(duì)MySQL所帶來的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：

sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時(shí)候來做同步，或者cache滿了之后才同步到磁盤。

sync_binlog=n，當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。

在MySQL中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫到y(tǒng)Crash，在binlog_cache中的所有binlog信息都會(huì)被丟失。而當(dāng)設(shè)置為“1”的時(shí)候，是最安全但是性能損耗最大的設(shè)置。因?yàn)楫?dāng)設(shè)置為1的時(shí)候，即使系統(tǒng)Crash，也最多丟失binlog_cache中未完成的一個(gè)事務(wù)，對(duì)實(shí)際數(shù)據(jù)沒有任何實(shí)質(zhì)性影響。

從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來看，對(duì)于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達(dá)5倍甚至更多。

relay log的相關(guān)參數(shù)：

通過語句：show variables like '%relay%'，查看先骨干的relay的所有相關(guān)參數(shù)

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：標(biāo)記relay log 允許的最大值，如果該值為0，則默認(rèn)值為max_binlog_size(1G)；如果不為0，則max_relay_log_size則為最大的relay_log文件大??；

2.2 relay_log：定義relay_log的位置和名稱，如果值為空，則默認(rèn)位置在數(shù)據(jù)文件的目錄，文件名為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：設(shè)置relay-log.info的位置和名稱（relay-log.info記錄MASTER的binary_log的恢復(fù)位置和relay_log的位置）

2.5relay_log_purge：是否自動(dòng)清空不再需要中繼日志時(shí)。默認(rèn)值為1(啟用)。

2.6relay_log_recovery：當(dāng)slave從庫(kù)宕機(jī)后，假如relay-log損壞了，導(dǎo)致一部分中繼日志沒有處理，則自動(dòng)放棄所有未執(zhí)行的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性。默認(rèn)情況下該功能是關(guān)閉的，將relay_log_recovery的值設(shè)置為 1時(shí)，可在slave從庫(kù)上開啟該功能，建議開啟。

2.7relay_log_space_limit：防止中繼日志寫滿磁盤，這里設(shè)置中繼日志最大限額。但此設(shè)置存在主庫(kù)崩潰，從庫(kù)中繼日志不全的情況，不到萬不得已，不推薦使用；

2.8sync_relay_log：這個(gè)參數(shù)和sync_binlog是一樣的，當(dāng)設(shè)置為1時(shí)，slave的I/O線程每次接收到master發(fā)送過來的binlog日志都要寫入系統(tǒng)緩沖區(qū)，然后刷入relay log中繼日志里，這樣是最安全的，因?yàn)樵诒罎⒌臅r(shí)候，你最多會(huì)丟失一個(gè)事務(wù)，但會(huì)造成磁盤的大量I/O。當(dāng)設(shè)置為0時(shí)，并不是馬上就刷入中繼日志里，而是由操作系統(tǒng)決定何時(shí)來寫入，雖然安全性降低了，但減少了大量的磁盤I/O操作。這個(gè)值默認(rèn)是0，可動(dòng)態(tài)修改，建議采用默認(rèn)值。

2.9sync_relay_log_info：這個(gè)參數(shù)和sync_relay_log參數(shù)一樣，當(dāng)設(shè)置為1時(shí)，slave的I/O線程每次接收到master發(fā)送過來的binlog日志都要寫入系統(tǒng)緩沖區(qū)，然后刷入relay-log.info里，這樣是最安全的，因?yàn)樵诒罎⒌臅r(shí)候，你最多會(huì)丟失一個(gè)事務(wù)，但會(huì)造成磁盤的大量I/O。當(dāng)設(shè)置為0時(shí)，并不是馬上就刷入relay-log.info里，而是由操作系統(tǒng)決定何時(shí)來寫入，雖然安全性降低了，但減少了大量的磁盤I/O操作。這個(gè)值默認(rèn)是0，可動(dòng)態(tài)修改，建議采用默認(rèn)值。

關(guān)于binlog和relay log的刪除方法：

binlog 太多的 解決方法如下：

PURGE MASTER LOGS手動(dòng)刪除用法及示例,MASTER和BINARY是同義詞

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

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

實(shí)例：

> 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'格式。

或者直接：

進(jìn)入數(shù)據(jù)庫(kù)，查看一下當(dāng)前使用的binlog日志是哪個(gè)，除了這個(gè)以外的，其它都可以使用rm -rf 刪除！

relay log 可以通過修改一個(gè)參數(shù)自動(dòng)刪除，前面已經(jīng)介紹過參數(shù)：relay_log_purge=on 即可，自動(dòng)清空不再需要中繼日志。

總結(jié)：

mysql數(shù)據(jù)庫(kù)的binlog和relay log日志有著舉足輕重的作用，并且relay log僅僅存在于mysql 的slave庫(kù)，它的作用就是記錄slave庫(kù)中的io進(jìn)程接收的從主庫(kù)傳過來的binlog,然后等待slave庫(kù)的sql進(jìn)程去讀取和應(yīng)用，保證主從同步，但是binlog主庫(kù)和從庫(kù)（slave）都可以存在，記錄對(duì)數(shù)據(jù)發(fā)生或潛在發(fā)生更改的SQL語句，并以二進(jìn)制的形式保存在磁盤，所以可以通過binlog來實(shí)時(shí)備份和恢復(fù)數(shù)據(jù)庫(kù)。

以上是“mysql中relay log和binlog有什么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！