深圳網(wǎng)絡(luò)營銷網(wǎng)站設(shè)計,手機管理網(wǎng)站模板下載安裝,網(wǎng)站改版對優(yōu)化的影響,外貿(mào)建站哪家公司專業(yè)簡介 binlog是一個二進制格式的文件#xff0c;用于記錄用戶對數(shù)據(jù)的更新操作#xff0c;一般來說#xff0c;開啟binlog大概會帶來1%的性能損耗。 binlog的使用場景#xff1a;主從復(fù)制、數(shù)據(jù)恢復(fù) binlog日志中包含日志索引文件和日志文件#xff0c;日志索引文件用于記錄…簡介binlog是一個二進制格式的文件用于記錄用戶對數(shù)據(jù)的更新操作一般來說開啟binlog大概會帶來1%的性能損耗。binlog的使用場景主從復(fù)制、數(shù)據(jù)恢復(fù)binlog日志中包含日志索引文件和日志文件日志索引文件用于記錄所有的二進制文件日志文件中記錄對數(shù)據(jù)庫的修改操作包括DML和DDL查看binlog的配置在mysql客戶端執(zhí)行如下命令查看binlog的相關(guān)配置1、查看是否開啟binlog show variables like ‘log_bin’ ON表示已開啟2、查看binlog的相關(guān)變量 show variables like ‘%log_bin%’ 、 show variables like ‘%binlog%’變量內(nèi)容講解是否開啟binloglog_bin默認值為ON表示已開啟binlogsql_log_bin當前會話的binlog記錄是否開啟ON是已開啟binlog的文件存放位置log_bin_basenamebinlog文件的基礎(chǔ)路徑例如 /var/lib/mysql/binlog.000001 等文件log_bin_indexbinlog索引文件的路徑binlog_formatbinlog的日志格式默認是ROW共有有三個可選值ROW記錄每行數(shù)據(jù)的變化不記錄SQL語句它的優(yōu)點是數(shù)據(jù)安全缺點是日志文件比較大不易閱讀STATEMENT記錄實際執(zhí)行的sql語句MIXED混合模式大部分使用STATEMENT特殊情況使用ROW例如某些根據(jù)時間來刪除數(shù)據(jù)的sql它對于數(shù)據(jù)庫的操作是不穩(wěn)定的不同時間執(zhí)行會獲得不同的結(jié)果所以需要使用ROW模式。binlog保留策略的相關(guān)配置binlog_expire_logs_seconds默認值是2592000單位是秒表示30天表示binlog默認保留30天binlog_expire_logs_auto_purge值為ON表示自動清理過期的binlogbinlog性能相關(guān)配置max_binlog_size單個binlog文件最大1GBsync_binlog每次提交都同步到磁盤默認中是10由系統(tǒng)決定何時同步1每次事務(wù)提交都同步最安全數(shù)據(jù)不丟失N每N次事務(wù)提交同步一次binlog_cache_sizebinlog緩存32KB高級特性binlog_row_imageFULL記錄完整的行數(shù)據(jù)binlog_checksumCRC32binlog校驗算法binlog_row_metadataMINIMAL行元數(shù)據(jù)記錄最小化binlog_rows_query_log_events OFF不記錄原始SQL語句如果設(shè)為ON會在ROW格式下同時記錄原始SQL語句建議在生產(chǎn)環(huán)境設(shè)為ON便于問題排查總結(jié)從這些配置中可以看出binlog默認是開啟的binlog的存放目錄這里是/var/lib/mysql/binlog的格式這里是ROW它記錄的sql操作的數(shù)據(jù)并且會導(dǎo)致binlog文件比較大binlog的生成默認每次提交都同步到磁盤默認保留30天配置binlog案例[mysqld] #配置serverid server-id1這里全部使用binlog的默認配置只額外配置一個服務(wù)器的id主從同步時需要使用到配置完成后重啟mysql服務(wù)systemctl restart mysqld介紹binlog中的內(nèi)容binlog由一系列事件組成除了binlog自身獨有的事件例如文件創(chuàng)建、文件輪轉(zhuǎn)大多數(shù)事件都對應(yīng)了mysql的一條sql。事件的結(jié)構(gòu)事件頭包含事件類型、時間戳、服務(wù)器ID等事件體事件的具體內(nèi)容校驗和binlog中的事件類型Format_desc事件binlog文件的開始事件所有binlog文件都是已這個事件開始的。這個事件記錄的信息中包含服務(wù)器的版本、binlog版本Previous_gtids事件記錄之前所有binlog文件中的gtid集合Anonymous_Gtid事件在沒有啟動GTID時標識事務(wù)開始Query事件查詢事件Table_map事件表映射事件建立表名到內(nèi)部id的映射通過這種方式減少數(shù)據(jù)量Delete_rows事件刪除行Xid事件事務(wù)提交事件Rotate事件binlog文件輪轉(zhuǎn)事件當前binlog文件結(jié)束切換到下一個binlog文件這里沒有列舉出全部還有諸如 Update_rows、Write_rows 等事件事件的位置position相當于這個事件的下標在文件中唯一標識這個事件根據(jù)它來操作binlog事件的GTIDGlobal Transaction Identifier全局事務(wù)標識符mysql 5.6之后引入的新特性為每個事務(wù)分配一個全局唯一的標識符。GTID默認是關(guān)閉的通過它來指定事件會更方便。查看binlog中的內(nèi)容1、查看當前mysql實例有哪些binlogshow master logs------------------------------------- | Log_name | File_size | Encrypted | ------------------------------------- | binlog.000028 | 377 | No | | binlog.000029 | 509 | No | | binlog.000030 | 157 | No | -------------------------------------這里共查詢到3個binlog文件包括文件名稱、文件大小、是否加密2、查看當前binlog點位show master status---------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ---------------------------------------------------------------------------- | binlog.000030 | 157 | | | | ----------------------------------------------------------------------------這里的內(nèi)容是最后一個binlog文件的名稱、最后一次操作時間的position值3、查看binlog中的事件show binlog events in ‘binlog.000029’;-------------------------------------------------------------------------------------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | -------------------------------------------------------------------------------------------------- | binlog.000029 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.38, Binlog ver: 4 | | binlog.000029 | 126 | Previous_gtids | 1 | 157 | | | binlog.000029 | 157 | Anonymous_Gtid | 1 | 236 | SET SESSION.GTID_NEXT ANONYMOUS | | binlog.000029 | 236 | Query | 1 | 311 | BEGIN | | binlog.000029 | 311 | Table_map | 1 | 374 | table_id: 97 (blog.ms_tag) | | binlog.000029 | 374 | Delete_rows | 1 | 434 | table_id: 97 flags: STMT_END_F | | binlog.000029 | 434 | Xid | 1 | 465 | COMMIT /* xid17 */ | | binlog.000029 | 465 | Rotate | 1 | 509 | binlog.000030;pos4 | --------------------------------------------------------------------------------------------------內(nèi)容解讀這里只做部分介紹可以在Info列中看到BEGIN到COMMIT之間就是一個刪除事務(wù)根據(jù)binlog來恢復(fù)數(shù)據(jù)就是以事務(wù)為單位的。3、查看binlog文件中的內(nèi)容mysqlbinlog binlog.000029使用mysqlbinlog命令進入binlog所在目錄查看文件中的內(nèi)容因為是二進制內(nèi)容所以要使用mysqlbinlog命令。文件中關(guān)于sql操作的數(shù)據(jù)使用加密的如果想要查看需要指定參數(shù)mysqlbinlog --base64-outputDECODE-ROWS -v binlog.000029原始的sql日志不太好看有一個關(guān)鍵點就是找# at N的位置它表示一個事件開始N是字節(jié)偏移根據(jù)binlog恢復(fù)數(shù)據(jù)根據(jù)binlog恢復(fù)數(shù)據(jù)的原理是通過把binlog中執(zhí)行的操作全部再執(zhí)行一次然后跳過需要被排除的操作來恢復(fù)錯誤操作帶來的影響同時還要注意如果binlog因過期等原因被刪除那么通過binlog無法恢復(fù)數(shù)據(jù)。任意時間點的數(shù)據(jù)狀態(tài) 基礎(chǔ)備份狀態(tài) 從備份時間點到目標時間點的所有變更 - 需要排除的錯誤變更所以使用binlog恢復(fù)數(shù)據(jù)依賴最新備份的數(shù)據(jù) 這之間所有的binlog日期 - 錯誤變更或者全量的binlog日志常見的方式是定期備份數(shù)據(jù)然后清理已備份數(shù)據(jù)的binlog如果需要通過binlog來恢復(fù)數(shù)據(jù)就把數(shù)據(jù)恢復(fù)到最新備份然后執(zhí)行最新備份到當前時間的binlog跳過需要被排除的事務(wù)從而達到恢復(fù)操作的目的?；謴?fù)時必須以事務(wù)為單位也就是日志中BEGIN到COMMIT之間的所有操作是一個單位恢復(fù)數(shù)據(jù)之前最好備份當前數(shù)據(jù)同時停止服務(wù)避免誤操作。方式一案例重新執(zhí)行所有的binlog跳過需要被排除的事務(wù)通過position來指定需要被排除的事務(wù)# 應(yīng)用刪除前的操作4-236 mysqlbinlog --start-position4 --stop-position236 binlog.000029 | mysql -u root -p blog # 應(yīng)用刪除后的操作465-509 mysqlbinlog --start-position465 binlog.000029 | mysql -u root -p blog這里只是一個案例演示一下命令的使用實際上當前binlog無法恢復(fù)數(shù)據(jù)因為在刪除數(shù)據(jù)之前新增數(shù)據(jù)的binlog已經(jīng)過期了所以即使執(zhí)行當前命令也無法恢復(fù)數(shù)據(jù)。這里僅僅演示命令的使用。案例2通過時間點來指定需要被排除的事務(wù)mysqlbinlog --start-datetime2025-12-19 00:00:00 --stop-datetime2025-12-19 13:56:09 binlog.000029 | mysql -u root -p blog這里是把數(shù)據(jù)庫中的數(shù)據(jù)恢復(fù)到指定時間點之前因為在日志中刪除操作在 251219 13:56:10 執(zhí)行所以指定一個比它早的時間 “2025-12-19 13:56:09”方式二根據(jù)binlog生成相反的操作然后恢復(fù)數(shù)據(jù)這需要依賴第三方工具例如binlog2sql有時間再介紹通過binlog同步數(shù)據(jù)的流程通過binlog同步數(shù)據(jù)的流程主庫寫binlog主庫發(fā)送binlog主庫會使用一個單獨的線程來發(fā)送binlog從庫接收binlog并且把它寫入到relay log從庫回放從庫執(zhí)行relay log中的sql達到數(shù)據(jù)同步的目的