My MariaDB Docker Image and Volume

Introduction

I want to generate a customized MariaDB Docker image that I can use to create more MariaDB containers for further testing in a Docker environment.

Prerequisites

A running Docker host in any OS. We are using a virtual machine instance in Google Cloud Platform with Container Optimized OS as it already includes Docker 19.03.

Build Base Container

Pull Cfficial MariaDB Image

We are going to search for the MariaDB image in the Docker Hub and pull the official one:

pato@patocontainer ~ $ docker search mariadb
NAME                                   DESCRIPTION                                     STARS               OFFICIAL            AUTOMATED
mariadb                                MariaDB is a community-developed fork of MyS…   3476                [OK]

pato@patocontainer ~ $ docker pull mariadb
Using default tag: latest

pato@patocontainer ~ $ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
mariadb               latest              ac9c11a18222        3 minutes ago       357MB

Create the New Volume

We know this official image is using a volume to store the datadir /var/lib/mysql that is the directory where all the data and binary logs are stored. So I’m going to create a particular volume to store that data.

pato@patocontainer ~ $ docker volume create --name patovolmariadb
patovolmariadb

Build Container from Official Image

Now we are going to create our container with docker run using the official public image mariadb.
We are redirecting the datadir to our newly create volume and we are setting the first time password for the MariaDB root user with the instruction -e MYSQL_ROOT_PASSWORD.

pato@patocontainer ~ $ docker run -d --name patomariadb -v patovolmariadb:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxxxxx mariadb
e73e6f0a6f9aa645fdce67f05be80312942ed346ce3fc40714c9aadbbbd97c5b

pato@patocontainer ~ $ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
e73e6f0a6f9a        mariadb             "docker-entrypoint.s…"   12 seconds ago      Up 10 seconds       3306/tcp            patomariadb

pato@patocontainer ~ $ docker volume ls
DRIVER              VOLUME NAME
local               patovolmariadb

Container Login

Connect to our newly created container with a shell session using docker exec -it patomariadb bash:

pato@patocontainer ~ $ docker exec -it patomariadb bash
root@e73e6f0a6f9a:/# 

Software Installation

First update Ubuntu, set your time zone and install edition and network tools:

root@e73e6f0a6f9a:/# apt update && apt upgrade -y
Get:1 http://ftp.osuosl.org/pub/mariadb/repo/10.4/ubuntu bionic InRelease [6265 B]

root@e73e6f0a6f9a:/# ln -fs /usr/share/zoneinfo/America/Mexico_City /etc/localtime

root@e73e6f0a6f9a:/# apt install -y vim nano net-tools iputils-ping
Reading package lists... Done

Create Administration OS User

We create a user to access and administrate MariaDB container to avoid using root:

root@e73e6f0a6f9a:/# adduser pato
Adding user `pato' ...
Adding new group `pato' (1000) ...

Database Users and Security

Enable unix passwordless authentication for the the root user and secure your installation with the procedure mysql_secure_installation:

root@e73e6f0a6f9a:/# mysql_secure_installation
Enter current password for root (enter for none):

Switch to unix_socket authentication [Y/n] Y
Change the root password? [Y/n] n

Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

Connect to the database:

root@e73e6f0a6f9a:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.4.13-MariaDB-1:10.4.13+maria~bionic mariadb.org binary distribution

MariaDB [(none)]> use mysql 

Create a database user for our administrator user with plugin unix_socket for passwordless local connection:

MariaDB [mysql]> CREATE USER 'pato'@'localhost' IDENTIFIED VIA unix_socket;

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'pato'@'localhost' WITH GRANT OPTION;

Create a remote user that can connect from any host @'%':

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'remoto'@'%' IDENTIFIED BY '********';

Create backup user with proper permissions:

MariaDB [mysql]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO respalda@localhost IDENTIFIED BY '********';

Create a user for MaxScale monitor with suitable privileges:

MariaDB [mysql]> GRANT SELECT ON mysql.* TO 'maxpato'@'%' IDENTIFIED BY '********';

MariaDB [mysql]> GRANT SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'maxpato'@'%';

and flush privileges so new grants get activated:

MariaDB [mysql]> FLUSH PRIVILEGES;

Create a Safe Database Backup

Using mysqldump make a backup of the database as a safe guard:

root@e73e6f0a6f9a:~# mysqldump --all-databases > initial-db.sql

Shutdown the Instance

Now that our MariaDB has been prepared we are going to shutdown it by running mysqladmin command:

