How To Import a Single Database in MySQL or MariaDB

<<Back to MySQL & MariaDB How To Page

Importing a Single Database in MySQL or MariaDB

Importing a database in Mysql and/or MariaDB is super easy. If you have export dump with you follow the steps below. See also How To Export a Single Database from MySQL or MariaDB if you have not exported the database yet.
Note:- MySQL and MariaDB is 100% compatible to each other and therefore the export taken from MySQL can be imported in MariaDB and vice verse.

In this demonstration I will import the database dump taken in  How To Export a Single Database from MySQL or MariaDB  post. from mysql Instance into MariaDB instance.

Step1> Login to your target database instance and check if the database name to be imported (test), exists or not.
In case DB Name already exists you need to rename the database while importing. See How to Import Database in MySQL or MariaDB with Different Name 

[root@host]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31052
Server version: 10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| mysql                |
| performance_schema   |
+----------------------+
3 rows in set (0.00 sec)


Step2> import the dump using mysql utility

[root@host]# mysql < /var/backup/tmp_test/test.sql

Step3> Validate the data once the import is done

• Check if the database is now exists

[root@host]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31098
Server version: 10.2.12-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| mysql                |
| performance_schema   |
| test                 |
+----------------------+
4 rows in set (0.00 sec)

• Check if all the tables are present compare the outcome from source

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]>
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1           |
| tab2           |
| tab3           |
+----------------+
3 rows in set (0.00 sec)

• Check if the contents of the table compare the result from source

MariaDB [test]> select count(*) from tab1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Note:- In case you are migrating the database, you additionally need to migrate the users and corresponding grants