mysqlq binlog_ignore_db 介绍

 

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。

 本文摘自：https://www.jb51.net/article/201609.htm