I have a large table of test data that I’m copying into some development environments. I exported the table with a mysqldump which has a DROP TABLE and CREATE TABLE statements at the top
DROP TABLE IF EXISTS `mytable`; CREATE TABLE `mytable` ( `somecol` varchar(10) NOT NULL default '', ... other columns ... PRIMARY KEY (`somecol`), KEY `isbn10` (`somecol`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The problem is that the developer has altered the table and re-importing the test data would undo those changes. Editing the text file is impractical because of its size (500 MB gzipped). So I came up with this workaround which just slightly alters the SQL using sed so that it doesn’t try to drop or recreate the table. It comments out the DROP TABLE line, and creates the new table in the test database instead of the real database.
zcat bigfile.sql.gz |sed "s/DROP/-- DROP/"|sed "s/CREATE TABLE /CREATE TABLE test./"|mysql databasename
2 thoughts on “Skipping the DROP TABLE, CREATE TABLE statements in a large mysqldump file.”
Wouldn’t it have been easier to just import in test directly, like this:mysql test <bigfile.sql? That was it doesn't delete anything and you get the exact same effect.
The sed command just creates the new table in the test database but imports all of the data into the real database where it would normally go. It was difficult to eliminate the whole CREATE TABLE statement, so I just had it create the table in the test database where it will never be used.