CODEWITHSUNDEEP
exportmysqldatabase-schemaauto-increment
Paris
Paris

Reputation: 6761

mysqldump - Export structure only without autoincrement

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

Upvotes: 101

Views: 56583

Answers (5)

JoDev
JoDev

Reputation: 6873

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 work 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

Upvotes: 82

Gerardo Rosciano
Gerardo Rosciano

Reputation: 901

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.

Upvotes: 2

Erin Schoonover
Erin Schoonover

Reputation: 539

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

Upvotes: 5

JohnW
JohnW

Reputation: 571

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

Upvotes: 57

Zio Panzu
Zio Panzu

Reputation: 57

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

Upvotes: -3

Related Questions