Search code examples
mysqldatabase-partitioning

How can I dump my DB with mysqldump and make it skip the table partition info?

I have a production DB that I'm constantly dumping into my QA environment. The production DB has a few tables that are partitioned. On my QA environment I have no need for those tables to be partitioned since I don't import all the live data.

I'd like to perform a mysqldump command that will output the tables' CREATE info without the partition statement (i.e. without "PARTITION BY ...").

Solution

  • I haven't found an elegant way to strip partitions. I've been using the following bit of regex hackery:

    perl -i -pe 's/^\/\*![0-9]* PARTITION BY.*$/;/' foo.sql

perl -i -pe 's/^.PARTITION.*ENGINE = .*$//' foo.sql

    The first regex removes the opening PARTITION BY and replaces it with a semicolon to close off the preceeding CREATE TABLE.

    The second regex removes all the partition declarations.