服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL binlog_ignore_db 参数的具体使用

MySQL binlog_ignore_db 参数的具体使用

2021-03-15 17:47MySQL技术 Mysql

这篇文章主要介绍了MySQL binlog_ignore_db 参数的具体作用，帮助大家更好的理解和使用MySQL数据库，感兴趣的朋友可以了解下

前言： 

经过前面文章学习，我们知道 binlog 会记录数据库所有执行的 DDL 和 DML 语句（除了数据查询语句select、show等）。注意默认情况下会记录所有库的操作，那么如果我们有另类需求，比如说只让某个库记录 binglog 或排除某个库记录 binlog ，是否支持此类需求呢？本篇文章我们一起来看下。

1. binlog_do_db 与 binlog_ignore_db

当数据库实例开启 binlog 时，我们执行 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 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

默认情况下，这两个选项为空，那么这两个参数有何作用？是否如同其字面意思一个只让某个库记录 binglog 一个排除某个库记录 binlog 呢？笔者查阅官方文档，简单说明下这两个参数的作用：

  • binlog_do_db：此参数表示只记录指定数据库的二进制日志，默认全部记录。
  • binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志。

这两个参数为互斥关系，一般只选择其一设置，只能在启动命令行中或配置文件中加入。指定多个数据库要分行写入，举例如下：

?
1
2
3
4
5
6
7
8
9
# 指定 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 ，下面来具体实验下：

?
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 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 格式下的实验结果：

  • 未选择任何数据库进行操作，所有都会记录。
  • 选择testdb，对testdb和logdb分别进行操作，所有库都会记录。
  • 选择logdb，对testdb和logdb分别进行操作，所有库都不会记录。
  • 选择某个库并只对当前库进行操作，则记录正常，不会记录logdb。

看了这么多实验数据，你是否眼花缭乱了呢，下面我们以思维导图的形式总结如下：

MySQL binlog_ignore_db 参数的具体使用

这么看来 binlog_ignore_db 参数的效果确实和诸多因素有关，特别是有从库的情况下，主库要特别小心使用此参数，很容易产生主从同步错误。不过，按照严格标准只对当前数据库进行操作，则不会产生问题。这也告诉我们要严格按照标准来，只赋予业务账号某个单库的权限，也能避免各种问题发生。

总结： 

不清楚各位读者是否对这种介绍参数的文章感兴趣呢？可能这些是数据库运维人员比较关注的吧。本篇文章主要介绍关于 binlog 的 binlog_ignore_db 参数的具体作用，可能本篇文章实验环境还不够考虑周全，有兴趣的同学可以参考下官方文档，有助于对该参数有更深入的了解。

以上就是MySQL binlog参数的使用的详细内容，更多关于MySQL binlog参数的资料请关注服务器之家其它相关文章！

原文链接：https://mp.weixin.qq.com/s/Xwwr1HJKRglNH9V-Ddihlg

  • mysql
  • 参数
  • binlog

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图