How to Import and Export Databases in MySQL or MariaDB

Introduction

In this blog, you will work on database dumps in MySQL and MariaDB. In particular, you will explore exporting the database and importing the dump file. 
 

Step 1. Exporting a MySQL or MariaDB Database

The mysqldump utility exports databases in SQL files. It makes it easy to move and transfer databases. For exporting you need the database name and credentials for the database user who has at least read-only access to the database. 

Use mysqldump to export the database:

mysqldump -u username -p database_name > database-dump-file-name.sql

• username is used to log in to the database.
• database_name is the database for exporting.
• database-dump-file-name.sql is the file for storing the output in the current directory.

Check SQL dump file is legitimate

Run the following command:

Head -n 5 database-dump-file-name.sql

The dump file looks similar to this, showing a MySQL dump for a database named database_name.

-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       8.0.36-0ubuntu0.22.04.1

If any errors occur during database export, mysqldump utility will print them to the screen. 

Step 2. Importing a MySQL or MariaDB Database

To import an existing dump file into the database, you will create a new database. This database will hold or store the imported data. 

To create a new database first login into MySQL as root or another user with sufficient privileges.

mysql -u root -p

This command opens the MySQL shell prompt. Use it to create a new database named new_database.

CREATE DATABASE new_database;

Seeing this output means the database has been created.

Query OK, 1 row affected (0.00 sec)

Then exit from the MySQL shell prompt using the exit command. From the normal command line or terminal, you can import the dump file into the database with the following command:

mysql -u username -p new_database < database-dump.sql

• username is used to log in to the database.
• new_database is a newly created database.
• database-dump.sql is the database dump file to be imported, located in the current directory.

If the import command is run successfully, it won’t produce any output. If any errors occur during the process, MySQL will print on the command line or terminal. 

Conclusion

In this blog, you export a database dump from MySQL or MariaDB database. You import the exported database dump into the newly created database.