MySQL: The BLACKHOLE Storage Engine, Perform Dummy Insert on Table

Have you ever wished that you do not want to insert any data, but want to execute INSERT command ?

This is looking like a very strange requirement, but this is possible in MySQL.

MySQL has a BLACKHOLE Storage Engine which we can use for the empty or the dummy INSERT on Table.

When we created a BLACKHOLE table, It creates with only .frm table format file and It has no any other file associated with it.

When you execute your INSERT on BLACKHOLE Table, It stores only binary log and It does not store any kind of data.

When we should use BLACKHOLE Storage Engine?

When we require to measure the performance only on the binary logging process. Mostly we require to test binary logging performance for bulk operations.

When we require to find performance bottlenecks not related to disk-storage.

When we require slave-side data validation and filtering, we can create a dummy BLACKHOLE table at the master-side which reduces the overall traffic.

Check the status of BLACKHOLE Engine:

Create BLACKHOLE table:

Try to insert few records:

Check inserted data, which return Empty Set:

Anvesh Patel

Leave a Reply

1 Comment on "MySQL: The BLACKHOLE Storage Engine, Perform Dummy Insert on Table"

Notify of
Sort by:   newest | oldest | most voted
kuldeep sirohi

this is actually beneficial when there are multiple slave and instead of connecting to the main master server you connect them to a log server ” which is actually having tables in blackhole”. this reduces the load on master server upto some extent.