Specific use of MySQL binlog_ignore_db parameter

Preface:

After studying the previous article, we know that binlog will record all DDL and DML statements executed in the database (except data query statements such as select and show). Note that by default, all library operations will be recorded. If we have alternative requirements, such as only allowing a certain library to record binglog or excluding a certain library from recording binlog, does it support such requirements? Let’s take a look at this article together.

1. binlog_do_db and binlog_ignore_db

When binlog is enabled for a database instance, we execute the show master status command and see the Binlog_Do_DB and Binlog_Ignore_DB options.

mysql> show master status;
+---------------+----------+-------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000009 | 282838 | | | |
+---------------+----------+-------------+------------------+-------------------+

By default, these two options are empty, so what do these two parameters do? Is it as its literal meaning, one only allows a certain library to record binglog, and the other excludes a certain library from recording binlog? The author consulted the official documentation and briefly explained the functions of these two parameters:

binlog_do_db: This parameter means that only the binary logs of the specified database are recorded. By default, all logs are recorded.
binlog_ignore_db: This parameter indicates that the binary log of the specified database is not recorded.

These two parameters are mutually exclusive. Generally, only one of them is selected and can only be added in the startup command line or configuration file. Specify multiple databases to be written in separate rows, as follows:

# Specify db1 db2 to record binlog
[mysqld]
binlog_do_db = db1
binlog_do_db = db2

# Prevent db3 and db4 from recording binlog
[mysqld]
binlog_ignore_db = db3
binlog_ignore_db = db4

In addition, the specific effect of these two parameters is also related to the binlog format. In some cases, setting the binlog format to STATEMENT or ROW will have different effects. In actual applications, binlog_ignore_db is more widely used. For example, if the data in a certain database is not very important, in order to reduce the writing pressure on the server, we may not let the database record binlog. There are also articles online saying that setting binlog_ignore_db will cause slave synchronization errors, so what effect does setting this parameter have? Let's do a specific experiment.

2. Specific effects of binlog_ignore_db

First of all, my test database instance is 5.7.23 community version, with two business databases, testdb and logdb. We set logdb not to record binlog. Let's do a specific experiment:

# binlog is in ROW format

 # 1. Do not use 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 | |
+---------------+----------+-------------+------------------+-------------------+
# Conclusion: Other libraries record normally. The logdb library records DDL but not DML.

# 2. Use use testdb across databasesmysql> 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)
# Conclusion: The logdb library also records DDL but not DML 

# 3. Use use logdb across databasesmysql> 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)
# Conclusion: logdb does not record any DDL of other libraries

# 4. Each operation enters this database and does not cross databasesmysql> 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)
# Conclusion: All other libraries record, but logdb does not record

Similarly, set the binlog format to STATEMENT and test again. I will not go into details about the test process here, but summarize the experimental results under the STATEMENT format:

No database was selected for operation, all will be logged.
Select testdb, and perform operations on testdb and logdb respectively. All libraries will be recorded.
Select logdb, and operate on testdb and logdb respectively. All libraries will not be recorded.
If you select a library and operate only on the current library, the record will be normal and logdb will not be recorded.

Are you dazzled by so much experimental data? Let's summarize it in the form of a mind map as follows:

It seems that the effect of the binlog_ignore_db parameter is indeed related to many factors. Especially when there are slave libraries, the master library should be particularly careful when using this parameter, as it is easy to cause master-slave synchronization errors. However, if you follow strict standards and only operate on the current database, there will be no problem. This also tells us that we must strictly follow the standards and only grant the business account permissions for a single database, which can also avoid various problems.

Summarize:

I wonder if you readers are interested in this kind of article introducing parameters? Maybe these are what database operation and maintenance personnel are more concerned about. This article mainly introduces the specific role of the binlog_ignore_db parameter of binlog. The experimental environment of this article may not be comprehensive enough. Interested students can refer to the official documentation to help them have a deeper understanding of this parameter.

The above is the detailed content of the use of MySQL binlog parameters. For more information about MySQL binlog parameters, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

MySQL series: redo log, undo log and binlog detailed explanation
How to choose the format when using binlog in MySQL
Detailed explanation of the binlog log analysis tool for monitoring MySQL: Canal
In-depth explanation of binlog in MySQL 8.0
Summary of some thoughts on binlog optimization in MYSQL
Detailed explanation of MySQL database binlog cleanup command
Steps to enable MySQL database monitoring binlog
How to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog

<<: ul list tag design web page multi-column layout

>>: js realizes the magnifying glass effect of shopping website products