MySQL binlog_ignore_db 参数的具体如何使用

前言：

经过前面文章学习，我们知道 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>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000009|282838||||
+---------------+----------+--------------+------------------+-------------------+

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

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

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

#指定db1db2记录binlog
[mysqld]
binlog_do_db=db1
binlog_do_db=db2

#不让db3db4记录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.不使用usedb
mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|154||logdb||
+---------------+----------+--------------+------------------+-------------------+
mysql>selectdatabase();
+------------+
|database()|
+------------+
|NULL|
+------------+
1rowinset(0.00sec)
mysql>CREATETABLEtestdb.`test_tb1`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.06sec)

mysql>insertintotestdb.test_tb1values(1001,'sdfde');
QueryOK,1rowaffected(0.01sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|653||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLElogdb.`log_tb1`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.05sec)

mysql>insertintologdb.log_tb1values(1001,'sdfde');
QueryOK,1rowaffected(0.00sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|883||logdb||
+---------------+----------+--------------+------------------+-------------------+
mysql>insertintologdb.log_tb1values(1002,'sdsdfde');
QueryOK,1rowaffected(0.01sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|883||logdb||
+---------------+----------+--------------+------------------+-------------------+

mysql>altertablelogdb.log_tb1addcolumnc3varchar(20);
QueryOK,0rowsaffected(0.12sec)
Records:0Duplicates:0Warnings:0

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1070||logdb||
+---------------+----------+--------------+------------------+-------------------+
#结论：其他库记录正常 logdb库会记录DDL 不记录DML

#2.使用usetestdb跨库
mysql>usetestdb;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>selectdatabase();
+------------+
|database()|
+------------+
|testdb|
+------------+
1rowinset(0.00sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1070||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLE`test_tb2`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.05sec)

mysql>insertintotest_tb2values(1001,'sdfde');
QueryOK,1rowaffected(0.04sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1574||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLElogdb.`log_tb2`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.05sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1810||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>insertintologdb.log_tb2values(1001,'sdfde');
QueryOK,1rowaffected(0.01sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1810||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)
#结论：同样logdb库会记录DDL 不记录DML 

#3.使用uselogdb跨库
mysql>uselogdb;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>selectdatabase();
+------------+
|database()|
+------------+
|logdb|
+------------+
1rowinset(0.00sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1810||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLEtestdb.`test_tb3`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.23sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|1810||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>insertintotestdb.test_tb3values(1001,'sdfde');
QueryOK,1rowaffected(0.02sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2081||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLE`log_tb3`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.05sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2081||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>insertintolog_tb3values(1001,'sdfde');
QueryOK,1rowaffected(0.02sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2081||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)
#结论：logdb都不记录同时不记录其他库的DDL

#4.每次操作都进入此库不跨库
mysql>usetestdb;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2081||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>CREATETABLE`test_tb4`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.05sec)

mysql>insertintotest_tb4values(1001,'sdfde');
QueryOK,1rowaffected(0.01sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2585||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>uselogdb;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>CREATETABLE`log_tb4`(idint,namevarchar(30))ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.04sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2585||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

mysql>insertintolog_tb4values(1001,'sdfde');
QueryOK,1rowaffected(0.01sec)

mysql>showmasterstatus;
+---------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+---------------+----------+--------------+------------------+-------------------+
|binlog.000011|2585||logdb||
+---------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)
#结论：其他库全部记录 logdb全不记录

同样的，将 binlog 格式设置为 STATEMENT ，再次进行测试，这里不再赘述测试过程，总结下 STATEMENT 格式下的实验结果：

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

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

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

总结：

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

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