MySQL binlog_ignore_db 参数的具体使用

吾爱主题阅读：99 2024-04-01 23:52:20 评论：0

前言：

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

1. binlog_do_db 与 binlog_ignore_db

当数据库实例开启 binlog 时，我们执行 show master status 命令，会看到有 Binlog_Do_DB 与 Binlog_Ignore_DB 选项。

默认情况下，这两个选项为空，那么这两个参数有何作用？是否如同其字面意思一个只让某个库记录 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全不记录