关于mysql:MySQL-binlogignoredb-参数最全解析

44次阅读

共计 11247 个字符，预计需要花费 29 分钟才能阅读完成。

前言：

通过后面文章学习，咱们晓得 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
发表至： mysql
2020-12-04
 0
关于mysql:关于MySQL-function创建的限制
关于mysql:msyql-触发器
关于mysql:墨天轮高分技术文档分享数据库安全篇共48个
关于mysql:故障分析-记一次-MTS-并行复制导致的死锁排查