Software Engineer, CTO @Makimo
The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.
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.
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.
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.
mysqldump to export your database:
- mysqldump -u username -p database_name > data-dump.sql
usernameis the username you can log in to the database with
database_nameis the name of the database to export
data-dump.sqlis 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:
- head -n 5 data-dump.sql
The top of the file should look similar to this, showing a MySQL dump for a database named
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.
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:
- 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
- CREATE DATABASE new_database;
You’ll see this output confirming the database creation.
OutputQuery 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:
- mysql -u username -p new_database < data-dump.sql
usernameis the username you can log in to the database with
newdatabaseis the name of the freshly created database
data-dump.sqlis 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.
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 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.
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!
Click below to sign up and get $200 of credit to try our products over 60 days!
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.
A few command issues we see people getting into:
The simple way to solve this is to add the
--no-tablespacesflag 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-databasesinstead of specifying a particular database.
You could do:
Here we uses the
--all-databasesflag to dump all databases instead of specifying a particular one.
Note: the output will be a single dump file,
alldatabases.sqlthat 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
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
Thank you for your article! It’s very helpful.
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:
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?