mysql binlog如何查看(MySQL binlog_ignore_db 参数的具体使用)
mysql binlog如何查看
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。
看了这么多实验数据,你是否眼花缭乱了呢,下面我们以思维导图的形式总结如下:
这么看来 binlog_ignore_db 参数的效果确实和诸多因素有关,特别是有从库的情况下,主库要特别小心使用此参数,很容易产生主从同步错误。不过,按照严格标准只对当前数据库进行操作,则不会产生问题。这也告诉我们要严格按照标准来,只赋予业务账号某个单库的权限,也能避免各种问题发生。
总结:
不清楚各位读者是否对这种介绍参数的文章感兴趣呢?可能这些是数据库运维人员比较关注的吧。本篇文章主要介绍关于 binlog 的 binlog_ignore_db 参数的具体作用,可能本篇文章实验环境还不够考虑周全,有兴趣的同学可以参考下官方文档,有助于对该参数有更深入的了解。
以上就是MySQL binlog参数的使用的详细内容,更多关于MySQL binlog参数的资料请关注开心学习网其它相关文章!
原文链接:https://mp.weixin.qq.com/s/Xwwr1HJKRglNH9V-Ddihlg
- mysql的事务隔离级别怎么实现(mysql、oracle默认事务隔离级别的说明)
- mysql自定义安装教程5.7(MySQL系列-源码编译安装v5.7.34)
- mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)
- mysql死锁情况(MySQL kill不掉线程的原因)
- innodb和myisam(Mysql InnoDB和MyISAM区别原理解析)
- mysqlbinlog优缺点(MySQL Binlog 日志处理工具对比分析)
- mysql8修改默认端口(MySQL 8.0新特性 — 管理端口的使用简介)
- mysql安装时服务无法启动(MySQL 实例无法启动的问题分析及解决)
- mysql怎么创建联合索引(mysql的联合索引复合索引的实现)
- mysql binlog模式实际使用(实例验证MySQL|update字段为相同的值是否会记录binlog)
- 阿里云mysql如何接入(阿里云服务器安装Mysql数据库的详细教程)
- MySQL中NULL和空值的区别
- mysql8.0.18.0安装详细教程(mysql 8.0.22 下载安装配置方法图文教程)
- mysql中date_format日期格式化
- mysql操作时间的函数(一篇文章搞定Mysql日期时间函数)
- 利用xampp搭建服务器(xampp集成环境怎样使用MySQL数据库)
- 法国面包(法国面包法棍)
- 微信(微信分身)
- 双十二(双十二和双十一哪个划算)
- 佛肚竹盆景的养护之道(佛肚竹盆景的养护之道)
- 包水饺(包水饺手法怎么包视频)
- 越南河粉(越南河粉来自哪里)
热门推荐
- ASP.NET私有构造函数作用
- servlet与tomcat区别(深入了解tomcat中servlet的创建方式实现)
- dedecms数据库备份(dedecms网站搬家需要的备份的文件)
- 定时删除linux日志(Linux使用shell脚本定时删除历史日志文件)
- JavaScript 常用的开发规范
- laravel 数据表格(Laravel自定义 封装便捷返回Json数据格式的引用方法)
- centos7宝塔面板离线安装(linux centos 宝塔面板和安全狗安装过程记录)
- docker配置redis集群(基于Docker搭建Redis主从集群的实现)
- thinkphp5.1修改(ThinkPHP 5.1 跨域配置方法)
- 面向对象设计中的单一职责原则