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 mysqldump --help.

Note that your MySQL installation comes with mysqldump by default, typically contained in /usr/local/mysql/bin on Mac OS. This means 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 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 command looks like this:

1mysqldump 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 on .sql)

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:

1mysqldump mydb > mydb.sql

Providing database credentials

You can add the following arguments to specify the location of your MySQL database server:

ArgumentDefaultDescription
--host (short: -h)localhostThe 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 argument:

ArgumentDefaultDescription
--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:

1mysql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus

You can use the following mysqldump command:

1mysqldump --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 (i.e. the SQL statements that define your database schema like CREATE TABLE,...)
  • dump only the DDL but exclude the actual data
  • exclude specic tables

Here's an overview of a few command line options you can use in these scenarios:

ArgumentDefaultDescription
--no-create-db (short: -n)falseExclude any DDL statements and export only data.
--no-data (short: -d)falseExclude data and export only DDL statements.
--tablesincludes all tables by defaultExplicitly 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 a SQL file, you can restore the state of the database by feeding the SQL file into mysql:

1mysql DB_NAME INPUT_FILE

Note that your MySQL installation comes with mysql by default, typically contained in /usr/local/mysql/bin on Mac OS. This means you can either invoke the command by pointing to that directory /usr/local/mysql/bin/mysmysqlqldump 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 hat name must be created beforehand!)
  • the name of the target input file (likely ends on .sql)

For example:

1mysql mydb < mydb.sql

To authenticate, you can use the --user and --password options discussed above:

1mysql --user root --password mydb < mydb.sql

To create a database beforehand, you can use the following SQL statement:

1CREATE DATABASE mydb;
Edit this page on Github