Introduction

You can use data dumps for backup and restoration purposes, or you can use them to migrate data to a new server or development environment.

Working with database dumps in MySQL and MariaDB is straightforward. This tutorial will cover how to export the database as well as import it from a dump file in MySQL and MariaDB.

Prerequisites

To import and/or export a MySQL or MariaDB database, you will need:

• Access to the Linux server running MySQL or MariaDB
• The database name and user credentials for it

Exporting the Database

The mysqldump console utility is used to export databases to SQL text files. These files can easily be transferred and moved around. You will need the database name itself as well as the username and password to an account with privileges allowing at least full read only access to the database.

Export your database using the following command.

mysqldump -u username -p database_name > dump.sql

• username is the username you can log in to the database with

• database_name is the name of the database that will be exported

• dump.sql is the file in the current directory that the output will be saved to

The command will produce no visual output, but you can inspect the contents of sql file to check if it's a legitimate SQL dump file by using:

head -n 5 dump.sql

The top of the file should look similar to this, mentioning that it's a mariadb dump for a database named database_name.

SQL dump fragment

-- MySQL dump 10.16  Distrib 10.1.20-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: localhost
-- ------------------------------------------------------
-- Server version    10.1.20-MariaDB-1~jessie

If any errors happen during the export process, mysqldump will print them clearly to the screen instead.

Importing the Database into docker container

To import an existing dump file into MySQL or MariaDB, you will have to create the new database. This is where the contents of the dump file will be imported.

I have an existing mariadb docker container running already locally.

docker run --name mariadb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password1 -e MYSQL_DATABASE=db -d mariadb:latest

Now verify it is running

docker ps

You will see output similar to below:

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                            NAMES
7cfaabda3346        mariadb:latest      "docker-entrypoint..."   5 days ago          Up 5 days           0.0.0.0:3306->3306/tcp                           mariadb

First, start a bash session inside container.

docker exec -it mariadb bash

Second, log in to the database as root or another user with sufficient privileges to create new databases.

mysql -u root -ppassword1

This will bring you into the mariadb shell prompt. Next, create a new database called new_database.

CREATE DATABASE new_database;

Now exit the MySQL shell by pressing CTRL+D. Exit the docker container also.On the normal command line, you can import the dump file with the following command:

docker exec -i mariadb mysql -uroot -ppassword1 --database=new_database < dump.sql

• username is the username you can log in to the database with
• new_database is the name of the freshly created database
• dump.sql is the data dump file to be imported, located in the current directory

The successfully run command will produce no output. If any errors occur during the process, mysql will print them to the terminal instead. You can check that the database was imported by logging in to the MySQL shell again and inspecting the data.

Conclusion

You now know how to create database dumps from MySQL databases as well as how to import them again. mysqldump has multiple additional settings that may be used to alter how the dumps are created, which you can learn more about from the official mysqldump documentation page.

To find out more about docker commands check out the docker doc here