How to dump or export all the table definitions in a MySQL database

So you have  a database with loads of tables. You want the table definitions of all of them. You don’t really need the data. mysqldump to the rescue!

mysqldump -u USERNAME --password=PASSWORD --no-data --opt DB1 > DUMPFILE

That’s it! Easy as a pie The key here is the –no-data option. It dumps all the table definitions, but not the table data.

Want to do this for multiple databases? No problem.

mysqldump -u USERNAME --password=PASSWORD --no-data --opt --databases DB1 DB2 > DUMPFILE

The –databases option allows you to add multiple databases.

mysqldump -u USERNAME --password=PASSWORD --no-data --opt --all-databases > DUMPFILE

The –all-databases option allows you to dump all the databases.

mysqldump -u USERNAME --password=PASSWORD --no-data --opt DB1 --ignore-table DB1.TABLENAME1 --ignore-table DB1.TABLENAME2 > DUMPFILE

–ignore-table option allows you to skip dumping certain tables. Do not forget to specify the databasename when using this option.

Know any more tricks? Let us know in the comments below

Related posts:

1. How to update a field in a table with a field from another table
2. How to programmatically create and log in drupal users
3. Pirate tweet