Is it Possible to Recover Data from a BLACKHOLE table? [closed]

Question

I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result.

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam. but i had tried that also but unable to get the result. Can any one pl help me on this issue to fix it.

mysql> CREATE TABLE test1(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test1;
Empty set (0.00 sec)

mysql>  CREATE TABLE test_recovery as select * from test1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_recovery ENGINE = innodb;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM test_recovery;
Empty set (0.00 sec)

Answer 1

You said

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam

Whoever told you that should have told you to enable binary logs as @jynus commented.

The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups.

EXAMPLE #1 : Star Topology

Some have Star Topologies to write data to a MySQL Replication Master. The Master has nothing but BLACKHOLE tables. All Slaves have InnoDB or MyISAM. That way, if the Master gets hacked, there is no local data. Such a Master is known as a Distribution Master.

• Apr 28, 2011 : MySQL in star topology (ServerFault)
• Mar 11, 2011 : MySQL in star topology (StackOverflow)

EXAMPLE #2 : Single Slave, Multiple Masters

This involves setting up Circular Replication with three servers. Two Masters with a mix of BLACKHOLE tables and a Slave with all real tables. This technique predates multisource replication.

• Apr 18, 2013 : Single slave - multiple master MySQL replication
• May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB? (ServerFault)

EXAMPLE #3 : Increase Write Performance

I knew of a Web Hosting Client who went to Percona for performance issues on one table. Since the client had MySQL Replication with Multiple Slaves, Percona converted the problem table to BLACKHOLE. This increased write performance on the Master. The data had to be read from the Slaves.

I once suggested this in a post for recording audit information (Performance of a Trigger vs Stored Procedure in MySQL)

EPILOGUE

While some of the comments remind me of StarTrek DS9, the BLACKHOLE Storage Engine is what its name says: NO DATA IS EVER STORED !!! If you had binary logs enabled, that would have been your only chance of having data.