Tutorial

How To Import and Export Databases in MySQL or MariaDB

Updated on February 28, 2024
Default avatar

By Mateusz Papiernik

Software Engineer, CTO @Makimo

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.

1-Click deploy a database using DigitalOcean Managed Databases. Let DigitalOcean focus on scaling, maintenance, and upgrades for your database.

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

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

Want to launch a high-availability MySQL cluster in a few clicks? DigitalOcean offers worry-free MySQL managed database hosting. We’ll handle maintenance and updates and even help you migrate your database from external servers, cloud providers, or self-hosted solutions. Leave the complexity to us, so you can focus on building a great application.

Learn more here


About the authors
Default avatar

Software Engineer, CTO @Makimo

Creating bespoke software ◦ CTO & co-founder at Makimo. I’m a software enginner & a geek. I like making impossible things possible. And I need tea.


Default avatar

staff technical writer

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



Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
7 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.

Simon Bennett
DigitalOcean Employee
DigitalOcean Employee badge
September 1, 2022

A few command issues we see people getting into:

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

The simple way to solve this is to add the --no-tablespaces flag to your mysqldump command, you could also solve this by updating your user privileges. GRANT PROCESS ON *.* TO user@localhost; (note it has to be done on a global level)

Simon Founder and CEO SnapShooter DigitalOcean Backups

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?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel