mysqldump - Export structure only without autoincrement

mysql export mysqldump database-schema auto-increment
Paris picture Paris · Mar 27, 2013 · Viewed 42.6k times · Source

I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. mysqldump --no-data would almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?

Answer

JoDev picture JoDev · Mar 27, 2013

You can do this : 

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql

As mentioned by others, If you want sed to works properly, add the g (for global replacement) parameter like this : 

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' > <filename>.sql

(this only works if you have GUI Tools installed: mysqldump --skip-auto-increment)

New UPDATE thanks to comments.

The \b is useless and sometimes will break the command. See this SO topic for explanations. So the optimized answer would be :

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*//g' > <filename>.sql

Related questions

How can I get rid of these comments in a MySQL dump?

I'm trying to create a simple structure only dump of my database. Using mysqldump gives me a result like: /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_…

Read More
Export MySQL data to Excel in PHP

I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code: $…

Read More
Exporting results of a Mysql query to excel?

My requirement is to store the entire results of the query SELECT * FROM document WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429) to an Excel file.

Read More