MySQL?Binlog日志寫(xiě)入及文件操作

更新時(shí)間：2023年07月06日 14:12:29 作者：Thresh

這篇文章主要為大家介紹了MySQL?Binlog日志寫(xiě)入及文件操作詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

Binlog 記錄模式

Redo Log 是屬于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二進(jìn)制日志），簡(jiǎn)稱(chēng)Binlog。

Binlog是記錄所有數(shù)據(jù)庫(kù)表結(jié)構(gòu)變更以及表數(shù)據(jù)修改的二進(jìn)制日志，不會(huì)記錄SELECT和SHOW這類(lèi)操作。

Binlog日志是以事件形式記錄，還包含語(yǔ)句所執(zhí)行的消耗時(shí)間。

開(kāi)啟Binlog日志有以下兩個(gè)最重要的使用場(chǎng)景。

主從復(fù)制：在主庫(kù)中開(kāi)啟Binlog功能，這樣主庫(kù)就可以把Binlog傳遞給從庫(kù)，從庫(kù)拿到Binlog后實(shí)現(xiàn)數(shù)據(jù)恢復(fù)達(dá)到主從數(shù)據(jù)一致性。

數(shù)據(jù)恢復(fù)：通過(guò)mysqlbinlog工具來(lái)恢復(fù)數(shù)據(jù)。

Binlog文件名默認(rèn)為“主機(jī)名_binlog-序列號(hào)”格式，例如oak_binlog-000001，也可以在配置文件中指定名稱(chēng)。

文件記錄模式有STATEMENT、ROW和MIXED三種，具體含義如下。

ROW（row-based replication, RBR）

日志中會(huì)記錄每一行數(shù)據(jù)被修改的情況，然后在slave端對(duì)相同的數(shù)據(jù)進(jìn)行修改。

優(yōu)點(diǎn)：能清楚記錄每一個(gè)行數(shù)據(jù)的修改細(xì)節(jié)，能完全實(shí)現(xiàn)主從數(shù)據(jù)同步和數(shù)據(jù)的恢復(fù)。

缺點(diǎn)：批量操作，會(huì)產(chǎn)生大量的日志，尤其是alter table會(huì)讓日志暴漲。

STATMENT（statement-based replication, SBR）

每一條被修改數(shù)據(jù)的SQL都會(huì)記錄到master的Binlog中，slave在復(fù)制的時(shí)候SQL進(jìn)程會(huì)解析成和原來(lái)master端執(zhí)行過(guò)的相同的SQL再次執(zhí)行。簡(jiǎn)稱(chēng)SQL語(yǔ)句復(fù)制。

優(yōu)點(diǎn)：日志量小，減少磁盤(pán)IO，提升存儲(chǔ)和恢復(fù)速度
缺點(diǎn)：在某些情況下會(huì)導(dǎo)致主從數(shù)據(jù)不一致，比如last_insert_id()、now()等函數(shù)。

MIXED（mixed-based replication, MBR）

以上兩種模式的混合使用，一般會(huì)使用 STATEMENT 模式保存binlog，對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用 ROW 模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇寫(xiě)入模式。

Binlog 寫(xiě)入機(jī)制

常用的log event有：Query event、Row event、Xid event等。binlog文件的內(nèi)容就是各種Log event的集合。

根據(jù)記錄模式和操作觸發(fā)event事件生成log event（事件觸發(fā)執(zhí)行機(jī)制）
將事務(wù)執(zhí)行過(guò)程中產(chǎn)生log event寫(xiě)入緩沖區(qū)，每個(gè)事務(wù)線(xiàn)程都有一個(gè)緩沖區(qū)

Log Event保存在一個(gè)binlog_cache_mngr數(shù)據(jù)結(jié)構(gòu)中，在該結(jié)構(gòu)中有兩個(gè)緩沖區(qū)，一個(gè)是stmt_cache，用于存放不支持事務(wù)的信息；另一個(gè)是trx_cache，用于存放支持事務(wù)的信息。

