mysqldump - Export structure only without autoincrement

Question

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 1

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

Answer 2

JoDev's answer worked perfectly for me with a small adjustment to the sed regular expression:

mysqldump -d -h localhost -u<user> -p<password> <databaseName> | sed 's/ AUTO_INCREMENT=[0-9]*//g' > databaseStructure.sql