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 dump data to back up and restore your data. You can also use it 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

A virtual machine with a non-root sudo user
MySQL or MariaDB installed
A sample database created on your database server

Step 1 – Export a MySQL or MariaDB Database

Console tools mysqldump Exports databases to SQL text files. This makes it easier to migrate and move databases. You will need the name of your database and credentials for an account whose privileges give at least full read-only access to the database.

From mysqldump To export your database, use:

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

Username: is the username with which you can log in to the database
database_name The name of the database to export is
data-dump.sql A file in the current directory that stores the output.

This command does not produce any visual output, but you can view the contents data-dump.sql Check to see if it is a legitimate SQL dump file.

Run the following command:

head -n 5 data-dump.sql

The top of the file should look something like this, and it will contain a MySQL wrapper for a database called database_name It shows.

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 an error occurs during the export process, mysqldump It prints them on the screen.

Step 2 – Import a MySQL or MariaDB database

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

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

mysql -u root -p

This command will take you to the MySQL shell prompt. Then create a new database with the following command. In this example, the new database new_database It is called:

CREATE DATABASE new_database;

You will see this output confirming the database creation.

Output
Query OK, 1 row affected (0.00 sec)

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

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

USERNAME This is the username with which you can log in to the database.
newdatabase The name of the newly created database
data-dump.sql The dump file is the data to be imported and is located in the current directory.

If the command is executed successfully, it will not produce any output. If an error occurs during the process, mysql It prints them to the terminal. To verify that the import was successful, log in to the MySQL shell and check the data. Selecting the new database with USE new_database and then use SHOW TABLES. Or a similar command to view some data.

Result

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