root@e73e6f0a6f9a:~# mysqladmin shutdown

or by issuing docker stop container:

pato@patocontainer ~ $ docker stop patomariadb
patomariadb

Create our Image from the Customized Container

Finally we commit our container to create an Docker Image and tag it to our repository patomx:

pato@patocontainer ~ $ docker commit patomariadb patomx/patomariadb
sha256:143534ffe233a383ef93aa9267deaae01a7fc6b0c309439e4fb5a5791c635a7f

pato@patocontainer ~ $ docker image ls
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
patomx/patomariadb    latest              143534ffe233        12 seconds ago      453MB
mariadb               latest              ac9c11a18222        53 minutes ago      357MB

pato@patocontainer ~ $ docker container rm patomariadb
patomariadb

and then we can push our image to the Docker Hub:


pato@patocontainer ~ $ docker login --username=patomx
Password:
Login Succeeded

pato@patocontainer ~ $ docker push patomx/patomariadb
The push refers to repository [docker.io/patomx/patomariadb]
ab834738fc97: Pushed
...
latest: digest: sha256:
pato@patocontainer ~ $

Replicate MariaDB Instance

Now that we have generated our own MariaDB Image patomx/patomariadb we are going to create more MariaDB Containers.

Generate New Container(s)

To generate a new MariaDB instance you issue the docker run command using our customized image patomx/patomariadb.

pato@patocontainer ~ $ 
docker run -d --name patomariac1 --network pato-net -v patovolmc1:/var/lib/mysql patomx/patomariadb
162aae1fbb3cca423d488150f6b482cf9fe29b3b2266b49bd692e8618bc93ae3

pato@patocontainer ~ $ docker stop patomariac1
patomariac1

Remeber we are using pato-net our own Docker Bridge Network and we also Tag Docker Volume for the datadir directory /var/lib/mysql.

Cloning Saved Data to New Container Volume

And we want the data generated in the original container to be copied in this new one:

pato@patocontainer ~ $ docker run --rm -i -t -v patovolmariadb:/origen -v patovolmc1:/destino alpine sh -c "cp -avr /origen/* /destino"	
'/origen/aria_log.00000001' -> '/destino/aria_log.00000001'
'/origen/aria_log_control' -> '/destino/aria_log_control'
...

pato@patocontainer ~ $ docker start patomariac1
patomariac1

pato@patocontainer ~ $ docker ps
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS              PORTS               NAMES
162aae1fbb3c        patomx/patomariadb   "docker-entrypoint.s…"   38 seconds ago      Up 33 seconds       3306/tcp            patomariac1

pato@patocontainer ~ $ docker volume ls
DRIVER              VOLUME NAME
local               patovolmc1
local               patovolmariadb

And that’s it, we have a new cloned container.

Validate our New Instance

OS Customizations

In the original image we installed edition and network software, let’s validate if they persist:

pato@patocontainer ~ $ docker exec -it patomariac1 bash
root@162aae1fbb3c:/# nano -V
 GNU nano, version 2.9.3

root@162aae1fbb3c:/# netstat -na | grep LISTEN | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN

also we created a backup, let check it still there:

root@162aae1fbb3c:/# ls /root/initial-db.sql
/root/initial-db.sql

OK, our OS customizations were persisted!

Database Customizations

To validate database we are going to access our newly created container using the administrator user pato and connect passwordless to the database, as this was one customization I wanted to preserve in my Image:

pato@patocontainer ~ $ docker exec -it --user pato patomariac1 bash
pato@162aae1fbb3c:/$ mysql

MariaDB [(none)]>

we connected successfully, so we want to validate all previously created users are present in our database:

MariaDB [(none)]> use mysql
Database changed

MariaDB [mysql]> select user, host, plugin from user;
+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | mysql_native_password |
| pato        | localhost | unix_socket           |
| remoto      | %         | mysql_native_password |
| respalda    | localhost | mysql_native_password |
| replicador  | %         | mysql_native_password |
| maxpato     | %         | mysql_native_password |
+-------------+-----------+-----------------------+
7 rows in set (0.052 sec)

OK every data was duplicated here from our custom Image!

Conclusion

We were able to create a customized base image of MariaDB instance for future duplication.
As the official image is minimal we added text edition and network software. Also secured the installation and created common users.

With our own Docker Image and Volume now we can easily create new MariaDB containers for clustering and replication, avoiding redo the installation or customization each time.