MySQL binlog_ignore_db 參數(shù)的具體使用
發(fā)布日期：2022-02-22 17:55 | 文章來源：源碼之家
：
經(jīng)過前面文章學習，我們知道 binlog 會記錄數(shù)據(jù)庫所有執(zhí)行的 DDL 和 DML 語句（除了數(shù)據(jù)查詢語句select、show等）。注意默認情況下會記錄所有庫的操作，那么如果我們有另類需求，比如說只讓某個庫記錄 binglog 或排除某個庫記錄 binlog ，是否支持此類需求呢？本篇文章我們一起來看下。
1. binlog_do_db 與 binlog_ignore_db
當數(shù)據(jù)庫實例開啟 binlog 時，我們執(zhí)行 show master status 命令，會看到有 Binlog_Do_DB 與 Binlog_Ignore_DB 選項。
mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000009|282838|||| +---------------+----------+--------------+------------------+-------------------+
默認情況下，這兩個選項為空，那么這兩個參數(shù)有何作用？是否如同其字面意思一個只讓某個庫記錄 binglog 一個排除某個庫記錄 binlog 呢？筆者查閱官方文檔，簡單說明下這兩個參數(shù)的作用：
- binlog_do_db：此參數(shù)表示只記錄指定數(shù)據(jù)庫的二進制日志，默認全部記錄。
- binlog_ignore_db：此參數(shù)表示不記錄指定的數(shù)據(jù)庫的二進制日志。
這兩個參數(shù)為互斥關系，一般只選擇其一設置，只能在啟動命令行中或配置文件中加入。指定多個數(shù)據(jù)庫要分行寫入，舉例如下：
#指定db1db2記錄binlog [mysqld] binlog_do_db=db1 binlog_do_db=db2 #不讓db3db4記錄binlog [mysqld] binlog_ignore_db=db3 binlog_ignore_db=db4
此外，這二者參數(shù)具體作用與否還與 binlog 格式有關系，在某些情況下 binlog 格式設置為 STATEMENT 或 ROW 會有不同的效果。在實際應用中 binlog_ignore_db 用途更廣泛些，比如說某個庫的數(shù)據(jù)不太重要，為了減輕服務器寫入壓力，我們可能不讓該庫記錄 binlog 。網(wǎng)上也有文章說設置 binlog_ignore_db 會導致從庫同步錯誤，那么設置該參數(shù)到底有什么效果呢，下面我們來具體實驗下。
2. binlog_ignore_db 具體效果
首先說明下，我的測試數(shù)據(jù)庫實例是 5.7.23 社區(qū)版本，共有 testdb、logdb 兩個業(yè)務庫，我們設置 logdb 不記錄 binlog ，下面來具體實驗下：
#binlog為ROW格式 #1.不使用usedb mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|154||logdb|| +---------------+----------+--------------+------------------+-------------------+ mysql>selectdatabase(); +------------+ |database()| +------------+ |NULL| +------------+ 1rowinset(0.00sec) mysql>CREATETABLEtestdb.`test_tb1`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.06sec) mysql>insertintotestdb.test_tb1values(1001,'sdfde'); QueryOK,1rowaffected(0.01sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|653||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLElogdb.`log_tb1`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.05sec) mysql>insertintologdb.log_tb1values(1001,'sdfde'); QueryOK,1rowaffected(0.00sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|883||logdb|| +---------------+----------+--------------+------------------+-------------------+ mysql>insertintologdb.log_tb1values(1002,'sdsdfde'); QueryOK,1rowaffected(0.01sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|883||logdb|| +---------------+----------+--------------+------------------+-------------------+ mysql>altertablelogdb.log_tb1addcolumnc3varchar(20); QueryOK,0rowsaffected(0.12sec) Records:0Duplicates:0Warnings:0 mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1070||logdb|| +---------------+----------+--------------+------------------+-------------------+ #結論：其他庫記錄正常 logdb庫會記錄DDL 不記錄DML #2.使用usetestdb跨庫 mysql>usetestdb; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql>selectdatabase(); +------------+ |database()| +------------+ |testdb| +------------+ 1rowinset(0.00sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1070||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLE`test_tb2`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.05sec) mysql>insertintotest_tb2values(1001,'sdfde'); QueryOK,1rowaffected(0.04sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1574||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLElogdb.`log_tb2`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.05sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1810||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>insertintologdb.log_tb2values(1001,'sdfde'); QueryOK,1rowaffected(0.01sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1810||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) #結論：同樣logdb庫會記錄DDL 不記錄DML #3.使用uselogdb跨庫 mysql>uselogdb; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql>selectdatabase(); +------------+ |database()| +------------+ |logdb| +------------+ 1rowinset(0.00sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1810||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLEtestdb.`test_tb3`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.23sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|1810||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>insertintotestdb.test_tb3values(1001,'sdfde'); QueryOK,1rowaffected(0.02sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2081||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLE`log_tb3`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.05sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2081||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>insertintolog_tb3values(1001,'sdfde'); QueryOK,1rowaffected(0.02sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2081||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) #結論：logdb都不記錄同時不記錄其他庫的DDL #4.每次操作都進入此庫不跨庫 mysql>usetestdb; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2081||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>CREATETABLE`test_tb4`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.05sec) mysql>insertintotest_tb4values(1001,'sdfde'); QueryOK,1rowaffected(0.01sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2585||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>uselogdb; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql>CREATETABLE`log_tb4`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.04sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2585||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>insertintolog_tb4values(1001,'sdfde'); QueryOK,1rowaffected(0.01sec) mysql>showmasterstatus; +---------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +---------------+----------+--------------+------------------+-------------------+ |binlog.000011|2585||logdb|| +---------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) #結論：其他庫全部記錄 logdb全不記錄
同樣的，將 binlog 格式設置為 STATEMENT ，再次進行測試，這里不再贅述測試過程，總結下 STATEMENT 格式下的實驗結果：
- 未選擇任何數(shù)據(jù)庫進行操作，所有都會記錄。
- 選擇testdb，對testdb和logdb分別進行操作，所有庫都會記錄。
- 選擇logdb，對testdb和logdb分別進行操作，所有庫都不會記錄。
- 選擇某個庫并只對當前庫進行操作，則記錄正常，不會記錄logdb。
看了這么多實驗數(shù)據(jù)，你是否眼花繚亂了呢，下面我們以思維導圖的形式總結如下：
這么看來 binlog_ignore_db 參數(shù)的效果確實和諸多因素有關，特別是有從庫的情況下，主庫要特別小心使用此參數(shù)，很容易產(chǎn)生主從同步錯誤。不過，按照嚴格標準只對當前數(shù)據(jù)庫進行操作，則不會產(chǎn)生問題。這也告訴我們要嚴格按照標準來，只賦予業(yè)務賬號某個單庫的權限，也能避免各種問題發(fā)生。
總結：
不清楚各位讀者是否對這種介紹參數(shù)的文章感興趣呢？可能這些是數(shù)據(jù)庫運維人員比較關注的吧。本篇文章主要介紹關于 binlog 的 binlog_ignore_db 參數(shù)的具體作用，可能本篇文章實驗環(huán)境還不夠考慮周全，有興趣的同學可以參考下官方文檔，有助于對該參數(shù)有更深入的了解。
以上就是MySQL binlog參數(shù)的使用的詳細內容，更多關于MySQL binlog參數(shù)的資料請關注本站其它相關文章！
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
相關文章
關注官方微信