How To Import and Export MySQL Database

MySQL is an relation database management system to storing data in table format. It is an opensource database server available to install on variety of operating systems

In case of mysql database migration, you can easily create a dump of database and restore it on target database server. MySQL server provides console utilities to export and import databases.

This tutorial help you to export MySQL database using system console. Also helped you to restore database from dump file.

Step 1 — Export MySQL Database

Use mysqldump command line utility to perform a database backp. Which makes the process more easier to transfer database to other system. In order to export database, you need database’s name and login credentials with at least read-only privileges to the databases.

Let’s export your database using mysqldump:

mysqldump -u root -p database_name > db_backup.sql 

Here:

• root – is the username to login to database server
• database_name – is the name of the database to export
• db_backup.sql -is the text file name, that will stores the output

The above command will run silently with no output on screen. If any errors occur during the export process, mysqldump will print them to the screen.

Step 2 — Verify Backup File

Let’s, verify the database dump file created in above step. First make sure there is no error displayed on screen with mysqldump command.

Next, Run the following command:

head db_backup.sql 

This will show you the database details like below

-- MySQL dump 10.13  Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       8.0.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;

Next, Run the following command to view the last line from the backup file.

tail db_backup.sql 

/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOT[email protected]_SQL_NOTES */;

-- Dump completed on 2021-02-02 17:07:24

The last line must contain a message like “Dump completed” followed by the date time.

Step 3 — Import MySQL Database

Before importing the data from backup file, make sure to create database on database server.

You can use “mysqladmin” console command to create a new database. To create a new database, execute:

mysqladmin -u root -p create new_database_name 

Enter mysql user password to complete process. This will create a database in mysql server.

Next, you can import the dump file using “mysql” console command. The command will be like below:

mysql -u root -p new_database_name < db_backup.sql

Here:

• root - is the username to access database server
• database_name - is the name of the newly created database
• db_backup.sql -is the dump file name taken from source database server.

On successfuly command execution, you will get the command prompt back without any message. In case of any error occurs with restore process, the error message will be printed on terminal screen.

Now, you can connect your database and access database tables from mysql shell.

Conclusion

In this tutorial, you have learned to create dump of mysql database. Then create a new database on destination and restore from backup file.

You can visit mysqldump official documentation page to read more about this command.