Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning.

How can I tell the mysql command line to ignore the partitioning information in the dump file?

Notes: The dump file isn't created by me. Its an auto generated backup so creating it without partitions is not an option.

The file is too large to edit manually. But adding a line to it is an option.

Thanks !

share|improve this question
up vote 1 down vote accepted

If your target database is an older MySQL version such as MySQL 5.0, then partitioning commands will already be ignored since they are wrapped in comments like this:

(PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB,
 PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB) */;

If you are loading into MySQL 5.1 or higher, then one trick that came to my mind is to change the version number in the comment section. You do not have to manually edit the dump file, you can process the file through sed. For example:

sed 's\!50500 PARTITION\!60000 PARTITION\g' dumpfile.sql | mysql
share|improve this answer
That's slick and makes sense. +1 !!! – RolandoMySQLDBA May 14 '12 at 18:49
Sorry, ran of of votes. I'll upvote tomorrow. – RolandoMySQLDBA May 14 '12 at 18:50
Thanks @RolandoMySQLDBA! – dabest1 May 14 '12 at 21:57
As promised, +1 !!! – RolandoMySQLDBA May 15 '12 at 0:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.