Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the concept and usage scenario of MySQL BlackHole engine". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Concept

Things like MyISAM and InnoDB,BlackHole are other MySQL engines, literally

It behaves like a black hole, only in and out, and then disappears. 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, and any insert for this table will be lost

An empty set is always returned for its select, and there is only one test.frm file in the corresponding data directory, and no other files are associated with it.

Working with scen

What's the point of an engine that doesn't save any data?

Crucially, although it does not save data, operations to the database are still recorded in the binlog log.

This brings the benefit of using it as an intermediary for master-slave replication, changing the operation that was originally synchronized from the master library to synchronizing from the BlackHole engine database acting as the 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 will load data from the master library, which will increase the burden on the master library. However, if you synchronize from the pseudo-master library of BlackHole, you can reduce the burden on the master library. The original master-slave architecture looks like this:

Now, the BlackHole pseudo-master library acts as an intermediary and looks like this:

In particular, replicate-do and replicate-ignore rules can be configured in the pseudo-main library to filter tables that do not require synchronization.

two。 As a binlog log collector

It does not save the actual data, but only records the characteristics of binlog, which makes the engine can be used for binlog log collection and facilitate database analysis.

Related knowledge: there are three kinds of format for binlog logs: row,statement,mixed.

Row records each row changed, that is, update records all rows that meet the conditions, and alter table is even worse, which is equivalent to rebuilding the entire table and recording all row changes. Therefore, the log is easy to be too large in this format.

The statement method only records the SQL that changes the data, and there is no row problem, but it will record the context information executed by the SQL. A downside is that when the context information is reproduced at the other end, it is easy to be responsible for errors because of the more complex information.

Mixed's way of combining row and statement.

Configuration

In the pseudo library, the following configuration is required:

The default type of configuration is BlackHole, and you can use the

Default_table_type = BLACKHOLE

Or

Default-storage-engine = BLACKHOLE

Open binlog:log-bin = ms-mysql-bin

In particular, configure: log-slave-update = 1, only in this way, the operations in the main library will be synchronized to the binlog of BlackHole, otherwise, only operations directly against BlackHole will be recorded in binlog.

Ignore InnoDB:skip-innodb, and the default BlackHole engine will be used when the table statement has engine=innodb.

It should be reminded that when adopting this architecture, there is an additional middle layer of data synchronization, and latency needs to be further considered.

This is the end of the introduction to the concept and usage scenario of the MySQL BlackHole engine. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.