Blackhole

This storage engine accepts data but does not store it. It discards data after every INSERT instead of storing it.

The following example shows the BLACKHOLE table process:

mysql> CREATE TABLE user(id INT, name CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO USER VALUES(1,'Kandarp'),(2,'Chintan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 
mysql> SELECT * FROM USER;
Empty set (0.00 sec)

Now what is the use of this storage engine? Why would anybody use it? Why would we run an INSERT query that doesn't insert anything into the table?

This engine is useful for replication with large amount of servers. A blackhole storage engine acts as a filter server between the master and slave server, which do not store any data but only apply replicate-do-* and replicate-ignore-* rules and write a binlogs. These binlogs are used to perform replication in slave servers. We will discuss this in detail in Chapter 7, Replication for building highly available solutions.