In relational databases, the database 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.
The basic command needed to export the database schema from MySQL looks like this:
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:
-u: The database username that you want to authenticate with
mysqldumpto prompt for a password to authenticate
-h: The hostname or IP address of where MySQL is located
-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:
-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:
mysqldump --user=sales_reporter --password --no-data SALES > sales_database_schema.sql
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.
You can modify how many databases the export will target with one of the following options:
-B: Treat all name arguments as database names. This allows you to export the schema from multiple databases at the same time.
-A: Export all databases within MySQL (with the exception of the
performance_schemadatabase that is used internally)
So to dump all databases, you could use:
mysqldump --user=USERNAME --password --no-data --all-databases > all_schemas.sql
Or to dump the structure from three different databases, you could use:
mysqldump --user=USERNAME --password --no-data --databases FIRST SECOND THIRD > three_db_schemas.sql
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
INVENTORY, you can export only those structures by typing:
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.
In addition to databases and tables, you can also explicitly export event and routine definitions by including these options:
-R: Include stored procedures and functions within the exported schema dump
-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:
mysqldump --user=USERNAME --password --no-data --routines --events SALES > all_sales_schemas.sql
Some additional options that can be useful depending on your goals include:
--add-drop-database: Add a
DROP DATABASEstatement to the dump file prior to each
CREATE DATABASEstatement. 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" 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.
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.