How To Import and Export Databases in MySQL or MariaDB

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 and Export Databases

• A virtual machine with a non-root sudo user. For setup, follow our Initial Server Setup Guide for your distribution.
• MySQL or MariaDB installed. Refer to our tutorials on How To Install MySQL or How To Install MariaDB.
• A sample database created in your database server. Follow Creating a Sample Database in our An Introduction to Queries in MySQL tutorial.

Note: Alternatively, explore the DigitalOcean Marketplace’s MySQL One-Click Application for an easy installation.

Step 1 — Exporting a MySQL or MariaDB Database

The mysqldump console utility exports databases to SQL text files, simplifying database transfer and migration. Use the following command to export your database:

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

Replace:

• username with your database username
• database_name with the name of the database to export
• data-dump.sql with the desired file name for the exported data

Check the contents of the dump file:

head -n 5 data-dump.sql

Output example:

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

Step 2 — Importing a MySQL or MariaDB Database

To import a dump file, first create a new database:

mysql -u root -p

Create the database:

CREATE DATABASE new_database;

Output:

Query OK, 1 row affected (0.00 sec)

Exit the MySQL shell and import the dump file:

mysql -u username -p new_database < data-dump.sql

Replace:

• username with your database username
• new_database with the name of the new database
• data-dump.sql with the name of the dump file

To verify the import, log in to the MySQL shell, select the new database, and inspect its data:

mysql -u username -p
USE new_database;
SHOW TABLES;

Conclusion for Import and Export Databases

In this tutorial, you created a database dump from a MySQL or MariaDB database and imported that data dump into a new database. Explore the official mysqldump documentation for additional options to customize your data dumps.