Use mysqldump to get the schema only

The MySQL command line tool mysqldump is used to create backup copies (or dumps) of databases including the structure or schema and the data itself. There are a number of command line flags which can get MySQL to dump just the data or just the structure instead of everything. This post looks at how to dump just the schema of the MySQL database as a whole, for a single table, or for several tables.

Dumping the database structure for all tables with no data

Add the -d flag to signify that no data should be included in the output like so, where "mydatabase" is the name of the database to dump, and "someuser" is the login name used to connect to the database. The following command will dump the table structure for all tables in the specified MySQL database:

mysqldump -d -u someuser -p mydatabase

The -d flag says not to include data in the dump. Alternatively you can use --no-data instead if you find that easier to remember:

mysqldump --no-data -u someuser -p mydatabase

The -u flag indicates the username and the -p flag that a password will be supplied. After pressing <enter> you will be prompted for the password.

Alternatively, the password can be supplied on the command line, but there must be no space between the -p flag and the password. For example, if the password was "apples" do this:

mysqldump --no-data -u someuser -papples mydatabase

Dumping the database structure for one table with no data

It's the same to dump data for a single table, using the same sort of syntax as the above but adding the table to dump to the end of the command. To dump the structure of a table called "products" do this:

mysqldump -d -u someuser -p mydatabase products

Dumping the database structure for several table with no data

This is the same as for one table but just specify as many additional tables after the database name as you would like to dump. This command will dump the structure for the tables "products", "categories" and "users":

mysqldump -d -u someuser -p mydatabase products categories users

Dumping the structure to a file

All the example commands above will write the dump out to standard output, meaning they'll scroll up your terminal / command prompt window which may not be very useful. To save it to a file instead redirect the output. For example:

mysqldump -d -u someuser -p mydatabase > mydatabase.sql

You could use the contents of this file with the "mysql" command line tool to create those same tables in another database:

mysql -u someuser -p anotherdatabase < mydatabase.sql