Understanding MySQL BlackHole Storage Engine: Concepts, Use Cases, and Configuration

Concept

MyISAM, InnoDB, and BlackHole are MySQL storage engines. BlackHole behaves like a black hole: any data written to it disappears, similar to Linux's /dev/null.

For example, a table test using the BlackHole engine will lose every INSERT, SELECT always returns an empty set, and only the test.frm file exists in the data directory.

Use Cases

Although it does not store data, operations on a BlackHole engine are still recorded in the binlog, allowing it to serve as an intermediary in master‑slave replication.

1. Acting as a Pseudo‑Master to Offload the Real Master

When many slaves pull data from the master, the master’s load increases. By inserting a BlackHole pseudo‑master, slaves replicate from this intermediate server, reducing the master’s burden. The original architecture and the revised architecture are illustrated below.

In the pseudo‑master you can configure replicate-do and replicate-ignore rules to filter tables that do not need to be synchronized.

2. Binlog Collector

Because it records binlog without storing actual data, the BlackHole engine can be used to collect binlog for database analysis.

Related Knowledge

MySQL binlog has three formats: row , statement , and mixed .

Row format logs every changed row, which can cause large binlog files for massive updates or ALTER TABLE operations.

Statement format logs only the SQL statements, avoiding the size issue but requiring the same execution context on the replica, which may lead to errors for complex statements.

Mixed format combines both row and statement approaches.

Configuration

In the pseudo‑master, set the default storage engine to BlackHole: default_table_type = BLACKHOLE or default-storage-engine = BLACKHOLE Enable binlog: log-bin = ms-mysql-bin Crucially, set the following so that updates on the master are also written to the BlackHole binlog: log-slave-update = 1 To ignore InnoDB tables and force them to use BlackHole, add: skip-innodb When a CREATE TABLE statement includes engine=innodb, the table will be created with the BlackHole engine instead. Note that introducing this intermediate layer may increase replication latency and should be considered in the overall architecture.