前言：
經(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 選項。
|
1
2
3
4
5
6
|
mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| 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ù)為互斥關(guān)系，一般只選擇其一設(shè)置，只能在啟動命令行中或配置文件中加入。指定多個數(shù)據(jù)庫要分行寫入，舉例如下：
|
1
2
3
4
5
6
7
8
9
|
# 指定 db1 db2 記錄binlog[mysqld]binlog_do_db = db1binlog_do_db = db2# 不讓 db3 db4 記錄binlog[mysqld]binlog_ignore_db = db3binlog_ignore_db = db4 |
此外，這二者參數(shù)具體作用與否還與 binlog 格式有關(guān)系，在某些情況下 binlog 格式設(shè)置為 STATEMENT 或 ROW 會有不同的效果。在實際應用中 binlog_ignore_db 用途更廣泛些，比如說某個庫的數(shù)據(jù)不太重要，為了減輕服務器寫入壓力，我們可能不讓該庫記錄 binlog 。網(wǎng)上也有文章說設(shè)置 binlog_ignore_db 會導致從庫同步錯誤，那么設(shè)置該參數(shù)到底有什么效果呢，下面我們來具體實驗下。
2. binlog_ignore_db 具體效果
首先說明下，我的測試數(shù)據(jù)庫實例是 5.7.23 社區(qū)版本，共有 testdb、logdb 兩個業(yè)務庫，我們設(shè)置 logdb 不記錄 binlog ，下面來具體實驗下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
# binlog 為 ROW 格式 # 1.不使用 use dbmysql> 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: 0mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000011 | 1070 | | logdb | |+---------------+----------+--------------+------------------+-------------------+# 結(jié)論：其他庫記錄正常 logdb庫會記錄DDL 不記錄DML# 2.使用 use testdb跨庫mysql> use testdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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庫會記錄DDL 不記錄DML # 3.使用 use logdb跨庫mysql> use logdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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都不記錄 同時不記錄其他庫的DDL# 4.每次操作都進入此庫 不跨庫mysql> use testdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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é)論：其他庫全部記錄 logdb全不記錄 |
同樣的，將 binlog 格式設(shè)置為 STATEMENT ，再次進行測試，這里不再贅述測試過程，總結(jié)下 STATEMENT 格式下的實驗結(jié)果：
- 未選擇任何數(shù)據(jù)庫進行操作，所有都會記錄。
- 選擇testdb，對testdb和logdb分別進行操作，所有庫都會記錄。
- 選擇logdb，對testdb和logdb分別進行操作，所有庫都不會記錄。
- 選擇某個庫并只對當前庫進行操作，則記錄正常，不會記錄logdb。
看了這么多實驗數(shù)據(jù)，你是否眼花繚亂了呢，下面我們以思維導圖的形式總結(jié)如下：
這么看來 binlog_ignore_db 參數(shù)的效果確實和諸多因素有關(guān)，特別是有從庫的情況下，主庫要特別小心使用此參數(shù)，很容易產(chǎn)生主從同步錯誤。不過，按照嚴格標準只對當前數(shù)據(jù)庫進行操作，則不會產(chǎn)生問題。這也告訴我們要嚴格按照標準來，只賦予業(yè)務賬號某個單庫的權(quán)限，也能避免各種問題發(fā)生。
總結(jié)：
不清楚各位讀者是否對這種介紹參數(shù)的文章感興趣呢？可能這些是數(shù)據(jù)庫運維人員比較關(guān)注的吧。本篇文章主要介紹關(guān)于 binlog 的 binlog_ignore_db 參數(shù)的具體作用，可能本篇文章實驗環(huán)境還不夠考慮周全，有興趣的同學可以參考下官方文檔，有助于對該參數(shù)有更深入的了解。
以上就是MySQL binlog參數(shù)的使用的詳細內(nèi)容，更多關(guān)于MySQL binlog參數(shù)的資料請關(guān)注服務器之家其它相關(guān)文章！
原文鏈接：https://mp.weixin.qq.com/s/Xwwr1HJKRglNH9V-Ddihlg
