The version of phpMyAdmin on Rackspace Cloud Sites has a hard limit of 16Mb for imported SQL. I recently had to migrate a database that was over 500Mb.
The solution was simple but tedious, export the database schema first and import it in phpMyAdmin then separate the data into many separate files below 16Mb.
I came up with a lot of ridiculous solutions using python, perl, combinations of science and voodoo until I realized the solution already existed, Linux. Utilizing the split and sed we can split the database dump into many small parts.
Split
In order to split the file effectively it will take a little experimentation (this is really fast though so it shouldn’t take long to figure out).
split -l 40000 big_giant_db.dump
Will split a file into individual files with 40,000 lines each. Splitting this way will yield files of varying sizes and depending on the data you may still have files over 16Mb which will need to be dealt with in the same way (kind of like recursion, no?).
Processing
I needed to disable foreign key constraints and wrap each file in a transaction but editing 90 individual files is a pain in the butt, so let’s skip that and do it with sed!
The following command will build a list of files starting with ‘x’, which is the output from split, and run sed against them to place text in the beginning of the file.
find ./x* -type f -print0 | while IFS= read -r -d '' filename;\
do sed -i '1s/^/\n\nSET FOREIGN_KEY_CHECKS=0;\nSET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\nSET AUTOCOMMIT = 0;\nSET time_zone = \"+00:00\";\nSTART TRANSACTION;\n\n /' $filename;\
done
Holy crap! You thought you had escaped your sed and perl nightmares, didn’t you?
The output will look something like this
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
SET time_zone = "+00:00";
START TRANSACTION;
To finish up we need to add the sql commit and re enable foreign key checks, so let’s get to it.
find ./x* -type f -print0 | while IFS= read -r -d '' filename;\
do sed -i '$s/$/\n\nCOMMIT;\nSET FOREIGN_KEY_CHECKS=1;\n\n /' $filename;\
done
Like the command before, this will append text to the bottom of each file generated by the find command.
The output will look something like this
COMMIT;
SET FOREIGN_KEY_CHECKS=1;
I hope this helped someone else, I apologize for formatting. If you know a better way to do this send me an email, twitter, comment, pull request, or smoke signal.
Update
So, had I read the damn Rackspace Cloud Sites KB in the first place I would have known that it is possible to import a database through a shell script
#!/bin/sh
mysql -h host-u user -p'password' db_name < /path/to/backup.sql