事務(wù)在提交階段會(huì)將產(chǎn)生的log event寫(xiě)入到外部binlog文件中。

不同事務(wù)以串行方式將log event寫(xiě)入binlog文件中，所以一個(gè)事務(wù)包含的log event信息在binlog文件中是連續(xù)的，中間不會(huì)插入其他事務(wù)的log event。

Binlog 文件操作

Binlog狀態(tài)查看

show variables like 'log_bin';

開(kāi)啟Binlog功能

需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重啟MySQL服務(wù)。

#log-bin=ON 
#log-bin-basename=mysqlbinlog 
binlog-format=ROW 
log-bin=mysqlbinlog

執(zhí)行開(kāi)啟語(yǔ)句

set global log_bin=mysqllogbin;

使用show binlog events命令

show binary logs; //等價(jià)于show master logs; 
show master status; 
show binlog events; 
show binlog events in 'mysqlbinlog.000001'\G;
結(jié)果:
     Log_name: mysql_bin.000001   //此條log存在那個(gè)文件中
        Pos: 174                 //log在bin-log中的開(kāi)始位置 
     Event_type: Intvar          //log的類(lèi)型信息 
        Server_id: 1  //可以查看配置中的server_id,表示log是那個(gè)服務(wù)器產(chǎn)生 
     End_log_pos: 202          //log在bin-log中的結(jié)束位置 
        Info: INSERT_ID=2    //log的一些備注信息，可以直觀的看出進(jìn)行了什么操作

可以用mysql自帶的工具mysqlbinlog

mysqlbinlog "文件名" mysqlbinlog "文件名" > "文件名比如:test.sql"

使用 binlog 恢復(fù)數(shù)據(jù)

//按指定時(shí)間恢復(fù) 
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop-datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 
//按事件位置號(hào)恢復(fù) 
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

mysqldump：定期全部備份數(shù)據(jù)庫(kù)數(shù)據(jù)。mysqlbinlog: 可以做增量備份和恢復(fù)操作。

刪除Binlog文件

purge binary logs to 'mysqlbinlog.000001'; //刪除指定文件 
purge binary logs before '2020-04-28 00:00:00'; //刪除指定時(shí)間之前的文件 
reset master; //清除所有文件

可以通過(guò)設(shè)置expire_logs_days參數(shù)來(lái)啟動(dòng)自動(dòng)清理功能。默認(rèn)值為0表示沒(méi)啟用。設(shè)置為1表示超出1天binlog文件會(huì)自動(dòng)刪除掉

Redo Log和 Binlog 區(qū)別

Redo Log是屬于InnoDB引擎功能，Binlog是屬于MySQL Server自帶功能，并且是以二進(jìn)制文件記錄。
Redo Log屬于物理日志，記錄該數(shù)據(jù)頁(yè)更新?tīng)顟B(tài)內(nèi)容，Binlog是邏輯日志，記錄更新過(guò)程。
Redo Log日志是循環(huán)寫(xiě)，日志空間大小是固定，Binlog是追加寫(xiě)入，寫(xiě)完一個(gè)寫(xiě)下一個(gè)，不會(huì)覆蓋使用。
Redo Log作為服務(wù)器異常宕機(jī)后事務(wù)數(shù)據(jù)自動(dòng)恢復(fù)使用，Binlog可以作為主從復(fù)制和數(shù)據(jù)恢復(fù)使用。Binlog沒(méi)有自動(dòng)crash-safe能力。

(crash-safe 即在 InnoDB 存儲(chǔ)引擎中，事務(wù)提交過(guò)程中任何階段，MySQL突然奔潰，重啟后都能保證事務(wù)的完整性，已提交的數(shù)據(jù)不會(huì)丟失，未提交完整的數(shù)據(jù)會(huì)自動(dòng)進(jìn)行回滾。這個(gè)能力依賴(lài)的就是redo log和unod log兩個(gè)日志。)

以上就是MySQL Binlog日志寫(xiě)入及文件操作的詳細(xì)內(nèi)容，更多關(guān)于MySQL Binlog日志的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: