Importing HUGE databases

Assumptions:

1. Huge means over 1GB or possibly 1TB (not tested) on MySQL.

2. This means that using tools like PhpMyAdmin, etc are not feasible (timeout, php.ini constraints, etc)

Problems:

1. MySQL dumps are NOT done in order of table dependencies. So it is highly possible that your dependent child table is created before the parent table is created in the SQL script. This is especially more so if you have a lot of tables in the database.

Solution:

1. Need to disable the foreign key constraint checks.

Constraint:

1. You can open up a 1GB file for editing, unless you have uber cool tools like UltraEdit on a Windows machine (almost nothing else works - vim in cygwin, vim in *nix, I dunno but please correct if I'm wrong).

Reference:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

How?

mysql> SET foreign_key_checks = 0; 

mysql> SOURCE dump_file_name; 

mysql> SET foreign_key_checks = 1;

That's it, more or less.