溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關binlog_ignore_db 參數在MySQL數據庫的作用有哪些，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
1. binlog_do_db 與 binlog_ignore_db
當數據庫實例開啟 binlog 時，我們執行 show master status 命令，會看到有 Binlog_Do_DB 與 Binlog_Ignore_DB 選項。
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000009 | 282838 | | | | +---------------+----------+--------------+------------------+-------------------+
默認情況下，這兩個選項為空，那么這兩個參數有何作用？是否如同其字面意思一個只讓某個庫記錄 binglog 一個排除某個庫記錄 binlog 呢？筆者查閱官方文檔，簡單說明下這兩個參數的作用：
這兩個參數為互斥關系，一般只選擇其一設置，只能在啟動命令行中或配置文件中加入。指定多個數據庫要分行寫入，舉例如下：
# 指定 db1 db2 記錄binlog [mysqld] binlog_do_db = db1 binlog_do_db = db2 # 不讓 db3 db4 記錄binlog [mysqld] binlog_ignore_db = db3 binlog_ignore_db = db4
此外，這二者參數具體作用與否還與 binlog 格式有關系，在某些情況下 binlog 格式設置為 STATEMENT 或 ROW 會有不同的效果。在實際應用中 binlog_ignore_db 用途更廣泛些，比如說某個庫的數據不太重要，為了減輕服務器寫入壓力，我們可能不讓該庫記錄 binlog 。網上也有文章說設置 binlog_ignore_db 會導致從庫同步錯誤，那么設置該參數到底有什么效果呢，下面我們來具體實驗下。
2. binlog_ignore_db 具體效果
首先說明下，我的測試數據庫實例是 5.7.23 社區版本，共有 testdb、logdb 兩個業務庫，我們設置 logdb 不記錄 binlog ，下面來具體實驗下：
# binlog 為 ROW 格式 # 1.不使用 use db mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 154 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE testdb.`test_tb1` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.06 sec) mysql> insert into testdb.test_tb1 values (1001,'sdfde'); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 653 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE logdb.`log_tb1` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> insert into logdb.log_tb1 values (1001,'sdfde'); Query OK, 1 row affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 883 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ mysql> insert into logdb.log_tb1 values (1002,'sdsdfde'); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 883 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ mysql> alter table logdb.log_tb1 add column c3 varchar(20); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1070 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ # 結論：其他庫記錄正常 logdb庫會記錄DDL 不記錄DML # 2.使用 use testdb跨庫 mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select database(); +------------+ | database() | +------------+ | testdb | +------------+ 1 row in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1070 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `test_tb2` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> insert into test_tb2 values (1001,'sdfde'); Query OK, 1 row affected (0.04 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1574 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE logdb.`log_tb2` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1810 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into logdb.log_tb2 values (1001,'sdfde'); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1810 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 結論：同樣logdb庫會記錄DDL 不記錄DML # 3.使用 use logdb跨庫 mysql> use logdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select database(); +------------+ | database() | +------------+ | logdb | +------------+ 1 row in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1810 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE testdb.`test_tb3` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.23 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 1810 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into testdb.test_tb3 values (1001,'sdfde'); Query OK, 1 row affected (0.02 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2081 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `log_tb3` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2081 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into log_tb3 values (1001,'sdfde'); Query OK, 1 row affected (0.02 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2081 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 結論：logdb都不記錄 同時不記錄其他庫的DDL # 4.每次操作都進入此庫 不跨庫 mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2081 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `test_tb4` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> insert into test_tb4 values (1001,'sdfde'); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2585 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> use logdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE `log_tb4` ( id int , name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2585 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into log_tb4 values (1001,'sdfde'); Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000011 | 2585 | | logdb | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 結論：其他庫全部記錄 logdb全不記錄
同樣的，將 binlog 格式設置為 STATEMENT ，再次進行測試，這里不再贅述測試過程，總結下 STATEMENT 格式下的實驗結果：
看了這么多實驗數據，你是否眼花繚亂了呢，下面我們以思維導圖的形式總結如下：
這么看來 binlog_ignore_db 參數的效果確實和諸多因素有關，特別是有從庫的情況下，主庫要特別小心使用此參數，很容易產生主從同步錯誤。不過，按照嚴格標準只對當前數據庫進行操作，則不會產生問題。這也告訴我們要嚴格按照標準來，只賦予業務賬號某個單庫的權限，也能避免各種問題發生。
關于binlog_ignore_db 參數在MySQL數據庫的作用有哪些就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
