How to Import Database in MySQL or MariaDB with Different Name

<<Back to MySQL & MariaDB How To Page

Importing Database in MySQL or MariaDB with Different Name

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> Edit the dumpfile with suitable editer and locate the following contents
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `test`;

Step2> Replace the database name with desired name. In this example I have replaced test with wccbox
--
-- Current Database: `wccbox`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wccbox` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `wccbox`;

Caution!!  MySQL may crash (due to memory exception) if you open a big dump file using vi

Step3> Ensure the database name you are using is unique and does not already exists
[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)

Step4> import the dump using mysql utility
[root@host]# mysql < /var/backup/tmp_test/test.sql
Step5> 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   |
| wccbox|
+----------------------+
4 rows in set (0.00 sec)
Check if all the tables are present compare the outcome from source
MariaDB [(none)]> use wccbox;
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