1

I need to load a database backup from some months ago.There are 29 tables backed up as individual.sql files. Is the order I load them important? For instance the ClientSupplementary table has a FK into the Client Table so that if the Client is deleted the Supplementary data is as well. So does the Client have to be loaded before their Supplementary information?

3

Usually yes, when foreign keys are involved, you have to create them in the right order.

When you are sure, however, that your data is consistent, you can do it like this:

mysql> USE your_schema;
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> SOURCE /path/to/your/dump/file1.sql
mysql> SOURCE /path/to/your/dump/file2.sql
mysql> # ...
mysql> SET FOREIGN_KEY_CHECKS=1;

Or on the command line:

shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=0;"
shell> cat file1.sql file2.sql | mysql -uuser your_schema
shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=1;"
  • 1
    I find that it is preferable to load the data in the correct order to eliminate surprises later on! Loading bad data that subsequently fails is worse than failing straight away - check out what Ken Thomson has to say on failing early and loudly! – Vérace Nov 29 '17 at 19:25
  • Absolutely. I should have emphasized that more. – tombom Nov 30 '17 at 9:35
  • No excuse now for not updating the schema,PK and FK, and relationship documentation. I had it all up to-date but someone amended the DB but not the documentation! – MichaelJohn Nov 30 '17 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.