前言：
经过前面文章学习，我们知道 binlog 会记录数据库所有执行的 ddl 和 dml 语句（除了数据查询语句select、show等）。注意默认情况下会记录所有库的操作，那么如果我们有另类需求，比如说只让某个库记录 binglog 或排除某个库记录 binlog ，是否支持此类需求呢？本篇文章我们一起来看下。
1. binlog_do_db 与 binlog_ignore_db
当数据库实例开启 binlog 时，我们执行 show master status 命令，会看到有 binlog_do_db 与 binlog_ignore_db 选项。
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：此参数表示不记录指定的数据库的二进制日志。
这两个参数为互斥关系，一般只选择其一设置，只能在启动命令行中或配置文件中加入。指定多个数据库要分行写入，举例如下：
# 指定 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 ，下面来具体实验下：
# 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。
看了这么多实验数据，你是否眼花缭乱了呢，下面我们以思维导图的形式总结如下：
这么看来 binlog_ignore_db 参数的效果确实和诸多因素有关，特别是有从库的情况下，主库要特别小心使用此参数，很容易产生主从同步错误。不过，按照严格标准只对当前数据库进行操作，则不会产生问题。这也告诉我们要严格按照标准来，只赋予业务账号某个单库的权限，也能避免各种问题发生。
总结：
不清楚各位读者是否对这种介绍参数的文章感兴趣呢？可能这些是数据库运维人员比较关注的吧。本篇文章主要介绍关于 binlog 的 binlog_ignore_db 参数的具体作用，可能本篇文章实验环境还不够考虑周全，有兴趣的同学可以参考下官方文档，有助于对该参数有更深入的了解。
以上就是mysql binlog参数的使用的详细内容，更多关于mysql binlog参数的资料请关注www.887551.com其它相关文章！