Table of Contents

Background

Recently, when I setup a number of Docker Compose stacks for myself, I naively used MySQL 8 databases. I had heard of MariaDB in the past, and knew it was a drop-in replacement to MySQL, but I didn’t really know much about it. After recently reading about some of MariaDB’s features and speed-improvements over MySQL Community Edition (and many people including myself really hate Oracle), I decided I wanted to swap out my MySQL databases. This ended up being more complicated than I initially hoped.

Challenge

The challenge was that I had previously used MySQL 8 for my databases. This presented the problem that I simply couldn’t stop the old MySQL container, and start a new MariaDB container with the existing volume of the /var/lib/mysql folder (which contains MySQL’s data) as is supported with older versions of MySQL. This meant that I would need to export the data from each database, delete the volume, start a new MariaDB database, and reimport the data. While it sounds simple enough, it quickly became very tedious.

Process

The general process was as follows:

  • Dump the MySQL database of the current database container.

    sudo docker exec -i container_name mysqldump -u username -p database > database_db.sql

  • Stop the Docker Compose stack.

    sudo docker-compose down

  • Change the docker-compose.yml file to use MariaDB.

1-   command: '--default-authentication-plugin=mysql_native_password'
2        env_file:
3            - ./.env
4-   image: 'mysql:latest'
5+   image: 'mariadb:latest'

  • Delete the old data volume.

    sudo docker volume rm volume_name

  • Start the modified Docker Compose stack.

    sudo docker-compose up -d

  • Wait for the MariaDB container to become ready, and load in the data.

    sudo docker exec -i container_name mysql -u username -p database < database_db.sql

Automating

After doing this process by hand twice, I quickly decided to write a script to do it for me as I use a standard naming convention for all of my Docker Compose stacks and really wanted to speed up the process

WARNING: DON’T USE THIS SCRIPT WITHOUT GOOD BACKUPS

 1#!/bin/bash
 2
 3# inputs
 4
 5echo "Enter application name (lower case):"
 6read APPNAME
 7
 8read -p "Enter the directory with the docker-compose.yml file: " -e -i `pwd`/$APPNAME DOCKERDIR
 9read -p "Enter the container name: " -e -i ${APPNAME}_db_1 CONTAINERNAME
10read -p "Enter the container volume name: " -e -i ${APPNAME}_db_data VOLUMENAME
11read -p "Enter the MySQL database: " -e -i $APPNAME DATABASE
12read -p "Enter the MySQL username: " -e -i $APPNAME USERNAME
13read -p "Enter the MySQL password: " -s PASSWORD
14
15# -----------------
16# Data export
17
18FILENAME=${DATABASE}_db.sql
19
20echo "Changing directories to $DOCKERDIR"
21cd $DOCKERDIR
22
23echo "Dumping MySQL database to $FILENAME"
24sudo docker exec -i $CONTAINERNAME mysqldump -u $USERNAME -p$PASSWORD $DATABASE > $FILENAME
25
26echo "Stopping containers"
27sudo docker-compose down
28
29# -----------------
30# Wait for user to modify compose file
31
32read -p "Press enter once you have modified the docker-compose.yml file"
33
34# -----------------
35# Data import
36
37echo "Changing directories to $DOCKERDIR"
38cd $DOCKERDIR
39
40echo "Deleting old volume $VOLUMENAME"
41sudo docker volume rm $VOLUMENAME
42
43echo "Starting containers"
44sudo docker-compose up -d
45
46echo "Waiting 90 seconds"
47sleep 90
48
49echo "Loading MySQL database from $FILENAME"
50echo "sudo docker exec -i $CONTAINERNAME mysql -u $USERNAME -p$PASSWORD $DATABASE < $FILENAME"
51sudo docker exec -i $CONTAINERNAME mysql -u $USERNAME -p$PASSWORD $DATABASE < $FILENAME
52
53#echo "Removing $DOCKERDIR/$FILENAME"
54#rm $DOCKERDIR/$FILENAME

Conclusion

With a bit of time and effort, I’ve now converted all my MySQL databases to MariaDB as part of my goal of ridding myself of all Oracle software.