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