Using mysqldump to only get database schema

The MySQL command line tool mysqldump is used to create 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.

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 “db_user” 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 db_user -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 db_user -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 “secure” do this:

mysqldump --no-data -u db_user -psecure mydatabase

Dumping the database structure for one table with no data