mysql binlog ignore db_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> 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。

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

8788ecf1cbbaeaa2a0106b05f30ec23d.png

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

总结:

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

以上就是MySQL binlog参数的使用的详细内容,更多关于MySQL binlog参数的资料请关注脚本之家其它相关文章!

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
可以通过以下步骤查看MySQLbinlog位置的pos位置: 1. 登录MySQL命令行客户端。 2. 执行以下命令查看当前正在使用binlog文件和位置: ``` show master status; ``` 执行结果类似于以下内容: ``` +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.000001 | 107 | test | | +------------------+-----------+--------------+------------------+ ``` 其中,File列显示的是当前使用binlog文件名,Position列显示的是当前文件的pos位置。 3. 如果需要查看历史的binlog文件和位置,可以执行以下命令: ``` show binary logs; ``` 执行结果类似于以下内容: ``` +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | | mysql-bin.000002 | 107 | | mysql-bin.000003 | 107 | +------------------+-----------+ ``` 其中,Log_name列显示的是binlog文件名,File_size列显示的是文件大小。 4. 如果需要查看某个binlog文件的pos位置,可以执行以下命令: ``` show binlog events in 'mysql-bin.000001'; ``` 执行结果类似于以下内容: ``` +------------------+-----+----------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+----------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.7.18-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Previous_gtids | 1 | 196 | | | mysql-bin.000001 | 196 | Anonymous_Gtid | 1 | 227 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 227 | Query | 1 | 329 | use `test`; INSERT INTO `users` VALUES (1,'Alice'); | +------------------+-----+----------------+-----------+-------------+----------------------------------------------------+ ``` 其中,Log_name列显示的是binlog文件名,Pos列显示的是pos位置。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值