Import and Export data in Dockerized MariaDB

Introduction

In the previous tutorial we installed Docker on a Rocky Linux instance and created a fresh MariaDB Docker image.

Now, it's time to import and export your data.

This tutorial is split into multiple parts.

Series index

Dockerizing MariaDB with Alpine Linux
Import and Export data in Dockerized MariaDB (You are here)
Connect to Dockerized MariaDB with Node.js via Unix socket

Prerequisites

Server with Rocky Linux 9

Step 1 - Update the bash script

If you haven't read the first tutorial, go to this link and start from there.

In order to import or export data, we have to add more scripts to main.sh. You might say: "What if I add a new script file for this purpose?" Adding a new script file would make our project more complex and we don't want that.

It's better to keep the Docker container process in one script and avoid multiple processes.

I added three new sections to main.sh. First one is the dump function, and other two sections are Import and Export.

main.sh

#!/bin/ash

set -e

signal_terminate_trap() {
    mariadb-admin shutdown &
    wait $!
    echo "MariaDB shut down successfully"
}

trap "signal_terminate_trap" SIGTERM

# Run
if [ "$REQUEST" == "run" ]; then
    echo "Starting MariaDB ..."
    exec mariadbd &
    wait $!
    exit 1
fi

# Initialize
if [ "$REQUEST" == "initialize" ]; then
    initialize_status="MariaDB is already initialized"

    if [ ! -f "$DIR_DATA/ibdata1" ]; then
        initialize_status="MariaDB initialization done"

        mariadb-install-db \
            --user=$USER \
            --datadir=$DIR_DATA \
            --auth-root-authentication-method=socket &
        wait $!
    fi

    echo $initialize_status
fi

# dump function will be used for export operation.
#
# This function has two parameters
# First param is "DATABASE_NAME"
# and the second one is "export_path".
#
# As you know, we use these params
# in the function body like this:
# $1 is the first param
# $2 is the second one
#
dump() {
    # Export database data with mariadb-dump
    #
    # Official mariadb-dump docs
    # https://mariadb.com/kb/en/mariadb-dump/
    #
    mariadb-dump \
        -u mysql \
        --skip-opt \
        --add-drop-table \
        --create-options \
        --quick \
        --single-transaction \
        --set-charset \
        --skip-comments \
        --add-drop-database \
        --add-drop-trigger \
        --triggers \
        --default-character-set=utf8mb4 \
        --databases $1 \
        --result-file="$2/data_$1.sql" &
    #
    # Wait for mariadb-dump until sucessfully done (exit)
    wait $!
}

