Using MariaDB via Docker on Digital Ocean

mariadb-docker-digitalocean.md

Notes on MariaDB and Docker

These notes describe how to start a MariaDB container with Docker on a Digital Ocean droplet running Ubuntu.

Installing Docker

Follow the instructions for Docker CE on Ubuntu:

sudo apt-get update
sudo apt-get install apt-transport-https ca-certificates curl software-properties-common

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

sudo apt-get update
sudo apt-get install docker-ce

sudo systemctl enable docker

sudo groupadd docker
sudo usermod -aG docker $USER

Getting MariaDB Image

Pull the latest image and use the Dockerfile to create an image with the MyRocks storage engine:

# Dockerfile
FROM mariadb:latest
RUN apt-get update && apt-get -y install mariadb-plugin-rocksdb && rm -rf /var/cache/apt/lists/*

docker pull mariadb:latest
docker build -t my-maria .

Starting the Container

Extensive instructions on using the image are available from the tutorial on Docker Hub.

First, we want to store data on the host system. So, create a directory for that. In this case, on a mounted Digital Ocean volume:

mkdir -p /mnt/vol-sfo02-01/my-maria-1
mkdir -p /mnt/vol-sfo02-01/my-maria-1/data
mkdir -p /mnt/vol-sfo02-01/my-maria-1/config

docker run --name my-maria-1 \
	-v /mnt/vol-sfo02-01/my-maria-1/config:/etc/mysql/conf.d \
	-v /mnt/vol-sfo02-01/my-maria-1/data:/var/lib/mysql \
	-e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
	-p 3306:3306 \
	-d my-maria

The above will...

• Create a container called my-maria-1
• Read any .cnf config files in /mnt/vol-sfo02-01/my-maria-1/config
• Keep data in /mnt/vol-sfo02-01/my-maria-1/data
• Map the container port 3306 to localhost:3006
• Leave the root MySQL password empty

Here is a starting place for a configuration file in /mnt/vol-sfo02-01/my-maria-1/config/my.cnf:

[server]
local-infile = 1
default-storage-engine = Aria
character-set-server = utf8
collation-server = utf8_general_ci
log-warnings = 2

Additional Configuration

At this point, you can connect to MariaDB from the host:

sudo apt-get install mariadb-client
mysql -h 127.0.0.1 -u root

Then you can use SHOW ENGINES to check that ROCKSDB is supported and create a database and any additional users...

CREATE DATABASE mydb CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
CREATE USER 'username' IDENTIFIED BY 'password';
GRANT ALL ON mydb.* TO 'username';

The next step is to get a bash prompt in the container and improve the security of the container's server:

docker exec -it my-maria-1 /bin/bash
mysql_secure_installation

Follow these prompts to improve the security of the server. Depending on how these are answered, it may no longer be possible to login as root from the Docker host machine.

Accessing the Container

From the Docker host machine use mysql -h 127.0.0.1 -u username -p to connect with the username created above.

Accessing the container from other machines can be done either by exposing a Droplet port or tunneling through SSH.