# How to export database and table schemas in MySQL

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

Learn how to export your database objects from MySQL for analysis, migrating, backups, and more.

Canonical URL: https://www.prisma.io/dataguide/mysql/short-guides/exporting-schemas
Authors: Justin Ellingwood
Content revision: 8ea77e5fdac07e33205a18aa6afcfbc52bef3d75eb21128466de453fc8bc5fdb
Source revision: c9a87c0db11e7456020d173bc74dc771048e8f5e

## Introduction

In relational databases, the [database schema](https://www.prisma.io/dataguide/intro/database-glossary#schema) defines the structure of the database and its component parts like tables, fields, and indexes. Extracting and exporting this information is useful in many scenarios, including backups, migrating to new environments, visualizing data structures, and managing these structures within a codebase.

In this short guide, we'll discuss how to export MySQL database schemas using the `mysqldump` command. While this utility can export many types of data from MySQL, we'll focus on extracting the data structures themselves in this guide.

## Basic usage

The basic command needed to export the database schema from MySQL looks like this:

```shell
mysqldump --user=USERNAME --host=HOSTNAME --password --no-data DATABASE > schema.sql
```

The options here can be divided into two separate categories.

The first category defines the generic basic connection information that you need to provide in order to connect with any MySQL utility:

- `--user=` / `-u`: The database username that you want to authenticate with
- `--password` / `-p`: Force `mysqldump` to prompt for a password to authenticate
- `--host=` / `-h`: The hostname or IP address of where MySQL is located
- `--port=` / `-p`: The port number where MySQL is listening

If you are connecting to a local MySQL instance running in the default configuration, you can typically omit the host and port options.

The second category tells `mysqldump` what to export:

- `--no-data` / `-d`: This tells the utility to only export the structure itself, not the records they contain

Additionally, the first non-option argument (represented here by the word "DATABASE") indicates the exact database to export.

Using this information, you could export the schema of a database called `SALES` using a limited user called `sales_reporter` with a command like this:

```command
mysqldump --user=sales_reporter --password --no-data SALES > sales_database_schema.sql
```

## Modifying the export behavior

The basic usage discussed above will output every structure related to the database in question. We can modify this behavior with a number of additional options.

### Targeting more than one database

You can modify how many databases the export will target with one of the following options:

- `--databases` / `-B`: Treat all name arguments as database names. This allows you to export the schema from multiple databases at the same time.
- `--all-databases` / `-A`: Export all databases within MySQL (with the exception of the `performance_schema` database that is used internally)

So to dump all databases, you could use:

```command
mysqldump --user=USERNAME --password --no-data --all-databases  > all_schemas.sql
```

Or to dump the structure from three different databases, you could use:

```command
mysqldump --user=USERNAME --password --no-data --databases FIRST SECOND THIRD > three_db_schemas.sql
```

### Exporting only certain structures

You can also reduce the structures exported by naming specific tables to export as additional arguments after the database name.

For example, if three of the tables in your `SALES` database are called `EMPLOYEE`, `STORE`, and `INVENTORY`, you can export only those structures by typing:

```command
mysqldump --user=USERNAME --password --no-data SALES EMPLOYEE STORE INVENTORY > some_sales_tables.sql
```

In this construction, the first argument is always assumed to the be database name and all additional named arguments are taken to be tables within that database. Because of this, this usage is incompatible with the `--databases` option which modifies how `mysqldump` interprets additional arguments.

### Exporting additional structures

In addition to databases and tables, you can also explicitly export event and routine definitions by including these options:

- `--routines` / `-R`: Include stored procedures and functions within the exported schema dump
- `--events` / `-E`: Include the definition of Event Scheduler events in the output

For example, to include a dump of the database `SALES` that includes these extra definitions, you could type:

```command
mysqldump --user=USERNAME --password --no-data --routines --events SALES > all_sales_schemas.sql
```

### Other relevant options

Some additional options that can be useful depending on your goals include:

- `--add-drop-database`: Add a `DROP DATABASE` statement to the dump file prior to each `CREATE DATABASE` statement. This ensures that any previously defined structure for a given database is removed first to avoid conflicts.
- `--single-transaction`: Sets the transaction isolation level to "[repeatable read](https://www.prisma.io/dataguide/intro/database-glossary#repeatable-read-isolation-level)" to help ensure a more consistent database state with storage engines like InnoDB. This dumps a snapshot of the database at the point-in-time when the dump is initialized.

These options can be added to your schema dump commands without altering the basic semantics or meaning of the other components.

## Conclusion

Being able to export your schemas allows you to save your database structures outside of the database itself. This is helpful when setting up new environments, evolving your schema as your needs change, and visualizing the structure of the information you are storing.