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 usernamedatabase_name
with the name of the database to exportdata-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 usernamenew_database
with the name of the new databasedata-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.