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.
February 27, 2019April 4, 20210
In this article of MySQL series, we’ll learn to import & export DBs. When you work with SQL, you will often be taking a backup of your databases. You will also be importing previously taken backups. All of this is an integral part of learning MySQL.
Exporting a Database
We first need a database that can be exported. You can see in the image below that I have a database named theitstuff. We will be exporting it here. To export the database we need to open our command line and type the following command.
mysqldump -uroot -proot theitstuff > theitstuff.sql;
This will export out database into a file called theitstuff.sql in the same folder where the command line is opened from. The file that was created can be opened in some text editor of your choice and it should look like this,
You can see that the contents of this file are nothing but SQL codes. We will now see how to import a previously exported database.
Importing a database
In the previous example, we exported a database. Now we will try to import it. Firstly we need to delete all tables from our current database; You can see in the image below that there are no tables in our current database.
mysql -uroot -proot theitstuff < theitstuff.sql;
You can see that the arrow sign has changed since this time data is being copied from the file to the database. Once you are done with this command you can see that the import has successfully worked.
Conclusion
Database imports and exports are very important concepts in MySQL. We have learned how to import and export a given database using the MySQL command line client. We also saw the export file is basically a big list of SQL commands and nothing else. I hope you understood this topic. If you have any queries regarding this topic, do let me know in the comment section below.