how to verify mysql backup file using BlackHole

Question

I'm interested in trying this solution: https://dba.stackexchange.com/a/44893/71052, also referenced here: https://dba.stackexchange.com/a/206151/71052

The problem I face is the following:

• mysqldump exports explicitly the storage engine for each table:

 CREATE TABLE `footable` ( 
 `foocolumn1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
 `foocolumn2` bigint(20) unsigned NOT NULL DEFAULT '0', 
 ... 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; 

• when I'm restoring the dump, BlackHole is set as default engine, still the table is created as explicitly stated in the dump - which actually seems quite normal.

-> Is there a way to force an engine substitution? (I mean as a restore option, editing the dump is no solution here)
-> I suppose the solution is is the quoted post:

Start a dedicated Mysql server with as default engine blackhole (and maybe the only one)

How can I start a mysqld instance with Blackhole as the only available engine? I saw lots of docs about compiling with or without this or that engine, but I haven't found so far how to just start it without innodb, myisam etc...

• Akina: Is there a way to force an engine substitution? No. You'd edit your dump file with any text editor and replace all occurencies of explicit engine specifyings (or remove them if the engine you want to used is the default one).
• Rick James: Percona and MariaDB have the VARIABLE enforce_storage_engine -- maybe this would help? mariadb.com/kb/en/server-system-variables/…
• fpierrat: @RickJames I think this is exactly what I would need... but I have to do it on mysql :-(
• fpierrat: @Akina Thanks, this at least clears one point. Now could someone help me find doc about how to load mysqld with only Blackhole as available engine? Then the substitution would still be done according to the first quoted post in my PO...
• fpierrat: Finally found it at last: disabled_storage_engines system variable - dev.mysql.com/doc/refman/8.0/en/…

Answer 1

I finally found the way: in the [mysqld] section of my.ini or my.cnf:

1. exclude NO_ENGINE_SUBSITUTION from line sql_mode=...
   ex.: sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

2. set blackhole as default engine: default-storage-engine=blackhole

3. use disabled_storage_engines to disable all other available engines
   ex.: disabled_storage_engines="MyISAM,InnoDB"

Now all tables created will have the default BLACKHOLE engine (with just a warning).

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_disabled_storage_engines

For those using MariaDB or Percona, see enforce_storage_engine as mentionned by @RickJames: https://mariadb.com/kb/en/server-system-variables/#enforce_storage_engine

---

[Edit] I wanted to do this so I can test dumps validity. But I figured out this is not a correct way to do this (correct me if I did something wrong...):

• it seems ok if you're just checking the formal, syntax validity of the dump (if corrupted, you may find out because of syntax breaking bad)
• but it doesn't check anything more than syntax. In particular, I tested modifying my dump to put string values (such as "xyzfoobar", not just "123") into integer fields, to remove loads of rows breaking integrity (because of missing external keys) etc...
  Since nothing is actually written, nothing can actually get checked.

So unfortunately, I lost quite some time on this but it doesn't answer my needs of verifying the actual restorability of a dump :-(