MySQL Dump Notes

mysqldump-notes.md

Using mysqldump

First see the documentation here.

You can do with the --no-data option with mysqldump command

mysqldump -u <db-user> -p --no-data <db-name> > <filename>.sql

You can also extract an individual table with the --no-data option

mysqldump -u <db-user> -h <db-host> --no-data -p <db-name> <db-table> > <filename>.sql

You can use the -d option with mysqldump command

mysqldump -u <db-user> -p -d <db-name> > <filename>.sql

If you want to dump all tables from all databases and with no data (only database and table structures) you may use:

mysqldump -P <db-port> -h <db-host> -u <db-user> -p --no-data --all-databases > <filename>.sql

Export structure only without autoincrement values.

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

Beware though that --no-data option will not include the view definition. So if yo had a view like following create view v1 select a.id AS id, a.created_date AS created_date from t1; with --no-data option, view definition will get changed to following create view v1 select 1 AS id, 1 AS created_date