How to export and import MySQL or MariaDB databases

In this post, we will focus on the process to export and import databases in MySQL and MariaDB in order to create a backup. There is not the slightest doubt that backups are essential to be protected against incidents such as data losses or cyberattacks. Therefore, when backing up websites and applications, it is important to keep in mind databases (DB) as well. So that, in case of a contingency, databases can be restored any minute.

So, if you want to learn how to import and export MySQL/MariaDB databases using data dumps, keep reading. We will also explain the steps to restore backups, which is easier than it might seem. This tutorial applies both to MySQL and MariaDB databases, as the commands are interchangeable between both database management systems.

Previous requirements to export and import databases

To begin with, it is interesting to know the necessary requirements to import and export MySQL or MariaDB databases:

• Access to the server where MySQL or MariaDB is installed and configured.
• The name of the database you wish to export/import, and the username and password to access it.

Exporting a MySQL or MariaDB database

For exporting the database, you can use the mysqldump command on the console. Once the backup is created, the file generated can be easily moved. To start exporting the database, execute the following command into the command line:

mysqldump -u username -p database_name > dump_filename.sql

• username refers to the database user name.
• database_name must be replaced by the name of the database you want to export.
• dump_filename.sql is the file that will be generated with all the database information.

That command will not generate any visual output. So, to verify the SQL copy has been done correctly, you can inspect the generated file in order to make sure it is a SQL copy. For doing so, you can use the following command:

head -n 5 dump_filename.sql

This command should return something like this:

-- MySQL dump 10.13  Distrib 8.0.28, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       8.0.28-0 ubuntu 0.20.04.1

It is also possible to export one or several tables instead of the whole database. For doing so, you must indicate in the command the selection you wish to do.

mysqldump -u username -p database_name table_name_1 table_name_2 table_name_3 > dump_filename.sql

In this case, it is important to be especially careful with the relationships between the different registers. After the import, only the selected tables will be overwritten.

Importing a MySQL or MariaDB database

To import a MySQL or MariaDB dump, the first thing to do is to create the database where the import will take place. For doing so, if you do not have a database manager, you need to connect to the database server as a “root” user.

mysql -u root –p

This will open the Shell of MySQL or MariaDB. Then, you will be able to create the database.

mysql> CREATE DATABASE new_database;

If everything works correctly, you will see something similar to this:

Query OK, 1 row affected (0.00 sec)

Once it has been created, you need to exit that Shell; for doing so, use CTRL+D. When you are back to the normal command line, it will be time to launch a command to import the database.

mysql -u username -p new_database < dump_filename.sql

• username is the name of the user that has access to the database.
• new_database is the name of the database where the import will take place.
• dump_filename.sql is the name of the file containing all the SQL commands that will be imported.

If there is any type of error during the import process, it will be displayed on the screen. As you can see, the process of exporting and importing a MySQL or MariaDB database is very simple.