# Importing and exporting data in MySQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Exporting data from MySQL to recreate your data structures and populate databases is a good way to migrate data, back up and recover, or prepare for replication. Learn more! Canonical URL: https://www.prisma.io/dataguide/mysql/inserting-and-modifying-data/importing-and-exporting-data-in-mysql Authors: Justin Ellingwood Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 ## Overview This document describes how you can export data from and import data into a MySQL database. You can learn more about this topic in the official [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html). ## Data export with `mysqldump` [`mysqldump`](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) is a native MySQL command line utility you can use to export data from your MySQL database. To see all the options for this command, run: ```shell mysqldump --help ``` Note that your [MySQL installation](https://dev.mysql.com/doc/refman/8.0/en/installing.html) comes with `mysqldump` by default, sometimes contained in `/usr/local/mysql/bin` on macOS. If the command isn't recognized automatically, you can either invoke the command by pointing to that directory `/usr/local/mysql/bin/mysqldump` or [adding it to your `PATH`](https://stackoverflow.com/questions/30990488/how-do-i-install-command-line-mysql-client-on-mac#answer-35338119) so that you can run `mysqldump` without specifying the directory. From the MySQL docs: > The `mysqldump` client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The `mysqldump` command can also generate output in CSV, other delimited text, or XML format. The basic command looks like this: ```shell mysqldump DB_NAME > OUTPUT_FILE ``` You need to replace the `DB_NAME` and `OUTPUT_FILE` placeholders with the respective values for: - your **database name** - the name of the desired **output file** (should end in `.sql` for best interoperability) For example, to export data from a local MySQL server from a database called `mydb` into a file called `mydb.sql`, you can use the following command: ```shell mysqldump mydb > mydb.sql ``` ### Providing database credentials You can add the following arguments to specify the location of your MySQL database server: | Argument | Default | Description | | ---------------------- | ----------- | ------------------------------------------------------------------------- | | `--host` (short: `-h`) | `localhost` | The address of the server's host machine | | `--port` (short: `-p`) | - | The port of the server's host machine where the MySQL server is listening | To authenticate against the MySQL database server, you can use the following arguments: | Argument | Default | Description | | -------------------------- | ------- | ------------------------------ | | `--user` (short: `-u`) | - | The name of the database user. | | `--password` (short: `-p`) | - | Trigger password prompt. | For example, if you want to export data from a MySQL database that has the following connection string (see the [format in Prisma ORM 7's MySQL docs](https://www.prisma.io/docs/orm/v7/core-concepts/supported-databases/mysql#self-hosted-mysqlmariadb)): ``` mysql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus ``` You can use the following `mysqldump` command: ```shell mysqldump --host ec2-46-137-91-216.eu-west-1.compute.amazonaws.com --port --user opnmyfngbknppm --password d50rgmkqi2ipus > backup.sql ``` Note that **this command will trigger a prompt where you need to specify the password** for the provided user. ### Controlling the output There might be cases where you don't want to dump the _entire_ database, for example you might want to: - dump only the actual data but exclude the [DDL](https://www.prisma.io/dataguide/intro/database-glossary#data-definition-language) (i.e. the SQL statements that define your database schema like `CREATE TABLE`,...) - dump only the DDL but exclude the actual data - exclude specific tables Here's an overview of a few command line options you can use in these scenarios: | Argument | Default | Description | | ------------------------------ | -------------------------------- | ------------------------------------------------------------------------------------------------------------------ | | `--no-create-db` (short: `-n`) | `false` | Exclude any [DDL](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_ddl) statements and export only data. | | `--no-data` (short: `-d`) | `false` | Exclude data and export only [DDL](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_ddl) statements. | | `--tables` | _includes all tables by default_ | Explicitly specify the names of the tables to be dumped. | | `--ignore-table` | - | Exclude specific tables from the dump. | ## Importing data from SQL files After having used `mysqldump` to export your MySQL database as an [SQL](https://www.prisma.io/dataguide/intro/database-glossary#sql) file, you can restore the state of the database by feeding the SQL file into [`mysql`](https://dev.mysql.com/doc/refman/9.0/en/mysql.html): ```shell mysql DB_NAME < INPUT_FILE ``` Note that your [MySQL installation](https://dev.mysql.com/doc/refman/8.0/en/installing.html) comes with `mysql` by default, sometimes contained in `/usr/local/mysql/bin` on macOS. If the command isn't recognized automatically, you can either invoke the command by pointing to that directory `/usr/local/mysql/bin/mysql` or [adding it to your `PATH`](https://stackoverflow.com/questions/30990488/how-do-i-install-command-line-mysql-client-on-mac#answer-35338119) so that you can run `mysql` without specifying the directory. You need to replace the `DB_NAME` and `INPUT_FILE` placeholders with the respective values for: - your **database name** (a database with that name must be created beforehand!) - the name of the target **input file** (likely ends in `.sql`) For example: ```shell mysql mydb < mydb.sql ``` To authenticate, you can use the `--user` and `--password` options discussed above: ```shell mysql --user root --password mydb < mydb.sql ``` To create a database beforehand, you can use the following SQL statement: ```sql CREATE DATABASE mydb; ``` ## Conclusion Exporting data from MySQL and ingesting it again to recreate your data structures and populate databases is a good way to migrate data, back up and recover, or prepare for replication. Understanding how the `mysqldump` and `mysql` tools work together to accomplish this task will help you transfer data across the boundaries of your databases.