If I had planned ahead, I would have generated my mysqldump file with the --add-drop-table command line option to add the DROP TABLE IF EXISTS `tablename` but I didn’t, so when my 39GB mysqldump file had a Foreign key constraint error on line 34233, I would have had to generate a whole new one to fix that problem and it came in from a remote database, so it took hours to generate this one. Fortunately it’s not very complicated to make sed do that work to the file in the first place.

sed -i 's/CREATE TABLE `\(.*\)`/DROP TABLE IF EXISTS `\1`;\nCREATE TABLE `\1`/' alldatabases.mysqldump

Caveat here is that you should be sure you have enough disk space before you run sed -i with no file extension

The command looks for

CREATE TABLE `tablename`

and then replaces it with

DROP TABLE IF EXISTS `tablename`;
CREATE TABLE `tablename`

Worked really quickly, too and now I can fix my foreign constraint error and get this database migrated. (The foreign key constraint appears to have been caused because I ran mysqldump with –compact which removes any impossibilities for importing, since a table with a foreign key could be imported before the key it’s linking to, also trivial to solve once I figured that out.)

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>