前言：
經(jīng)過(guò)前面文章學(xué)習(xí)，我們知道 binlog 會(huì)記錄數(shù)據(jù)庫(kù)所有執(zhí)行的 DDL 和 DML 語(yǔ)句（除了數(shù)據(jù)查詢(xún)語(yǔ)句select、show等）。注意默認(rèn)情況下會(huì)記錄所有庫(kù)的操作，那么如果我們有另類(lèi)需求，比如說(shuō)只讓某個(gè)庫(kù)記錄 binglog 或排除某個(gè)庫(kù)記錄 binlog ，是否支持此類(lèi)需求呢？本篇文章我們一起來(lái)看下。
1. binlog_do_db 與 binlog_ignore_db
當(dāng)數(shù)據(jù)庫(kù)實(shí)例開(kāi)啟 binlog 時(shí)，我們執(zhí)行 show master status 命令，會(huì)看到有 Binlog_Do_DB 與 Binlog_Ignore_DB 選項(xiàng)。
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 | 282838 | | | |
+---------------+----------+--------------+------------------+-------------------+
默認(rèn)情況下，這兩個(gè)選項(xiàng)為空，那么這兩個(gè)參數(shù)有何作用？是否如同其字面意思一個(gè)只讓某個(gè)庫(kù)記錄 binglog 一個(gè)排除某個(gè)庫(kù)記錄 binlog 呢？筆者查閱官方文檔，簡(jiǎn)單說(shuō)明下這兩個(gè)參數(shù)的作用：
- binlog_do_db：此參數(shù)表示只記錄指定數(shù)據(jù)庫(kù)的二進(jìn)制日志，默認(rèn)全部記錄。
- binlog_ignore_db：此參數(shù)表示不記錄指定的數(shù)據(jù)庫(kù)的二進(jìn)制日志。
這兩個(gè)參數(shù)為互斥關(guān)系，一般只選擇其一設(shè)置，只能在啟動(dòng)命令行中或配置文件中加入。指定多個(gè)數(shù)據(jù)庫(kù)要分行寫(xiě)入，舉例如下：
# 指定 db1 db2 記錄binlog
[mysqld]
binlog_do_db = db1
binlog_do_db = db2
# 不讓 db3 db4 記錄binlog
[mysqld]
binlog_ignore_db = db3
binlog_ignore_db = db4
此外，這二者參數(shù)具體作用與否還與 binlog 格式有關(guān)系，在某些情況下 binlog 格式設(shè)置為 STATEMENT 或 ROW 會(huì)有不同的效果。在實(shí)際應(yīng)用中 binlog_ignore_db 用途更廣泛些，比如說(shuō)某個(gè)庫(kù)的數(shù)據(jù)不太重要，為了減輕服務(wù)器寫(xiě)入壓力，我們可能不讓該庫(kù)記錄 binlog 。網(wǎng)上也有文章說(shuō)設(shè)置 binlog_ignore_db 會(huì)導(dǎo)致從庫(kù)同步錯(cuò)誤，那么設(shè)置該參數(shù)到底有什么效果呢，下面我們來(lái)具體實(shí)驗(yàn)下。
2. binlog_ignore_db 具體效果
首先說(shuō)明下，我的測(cè)試數(shù)據(jù)庫(kù)實(shí)例是 5.7.23 社區(qū)版本，共有 testdb、logdb 兩個(gè)業(yè)務(wù)庫(kù)，我們?cè)O(shè)置 logdb 不記錄 binlog ，下面來(lái)具體實(shí)驗(yàn)下：
# 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 | |
+---------------+----------+--------------+------------------+-------------------+
# 結(jié)論：其他庫(kù)記錄正常 logdb庫(kù)會(huì)記錄DDL 不記錄DML
# 2.使用 use testdb跨庫(kù)
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)
# 結(jié)論：同樣logdb庫(kù)會(huì)記錄DDL 不記錄DML
# 3.使用 use logdb跨庫(kù)
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)
# 結(jié)論：logdb都不記錄 同時(shí)不記錄其他庫(kù)的DDL
# 4.每次操作都進(jìn)入此庫(kù) 不跨庫(kù)
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)
# 結(jié)論：其他庫(kù)全部記錄 logdb全不記錄
同樣的，將 binlog 格式設(shè)置為 STATEMENT ，再次進(jìn)行測(cè)試，這里不再贅述測(cè)試過(guò)程，總結(jié)下 STATEMENT 格式下的實(shí)驗(yàn)結(jié)果：
- 未選擇任何數(shù)據(jù)庫(kù)進(jìn)行操作，所有都會(huì)記錄。
- 選擇testdb，對(duì)testdb和logdb分別進(jìn)行操作，所有庫(kù)都會(huì)記錄。
- 選擇logdb，對(duì)testdb和logdb分別進(jìn)行操作，所有庫(kù)都不會(huì)記錄。
- 選擇某個(gè)庫(kù)并只對(duì)當(dāng)前庫(kù)進(jìn)行操作，則記錄正常，不會(huì)記錄logdb。
看了這么多實(shí)驗(yàn)數(shù)據(jù)，你是否眼花繚亂了呢，下面我們以思維導(dǎo)圖的形式總結(jié)如下：
這么看來(lái) binlog_ignore_db 參數(shù)的效果確實(shí)和諸多因素有關(guān)，特別是有從庫(kù)的情況下，主庫(kù)要特別小心使用此參數(shù)，很容易產(chǎn)生主從同步錯(cuò)誤。不過(guò)，按照嚴(yán)格標(biāo)準(zhǔn)只對(duì)當(dāng)前數(shù)據(jù)庫(kù)進(jìn)行操作，則不會(huì)產(chǎn)生問(wèn)題。這也告訴我們要嚴(yán)格按照標(biāo)準(zhǔn)來(lái)，只賦予業(yè)務(wù)賬號(hào)某個(gè)單庫(kù)的權(quán)限，也能避免各種問(wèn)題發(fā)生。
總結(jié)：
不清楚各位讀者是否對(duì)這種介紹參數(shù)的文章感興趣呢？可能這些是數(shù)據(jù)庫(kù)運(yùn)維人員比較關(guān)注的吧。本篇文章主要介紹關(guān)于 binlog 的 binlog_ignore_db 參數(shù)的具體作用，可能本篇文章實(shí)驗(yàn)環(huán)境還不夠考慮周全，有興趣的同學(xué)可以參考下官方文檔，有助于對(duì)該參數(shù)有更深入的了解。
以上就是MySQL binlog參數(shù)的使用的詳細(xì)內(nèi)容，更多關(guān)于MySQL binlog參數(shù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL系列之redo log、undo log和binlog詳解
- MySQL中使用binlog時(shí)格式該如何選擇
- 詳解監(jiān)聽(tīng)MySQL的binlog日志工具分析：Canal
- MySQL8.0中binlog的深入講解
- MYSQL中binlog優(yōu)化的一些思考匯總
- Mysql數(shù)據(jù)庫(kù)清理binlog日志命令詳解
- Mysql數(shù)據(jù)庫(kù)監(jiān)聽(tīng)binlog的開(kāi)啟步驟
- 如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog
