How to use mysqldump Command to Export Database Schema In SQL

The mysqldump is a command-line client utility that takes backups of one or more MySQL databases. This is helpful when transferring MySQL data to another server.

Following is the mysqldump command’s syntax of SQL export database schema:

Syntax

mysqldump -u [Username] -p —no-data —databases [Database1 | Database2 | …….. | DatabaseN] > [DumpFileName].sql

mysqldump Command Flags:

• -u: Database username
• -p: Database password
• –databases: The list of databases that you want to export. If you wish to export all the databases, use –all-databases.
• –no-data: Indicates the mysqldump command not to include any DB data while exporting schema.

Exporting Database Schema Without Data using mysqldump Command Example

Let’s look at an example of how to export a database schema using mysqldump command. In this example, we will export a database called ‘GFG’ and its tables and indexes, but not data.

mysqldump -u root -p --no-data --databases GFG > dump.sql

Output:

The dump.sql file contains table creation statements, but it won’t contain any insert statements. Using –no-data mysqldump does not export any data.

Database Schema specifies the structure of a database with its components like tables, columns, and indexes. Exporting data in SQL is an essential task in database management used to perform functions like data backup, recovery, migration, data analysis, performance optimization, compliance, auditing, etc.

There may be situations when there is a need to export the schema that includes tables & indexes structure and not the actual data. In many cases, there is only a need to export the schema containing the table and index structures. This is useful for replicating the database in another environment or visualizing the schema.

This article teaches exporting the database schema without data using the mysqldump command.