mysqldump - Export structure only without autoincrement
Solution 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
Solution 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
Solution 3
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
Solution 4
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.
Paris
I am a co-founder at SourceLair, an online IDE for developers that build Python and JavaScript web applications.
Updated on November 27, 2020Comments
-
Paris over 3 years
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)? -
Rich almost 11 yearsI don't think
--skip-auto-increment
is a real option. I can't find it in the documentation. Neither of the two MySQL bugs for this issue mention it: 20786, 30957. Which version of mysqldump has this option? -
JoDev almost 11 years
--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 where the topic talking about autoincrement issue, and provide the idea ofsed
filtering! -
Aquarion about 10 yearsThis will also remove the auto_increment flag on the fields it's enabled on, not just the auto_increment value at the end.
-
Chris almost 10 years
-
Lex Podgorny almost 9 yearsSed 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?
-
p91paul almost 9 yearsyou need a g at the end of your sed command: sed 's/.../.../g' to replace all occurrences.
-
Deanna over 8 yearsNote that this also drops any auto increment fields,
drop table
s, character sets, etc. -
Aalex Gabi almost 8 yearsThis 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.
-
David almost 5 yearsYeah 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. -
Janne Annala over 4 yearsThe sed didn't work for me, I used
sed 's/ AUTO_INCREMENT=[0-9]* / /g'
-
Steve Chambers over 3 yearsWould 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.