How much do you know about the MySQL BlackHole engine?

MySQL BlackHole engine

Concept

Like MyISAM and InnoDB, BlackHole is another MySQL engine. From a literal meaning,
it behaves like a black hole , you can only get in but not get out. Once you get in, you disappear. In other words, any data written to it will be lost, a bit like Linux's /dev/null
For example, the engine of a table test is BlackHole, any insert to this table will be lost,
its select always returns an empty set. There is only one test.frm file in the corresponding data directory and no other files are associated with it.

Usage scenarios

What is the point of an engine that does not save any data?
The key is that although it does not save data, the operations on the database are still recorded in the binlog log.
This brings a benefit, it can be used as an intermediary for master-slave replication, and the original synchronization operation from the main database can be changed from the BlackHole engine database as an intermediary.

1. Share the burden of the main library as a pseudo-master library

As we all know, when there are many slave libraries, all slave libraries load data from the main library, which will increase the burden on the main library. But if you synchronize from BlackHole's pseudo-main library, you can reduce the burden on the main library. The original master-slave architecture probably looks like the following:

现在，BlackHole伪主库作为中介，变成这样：

In particular, replicate-do and replica-do can be configured in the pseudo-master library The replicate-ignore rule filters tables that do not need to be synchronized.

2. As a binlog log collector

It does not save actual data, but only records the characteristics of binlog, so that the engine can be used for binlog log collection to facilitate database analysis.
Related knowledge: There are three formats for binlog logs: row, statement, and mixed.
row records the changed records of each row. In other words, update will record all modified rows that meet the conditions. Alter table is even worse, which is equivalent to rebuilding the entire table and recording all rows. Change. Therefore, the log in this format is easy to be too large; the
statement method only records the SQL that changes the data. There is no problem with the row method, but it will record the context information of the SQL execution, which has a disadvantage. The problem is that when the context information is reproduced at the other end, it is easy to make errors due to more complex information.
mixed combines row and statement methods.

Configuration

In the pseudo library, the following configuration is required:
The default type of configuration is BlackHole, you can use
default_table_type = BLACKHOLE
or
default-storage -engine = BLACKHOLE
Open binlog: log-bin = ms-mysql-bin
Specially configure: log-slave-update = 1. Only in this way, the operations in the main library will be synchronized to BlackHole's binlog , otherwise, only operations directly targeting BlackHole will be recorded to the binlog.
Ignore InnoDB: skip-innodb. When the table creation statement contains engine=innodb, the default BlackHole engine will be used.
It should be reminded that when this architecture is adopted, there is an additional middle layer for data synchronization, and the delay issue needs to be further considered.

Recommended study: "mysql video tutorial"

The above is the detailed content of How much do you know about the MySQL BlackHole engine?. For more information, please follow other related articles on the PHP Chinese website!