Creating backups of your websites and applications is basically essential. When talking about backups, we don’t only refer to website files, but also to databases (DB). In this post, we will focus on the process to create a backup of MySQL and MariaDB databases.
There isn’t the slightest doubt that backing up databases is very important. In case of an incident, such as some hack or data loss, having a backup of your database will allow you to restore it any minute. So, if you want to learn how to import and export MySQL databases, keep reading. We will also explain the steps to follow in order to restore backups; it is easier than it might seem.
Previous requirements to import and export 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.
- The name of the database, the user and the password to access the database.
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 line:
mysqldump -u username -p database_name > data-dump.sql
usernamerefers to the database user name.
database_namemust be replaced by the name of the database you want to export.
data-dump.sqlis the file that will be generated with all the database information.
That command won’t generate any visual output. So, to verify the SQL copy has been done correctly, you can inspect the file generated in order to make sure it is a SQL copy. For doing so, you can use the following command:
head -n 5 data-dump.sql
This command should return something like this:
-- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64) -- -- Host: localhost Database: database_name -- ------------------------------------------------------ -- Server version 5.7.16-0 ubuntu 0.16.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 > data-dump.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 don’t 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 < data-dump.sql
usernameis the name of the user that has access to the database.
new_databaseis the name of the database where the import will take place.
data-dump.sqlis 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.