How To Import and Export Databases in MySQL or MariaDB

by
mysql import database

This blog post is about importing and exporting databases in MySQL or MariaDB. Importing and Exporting are fundamental processes that every system administrator and developer should have a solid understanding of. MySQL import database is also known as restoring the database from a daily, weekly, or any backup we have on our server. Exporting is considered as dumping the existing database for a backup before any upgrade of the MySQL or MariaDB or regular backup process. In the following paragraphs, we will cover all processes, from installing a MySQL database server, creating a database, making a backup, and restoring the same one to learn the process.

In this tutorial, we will use Ubuntu 24.04 OS, but you can choose any Linux OS you want. The MySQL commands are the same. Let’s get started!

Prerequisites

Update the System

We assume that you have freshly installed Ubuntu 24.04 OS. We will update the system packages to the latest version before installing MySQL. To do that, execute the following commands:

sudo apt update -y && sudo apt upgrade -y

Install MySQL database server

We installed and used the MySQL database server instead of MariaDB for this post. Please note that you can also use MariaDB since the same commands. Just in case you want to install and use MariaDB, you can install it with the command below:

sudo apt install mariadb-server -y

Since we are going to use MySQL, the command for installation is the following one:

sudo apt install mysql-server -y

Once installed, start and enable the MySQL service:

sudo systemctl start mysql && sudo systemctl enable mysql

To check the status of the service, you can execute the command below:

sudo systemctl status mysql

You should receive the following output:

root@host:~# sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Wed 2024-12-03 04:58:28 CST; 43s ago
   Main PID: 393089 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4613)
     Memory: 362.5M (peak: 377.0M)
        CPU: 2.423s
     CGroup: /system.slice/mysql.service
             └─393089 /usr/sbin/mysqld

Create a test Database and User

First, log in to the MySQL terminal with the command below:

mysql

Once logged in, you will see the following screen:

root@host:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.40-0ubuntu0.24.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To create a database and user, you should execute the following commands one by one in the MySQL terminal:

CREATE DATABASE dbtest;
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'StrongPasswordHere';
GRANT ALL ON dbtest.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Export Database

As we said in the first paragraph, exporting a database is considered a dump of the database. The database dump is .sql, which contains the database tables and data. To create a database dump of the database, you need to follow this syntax:

mysqldump -u username -p database_name > database_dump.sql

The main command is mysqldump and the parameters are -u for the username and the -p for the password. The most important part of the command is the sign > which tells that this is exporting(dumping) the database. The rest is the database name and the name of the dump file you give.

So, the exact command in our case will be the following one:

mysqldump -u dbuser -p dbtest > dbtest.sql

Once done you can list the content of the directory to check for the database file:

ls -al | grep dbtest.sql

You should get the following output:

root@host:~# ls -al | grep dbtest.sql
-rw-r--r--  1 root root 1267 Dec  4 07:47 dbtest.sql

Importing Database

The MySQL import database is from an existing database dump, so we need to revert to the existing database, whether it is empty or not. If the database is not empty, the data will be overwritten with the MySQL import.

To import the MySQL database, you need to follow the syntax below:

mysql -u username -p database_name < database_dump_toimport.sql

The command is similar to the export, with two differences. The first is the word mysql instead of mysqldump, and the second most important difference is the sign <, which tells us that we import the database dump into the MySQL database.

So, to import the database we dumped in the previous step, we need to use the command below:

mysql -u dbuser -p dbtest < dbtest.sql

That’s it. You learned how to import and export databases using MySQL or MariaDB servers on Linux OS.

Of course, you don’t have to do this if you have difficulties and are unfamiliar with Linux and MySQL servers. You can always contact our technical support. You only need to sign up for one of our NVMe VPS server plans and submit a support ticket. We are available 24/7 and will take care of your request immediately.

If you liked this post about MySQL import and export databases, please share it with your friends or leave a comment below.

Leave a Comment