How to Export MySQL Schema Without Data

MySQLdump is the most common tool used to export data into .sql files in MySQL. However, by default it exports both database schema as well as the actual data in databases. Sometimes you may want to export MySQL schema without data. In this article, we will learn how to do this using MySQLdump.

How to Export MySQL Schema Without Data

There are several third-party tools that help you to export database structure without data. However, we will use MySQLdump for our purpose. Here is the typical MySQLdump command to export a database.

mysqldump -u user -h localhost -p database > database.sql

The above command exports both data as well as schema. If you want to export only schema, you need to add –no-data option.

mysqldump -u user -h localhost --no-data -p database > database.sql

The above command will not export data, but only the schema.

If you want to export only a specific table and not the entire database mention the table name after database and before > operator in above command.

mysqldump -u user -h localhost --no-data -p database table_name > database.sql

If you want to export all databases with only schema and no data, you need to also add –all-databases option.

mysqldump -u user -h localhost --no-data -p --all-databases > database.sql

But please note, it will not include any view definition either.

Also note, it will not export any routines or events defined in original database. If you want to include routines and events also, you need to add –routines and –events options respectively, in your MySQLdump command.

mysqldump -u user -h localhost --no-data --routines --events -p database > database.sql

In this article, we have learnt several commands to export MySQL database schema without data. You can use any of them as per your requirement. This is very useful if you want to replicate one or more databases without copying the data but just the structure.

Also read:

How to Insert Element after Another Element in JS
How to Sum Values of JS Object
How to Access POST Form Fields in ExpressJS
How to Detect Scroll Direction in JavaScript
How to Split String With Multiple Delimiters in JS