前言：
經(jīng)過(guò)前面文章學(xué)習(xí)，我們知道 binlog 會(huì)記錄數(shù)據(jù)庫(kù)所有執(zhí)行的 DDL 和 DML 語(yǔ)句（除了數(shù)據(jù)查詢語(yǔ)句select、show等）。注意默認(rèn)情況下會(huì)記錄所有庫(kù)的操作，那么如果我們有另類需求，比如說(shuō)只讓某個(gè)庫(kù)記錄 binglog 或排除某個(gè)庫(kù)記錄 binlog ，是否支持此類需求呢？本篇文章我們一起來(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)。
|
1
2
3
4
5
6
|
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ě)入，舉例如下：
|
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 會(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)下：
|
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é)論：其他庫(kù)記錄正常 logdb庫(kù)會(huì)記錄DDL 不記錄DML# 2.使用 use testdb跨庫(kù)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庫(kù)會(huì)記錄DDL 不記錄DML # 3.使用 use logdb跨庫(kù)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都不記錄 同時(shí)不記錄其他庫(kù)的DDL# 4.每次操作都進(jìn)入此庫(kù) 不跨庫(kù)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é)論：其他庫(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)注服務(wù)器之家其它相關(guān)文章！
原文鏈接：https://mp.weixin.qq.com/s/Xwwr1HJKRglNH9V-Ddihlg