# Import
#
# Read the note about .sql files you want to import
# in Step 2.
if [ "$REQUEST" == "import" ]; then
    exec mariadbd &
    #
    # Wait 5 seconds for MariaDB to start
    sleep 5 &
    wait $!

    # Search for .sql files in the import directory
    for i in $DIR_DUMP/import/*.sql; do
        #
        # Use mariadb client to import .sql files
        # into database.
        mariadb < $i &
        wait $!
    done

    mariadb-admin shutdown &
    wait $!
    echo "Import operation successfully done"
    exit 1
fi

# Export
#
# Read the note about export operation
# in Step 3.
if [ "$REQUEST" == "export" ]; then
    exec mariadbd &
    sleep 5 &
    wait $!

    # Use dump function to export our data
    # into the export directory.
    #
    # DATABASE_NAME is one of our environment variables
    # that comes from Docker's --env flag.
    # You will see how to set that in Step 3.
    dump $DATABASE_NAME "$DIR_DUMP/export"

    # Shutdown MariaDB with mariadb-admin
    # https://mariadb.com/kb/en/mariadb-admin/
    mariadb-admin shutdown &
    wait $!
    echo "Export operation successfully done"
    exit 1
fi

Now it's time to build the new image.

# Build
docker build --rm -t maria:2.0.0 --file Dockerfile ./

# Check Docker images on your Linux machine
docker image list --all

I use --rm flag to clean up the temporary container and remove the filesystem after the build is complete.
Specify the name of your image and its version with -t.
Specify Dockerfile with --file flag.

Step 2 - Import data

How does the import operation work?

First of all, you have to copy your .sql files into this /maria/dump/import/ directory of the Rocky Linux 9 instance.

I give you an example to see how it works.

maria/
│
└─── dump/
     │
     └─── import/
          │
          └─── data_tennis.sql

In the data_tennis.sql, I assume that you have a database named tennis and a table called players.

Click here for commands to add a database

[root@rocky-linux]# docker ps
[root@rocky-linux]# docker exec -it <container-id> /bin/sh
/ $ mysql -u mysql -p
MariaDB [(none)]> CREATE DATABASE tennis;
MariaDB [(none)]> USE tennis;
MariaDB [(tennis])]> CREATE TABLE players (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
MariaDB [(tennis])]> SHOW TABLES;

dump/import/data_tennis.sql

DROP DATABASE IF EXISTS tennis;

CREATE DATABASE
    IF NOT EXISTS tennis
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE tennis;
DROP TABLE IF EXISTS players;

CREATE TABLE `players` (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(40) NOT NULL,
    gender varchar(10) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO players VALUES (1, 'Novak Djokovic', 'Male');
INSERT INTO players VALUES (2, 'Maria Sharapova', 'Female');
INSERT INTO players VALUES (3, 'Roger Federer', 'Male');

Very good, you're ready for the import operation.

As you can see below, I specify our request environment variable like this --env REQUEST=import. Docker will pass this variable into the container's main process (main.sh).

We use the REQUEST variable in this line of main.sh:
- if [ "$REQUEST" == "import" ]; then

It's time to use docker run command.

# Import
docker run \
    --rm \
    --name maria_import \
    --user mysql \
    --env REQUEST=import \
    --env USER=mysql \
    --env DIR_DATA="/var/lib/maria" \
    --env DIR_DUMP="/var/lib/maria_dump" \
    -v /maria/config/my.cnf:/etc/my.cnf:ro \
    -v /maria/config/my.cnf.d/:/etc/my.cnf.d/:ro \
    -v /maria/data/:/var/lib/maria/ \
    -v /maria/dump/:/var/lib/maria_dump/ \
    -v /maria/logs/:/var/log/maria/ \
    -v /maria/socket/:/run/maria/ \
    maria:2.0.0

You will see the Import operation successfully done message in your terminal.

Click here for commands to view the table

[root@rocky-linux]# docker ps
[root@rocky-linux]# docker exec -it <container-id> /bin/sh
/ $ mysql -u mysql -p
MariaDB [(none)]> USE tennis;
MariaDB [(tennis])]> SELECT * FROM players;

Step 3 - Export data

In this step you have to define your export request environment variable like this --env REQUEST=export, and specify the name of the database you want to export like this --env DATABASE_NAME=tennis.

We use the REQUEST variable in this line of main.sh:
- if [ "$REQUEST" == "export" ]; then
We use the DATABASE_NAME variable in this line of main.sh:
- dump $DATABASE_NAME "$DIR_DUMP/export"

# Export
docker run \
    --rm \
    --name maria_export \
    --user mysql \
    --env REQUEST=export \
    --env USER=mysql \
    --env DIR_DATA="/var/lib/maria" \
    --env DIR_DUMP="/var/lib/maria_dump" \
    --env DATABASE_NAME=tennis \
    -v /maria/config/my.cnf:/etc/my.cnf:ro \
    -v /maria/config/my.cnf.d/:/etc/my.cnf.d/:ro \
    -v /maria/data/:/var/lib/maria/ \
    -v /maria/dump/:/var/lib/maria_dump/ \
    -v /maria/socket/:/run/maria/ \
    maria:2.0.0

At the end, you will see the Export operation successfully done message in your terminal.

Well done, you can see the exported .sql file in this /maria/dump/export/ directory.

Conclusion

In this tutorial, you were able to import and export your database data with a Dockerized MariaDB.

Import and Export data in Dockerized MariaDB

Author

Published

Time to read

Table of Contents