// Tutorial //

How To Import and Export Databases in MySQL or MariaDB

Published on December 21, 2016 · Updated on December 15, 2021
Default avatar
By Mateusz Papiernik
Developer and author at DigitalOcean.
How To Import and Export Databases in MySQL or MariaDB

The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

Introduction

Importing and exporting databases is a common task in software development. You can use data dumps to back up and restore your information. You can also use them to migrate data to a new server or development environment.

In this tutorial, you will work with database dumps in MySQL or MariaDB (the commands are interchangeable). Specifically, you will export a database and then import that database from the dump file.

Prerequisites

To import or export a MySQL or MariaDB database, you will need:

Note: As an alternative to manual installation, you can explore the DigitalOcean Marketplace’s MySQL One-Click Application.

Step 1 — Exporting a MySQL or MariaDB Database

The mysqldump console utility exports databases to SQL text files. This makes it easier to transfer and move databases. You will need your database’s name and credentials for an account whose privileges allow at least full read-only access to the database.

Use mysqldump to export your database:

  1. mysqldump -u username -p database_name > data-dump.sql
  • username is the username you can log in to the database with
  • database_name is the name of the database to export
  • data-dump.sql is the file in the current directory that stores the output.

The command will produce no visual output, but you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file.

Run the following command:

  1. head -n 5 data-dump.sql

The top of the file should look similar to this, showing a MySQL dump for a database named database_name.

SQL dump fragment
-- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64) -- -- Host: localhost Database: database_name -- ------------------------------------------------------ -- Server version 5.7.16-0ubuntu0.16.04.1

If any errors occur during the export process, mysqldump will print them to the screen.

Step 2 — Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.

First, log in to MySQL as root or another user with sufficient privileges to create new databases:

  1. mysql -u root -p

This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In this example, the new database is called new_database:

  1. CREATE DATABASE new_database;

You’ll see this output confirming the database creation.

Output
Query OK, 1 row affected (0.00 sec)

Then exit the MySQL shell by pressing CTRL+D. From the normal command line, you can import the dump file with the following command:

  1. mysql -u username -p new_database < data-dump.sql
  • username is the username you can log in to the database with
  • newdatabase is the name of the freshly created database
  • data-dump.sql is the data dump file to be imported, located in the current directory

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.

Conclusion

In this tutorial you created a database dump from a MySQL or MariaDB database. You then imported that data dump into a new database. mysqldump has additional settings that you can use to alter how the system creates data dumps. You can learn more about from the official mysqldump documentation page.

To learn more about MySQL, check out our MySQL resource page.

To learn more about MySQL queries, check out our tutorial, “An Introduction to Queries in MySQL”.


Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Developer and author at DigitalOcean.

Default avatar
senior technical writer

hi! i write do.co/docs now, but i used to be the senior tech editor publishing tutorials here in the community.


Default avatar
Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?
6 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Nice and straight tutorial thanks.

it would be great if you write on similar topic for an advance level. example, how to automate mysql backup and upload to external destination(dropbox/google drive) via cron job. furthermore keep only last 3/5 dump and delete older once. i think it would be a very useful doc for most of all. specially me. i found many tutorials on net but honestly, DO’s tutorials are best for beginners in understanding aspect. i would really appreciate that if you do that.

If you would like to dump all databases in MySQL instead of just one database, you can use the same command, but also add the flag --all-databases instead of specifying a particular database.

Instead of:

$ mysqldump -u username -p database_name > data-dump.sql

You could do:

$ mysqldump -u username -p --all-databases > alldatabases.sql

Here we uses the --all-databases flag to dump all databases instead of specifying a particular one.

Note: the output will be a single dump file, alldatabases.sql that includes all the databases you have access to, and not a single file per database.

If you decide to use an automated service to back up mysql, check out ours, it will be helpful in this case.

Islam @ SimpleBackups

When exporting, an error occurs: mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege (s) for this operation 'when trying to dump tablespaces

Why not keep it simple…?

Just create the db and then, within mysql write

Mysql>source data-dump.sql

Assuming of course your dump in your home directory, and you have USE your new db context.

If your password contains special characters you must wrap it in quotes

--password='Y0Ürp4$$W0rd??'

Thank you for your article! It’s very helpful.

One thing:

The command will produce no visual output, but you can inspect the contents of filename.sql to check if it’s a legitimate SQL dump file by using:

And then:

head -n 5 data-dump.sql

Do you mean “you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file by using…”? Or filename.sql is some other file?