MySQL BLACKHOLE Storage Engine

Summary: in this tutorial, you will learn about MySQL BLACKHOLE storage engine and its application in a replication setup.

Introduction to MySQL BLACKHOLE storage engine

Unlike other storage engines, the BLACKHOLE storage engine doesn’t store table data. This means that when you send data to the BLACKHOLE tables, they immediately discard the data.

To check if the current MySQL server supports the BLACKHOLE storage engine or not, you use the following query:

SELECT 
  engine, 
  support 
FROM 
  information_schema.engines 
ORDER BY 
  engine;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+---------+
| engine             | support |
+--------------------+---------+
| ARCHIVE            | YES     |
| BLACKHOLE          | YES     |
| CSV                | YES     |
| FEDERATED          | NO      |
| InnoDB             | DEFAULT |
| MEMORY             | YES     |
| MRG_MYISAM         | YES     |
| MyISAM             | YES     |
| ndbcluster         | NO      |
| ndbinfo            | NO      |
| PERFORMANCE_SCHEMA | YES     |
+--------------------+---------+
11 rows in set (0.00 sec)Code language: plaintext (plaintext)

If your MySQL server supports the BLACKHOLE storage engine, you can start using it.

First, create a new table that uses the BLACKHOLE storage engine:

CREATE TABLE products(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE = 'BLACKHOLE';Code language: SQL (Structured Query Language) (sql)

Second, insert a row into the products table:

INSERT INTO products(name)
VALUES('Flagship product');Code language: SQL (Structured Query Language) (sql)

Third, query data from the products table:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

Empty set (0.00 sec)Code language: JavaScript (javascript)

It returns no rows because the blackhole tables do not store the data.

The question is, why does the BLACKHOLE storage engine exist in the first place, and when can you use it?

The BLACKHOLE storage engine can be useful in some scenarios such as replication.

Using the BLACKHOLE storage engine in a replication setup

In MySQL, replication is a mechanism that allows you to replicate data changes from one MySQL server (the “master”) to one or more other MySQL servers (the “slaves”).

Typically, you use replication for various purposes, including load balancing, high availability, and data backup.

When you use replication, you want the data on the slave servers to be consistent with the data on the master server.

The BLACKHOLE storage engine can be useful in replication setups, specifically when you want to capture data changes on the master server without storing that data on the local server.

On the master server, you create a table using the BLACKHOLE storage engine. This table doesn’t store any data and simply discards any data inserted into it.

CREATE TABLE sample_tables (
  id INT AUTO_INCREMENT PRIMARY KEY, 
  data_column VARCHAR(255)
) ENGINE = BLACKHOLE;Code language: SQL (Structured Query Language) (sql)

Whenever you insert, update, or delete data the sample_tables on the master server, MySQL captures these operations in the binary log. The binary log is a record of all data modifications, and it’s a fundamental component of MySQL replication.

MySQL then replicates the binary log to one or more slave servers. On the slave servers, you can use a different storage engine (e.g., InnoDB or MyISAM) to create a table with the same structure as the sample_tables. MySQL will relay the data changes from the binary log on the slave, effectively making the data on the slave server consistent with the master.

The key point here is that the slave servers use a different storage engine (e.g., InnoDB or MyISAM) to store the replicated data. The BLACKHOLE storage engine on the master server doesn’t store the data locally, so there is no unnecessary storage overhead on the master.

This setup allows you to capture and replicate data changes without keeping the data locally on the master server. It can be useful when you want to maintain data consistency across multiple servers while minimizing the storage requirements on the master.

Summary

In MySQL, the BLACKHOLE storage engine does not store table data.
Use the BLACKHOLE storage engine in a replication without keeping data locally on the master server.

Was this tutorial helpful?