I spent the better part of today attempting to import a 10GB MySQL database that occupies around 17GB if imported correctly.
This dataset was given to me in its current condition and a regular import yields the following as mysql does some sanity checks re. data integrity etc.
[ERROR in query 2150] Cannot add foreign key constraint
[ERROR in query 2152] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2153] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2154] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2155] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2156] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2157] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2158] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2159] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2160] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2161] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2162] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2163] Table 'foo.keywords_titles' doesn't exist
[ERROR in query 2164] Table 'foo.keywords_titles' doesn't exist
*I've obviously renamed the database as 'foo' in the above snippet!
The solution was to prepend and append the following around the dumped SQL before performing the import, thereby getting mysql to stop its sanity checks. Disabling AUTOCOMMIT and performing this at the very end is also a timesaver.
SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
# teh SQL goes here...
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
SET AUTOCOMMIT=1;
COMMIT;
You can find my completed script on github.
Usage is simply ./restore_dump.sh dump.sql app_devdb. If app_debdb does not exist, it will create that for you and proceed with the import. You will of course need to create/grant users and their permissions etc. Root mysql login is assumed and you will be prompted for the password.
Happy forking!