Skipping the DROP TABLE, CREATE TABLE statements in a large mysqldump file.

Posted on April 28th, 2010 in General,Linux System Administration,MySQL,Programming by Brandon

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 Responses to 'Skipping the DROP TABLE, CREATE TABLE statements in a large mysqldump file.'

Subscribe to comments with RSS or TrackBack to 'Skipping the DROP TABLE, CREATE TABLE statements in a large mysqldump file.'.

  1. unclebob said,

    on May 5th, 2010 at 5:55 am

    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.

  2. Brandon said,

    on June 13th, 2010 at 8:49 pm

    @unclebob,

    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.

Post a comment

Please copy the string 2Z1e7t to the field below: