MySQL / Inserting and modifying data
Importing and exporting data in MySQL
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.
Data export with mysqldump
mysqldump 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:
Note that your MySQL installation 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 so that you can run mysqldump without specifying the directory.
From the MySQL docs:
The
mysqldumpclient 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. Themysqldumpcommand can also generate output in CSV, other delimited text, or XML format.
The basic command looks like this:
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
.sqlfor 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:
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:
You can use the following mysqldump command:
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 (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 statements and export only data. |
--no-data (short: -d) | false | Exclude data and export only 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 file, you can restore the state of the database by feeding the SQL file into mysql:
Note that your MySQL installation 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 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:
To authenticate, you can use the --user and --password options discussed above:
To create a database beforehand, you can use the following SQL statement:
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.
