MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

I am not aware of any tools to do exactly what you are asking right now. I want to suggest a few options you might have overlooked however that may meet your needs.

Database Cloning

If your database is not being used 24/7 you can clone it for test cases. This would be done using during times when the db is not in use:

createdb -U postgres new_dbname -T old_dbname

This does a file-level copy of the database, and so it preserves all data and is much faster than a dump and reload.

I very frequently use this technique for cases where I want to run test cases on one db where I can't guarantee that everything will roll back.

Base Backup and Restore

A second thing you can do if you want to bring all data onto another server is to set it up for replication as a master with at least one wal_sender, and use pg_base_backup to clone your db cluster over the network. Note this is a file-based clone, and so it will be a very network-intensive process (more than a dump) but restore will be a lot faster.

Writing your own Tool

One of the big difficulties in having a generally available tool is that cross-schema dependencies could be quite complex and handling all possible complexity is a bit of a daunting task. However for specific purposes this isn't too hard. I would recommend three steps, using pg_dump to get you most of the way there, and then having a script written in one language or another to add what we can hope are modest missing pieces. I think you could even do it with an sql script run through psql.

1. Dump schemas only of tables you need. Create new file. Something like: pg_dump -t mytable1 -t mytable2 -t mytable3 > my_partial_dump.sql

2. Export selected data to csv, and wrap using COPY statements. This could be done using psql along with echo and COPY TO STDOUT. This is appended to the partial dump.

3. Dump the schema as you are, and append this to the partial dump.

The advantage of a purpose-built tool if this is what you need is that you can manually manage the complex part of it and thus have a smaller, simpler tool than you might otherwise require.

Mock tables were how mysqldump were loaded in older versions of MySQL a long time ago

Issue like this existed

• load of mysqldump script may fail with error 1215 if dest already has tables
• Foreign keys: mysqldump should respect foreign keys

See that last line

2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

Since mysqldumps were in alphabetical order by default, there might be some cases where the parent table of a foreign key relationship would appear after the child table rather than before. As a consequence, the mock table was created to have a parent to adopt the aclGroup table.

Since a child cannot have more than one foreign key reference to the same parent on the same columns, then NDB is ignoring it and still trying to enforce foreign_key_checks=OFF

Under the hood, this is what the NDB storage engine should be doing afterCREATE TABLE aclGroup

ALTER TABLE aclGroup DROP CONSTRAINT aclGroup_fk_aclRole_id;
DROP TABLE `NDB$FKM_20_0_aclRole`;
DROP TABLE IF NOT EXISTS aclRole;
CREATE TABLE aclRole (...) ENGINE=NDBCLUSTER;
ALTER TABLE aclGroup ADD CONSTRAINT aclGroup_fk_aclRole_id
FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;    

If you look at Dump of foreign key constraints with mysqldump not sorted, you will note that this was a feature request for InnoDB long ago. This same feature is probably not properly implemented in NDB. I am sure if you change the NDBCLUSTER to InnoDB in your script and run it, it will work without incident.

So, YES, foreign_key_checks=OFF is being ignored.