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)?

It looks like with MySQL 5.5 (server), `--no-data` will omit auto_increment values by default. — Joey Adams, Oct 16 '14 at 15:00
@JoeyAdams are you sure? That is not the behaviour I experience — aland, Feb 12 '16 at 22:59
@JoeyAdams MySQL 5.7.*'s mysqldump does not ommit the auto_increment when using --no-data. — oxygen, Aug 30 '16 at 11:53

JoDev · Accepted Answer · 2019-11-27T08:57:46.250

79

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

edited Nov 27 '19 at 08:57

answered Mar 27 '13 at 10:22

JoDev

6,633
1
22
37

12

I don't think `--skip-auto-increment` is a real option. I can't find it in the [documentation](http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html). Neither of the two MySQL bugs for this issue mention it: [20786](http://bugs.mysql.com/bug.php?id=20786), [30957](http://bugs.mysql.com/bug.php?id=30957). Which version of mysqldump has this option? – Rich Jun 18 '13 at 10:21
1

`--skip-auto-increment` is an option added in MySQL GUI Tools if a remember correctly. (not sure ^^) So in fact it's not really a solution! But the second inline command is correct, I founded it [here](http://bugs.mysql.com/bug.php?id=20786) where the topic talking about **autoincrement** issue, and provide the idea of `sed` filtering! – JoDev Jun 20 '13 at 09:27
4

Sed option would not remove all auto increments if you export multiple tables, only the last one. Also, --skip-auto-increment is not an existing option. How did this answer get to be rated so high? – Lex Podgorny Jun 17 '15 at 20:51
3

you need a g at the end of your sed command: sed 's/.../.../g' to replace all occurrences. – p91paul Jul 25 '15 at 09:33
1

This command is not correct. --skip-auto-increment doesn't exist. The regex is not correct. As p91paul noted the g modifier needs to be added in order to replace all occurrences. Use @JohnW solution instead. – Aalex Gabi May 20 '16 at 12:37
The sed didn't work for me, I used `sed 's/ AUTO_INCREMENT=[0-9]* / /g'` – Janne Annala Nov 18 '19 at 12:30
Would suggest removing all the attempts and just leaving the final one as new visitors arriving at this from Google won't need to know about the history. – Steve Chambers Nov 21 '20 at 19:33

score 54 · Answer 2 · answered Oct 12 '14 at 18:09

54

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

answered Oct 12 '14 at 18:09

JohnW

541
4
2

1

Yeah I'm not sure what that `\b` does within the sed statement, but as per @JohnW's suggestion here, the removal of that switch does the trick for me as well. – David Jun 24 '19 at 22:44

Erin Schoonover · Answer 3 · 2015-12-29T01:50:46.290

4

It is --create-options, which is included with --opt, by default, which generates the AUTO_INCREMENT table definitions.

If you only want the base tables,

mysql -hlocalhost -uuser -ppass --skip-column-names --batch \
    -e "select table_name from tables where table_type = 'BASE TABLE' and table_schema = 'schemaname'" INFORMATION_SCHEMA \
| xargs mysqldump -hlocalhost -uuser -ppass \
    --no-data --skip-triggers --skip-opt --no-create-db \
    schemaname

If you want views, triggers and routines too,

mysqldump -hlocalhost -uuser -ppass \
    --skip-opt --events --routines --no-data \
    schemaname

edited Dec 29 '15 at 01:50

answered Jul 08 '14 at 22:09

Erin Schoonover

530
2
13

3

Note that this also drops any auto increment fields, `drop table`s, character sets, etc. – Deanna Oct 08 '15 at 13:02

score 2 · Answer 4 · edited May 30 '17 at 08:56

Thanks to this post, I was able to answer my question:

How can I do version control on my db?

Then I just created this script: db_bkp.sh

#!/bin/sh
filename="db_structure.sql"
backupfolder="/var/www/"
fpath="$backupfolder/$filename"
usr="DBUSER"
pass="DBPASS"
db="DBNAME"
mysqldump --user=$usr --password=$pass --no-data $db | sed 's/ AUTO_INCREMENT=[0-9]*//g' > "$fpath"

Then I added this to crontab:

30 5 * * * sh /home/scripts/db_bkp.sh

Then in my repo I added the result, db_structure.sql to git and before pushing changes to prod I always check if there's any structural changes I forgot to do on all dbs.

score -3 · Answer 5 · answered Mar 14 '14 at 15:37

-3

mysqldump -u [USER] -p [PASSWORD] -d --skip-opt --single-transaction [DB_SCHEMA] > [FILE.ESTENSIONE]

answered Mar 14 '14 at 15:37

Zio Panzu

57
2
11

6

This will also remove the auto_increment flag on the fields it's enabled on, not just the auto_increment value at the end. – Aquarion Apr 01 '14 at 13:23
`--skip-opt` discussed [here](http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_opt) and [here.](http://bugs.mysql.com/bug.php?id=22941) – Chris May 22 '14 at 09:35

mysqldump - Export structure only without autoincrement

5 Answers5

New UPDATE thanks to comments.

Linked

Related