Setting up a MariaDB container

in case you’re lazy and don’t want to set it up yourself on the host system

Notice

• mariadb/mysql spits on port 3306
• based on upstream image tagged latest
• re-run without troubles (just keep the volume)
• shared accross different applications

Official image

Grab or update the MariaDB official image and check

docker pull mariadb:latest
docker images

Ready to go

Make sure the data folder doesn’t exist yet otherwise the init part that follows doesn’t make sense

app=mariadbprod
sudo ls -alhF /data/$app/
#sudo rm -rf /data/$app/

Generate a strong password that you would copy/paste to replace PASSWORD_HERE and USERPASS_HERE

sudo apt install pwgen # Ubuntu
sudo yum install pwgen # CentOS/RHEL - EPEL required
pwgen

Launch a MariaDB container mapping volume /data/mariadbprod/ to /var/lib/mysql/ and check

app=mariadbprod
docker ps -a | grep $app
docker run -d --name $app -h $app \
    -p 3306:3306 \
    -v /data/$app:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=PASSWORD-HERE \
    mariadb
docker ps -a | grep $app
docker logs $app

Note this is for the first run. For subsequents runs, remove -e MYSQL_ROOT_PASSWORD=PASSWORD_HERE.

Note -p 3306:3306 to allow access from the docker host or the network. Otherwise the db will only be used by other containers tru run --link which is good but prevents troubleshooting from the docker host or network. Also note that it depends on the MariaDB setup itself if you want to listen on the network (defaults to localhost only), so it’s almost safe to map that port to the docker host. I would recommend it.

There’s no need to tweak the my.cnf to allow remote connections unless you want to access the db from the docker host or from the network. Basically we are using localhost’s binding which is enabled by default. However, if you need to, here’s how to tweak it if needed

app=mariadbprod
docker exec -ti $app bash
apt -y update
apt -y install vim
export TERM=xterm
cp -pi /etc/mysql/my.cnf /etc/mysql/my.cnf.dist
vi /etc/mysql/my.cnf

Database setup

Setup the database e.g somedb

app=mariadbprod
docker exec -ti $app mysql -uroot -pPASSWORD_HERE

create database somedb;
grant all privileges on somdb.* to somedbuser identified by 'USERPASS_HERE';
flush privileges;

eventually inject some shit into it

app=mariadbprod
docker exec -ti $app mysql -usomedbuser -pUSERPASS_HERE somedb < ~/schema.sql

and check

app=mariadbprod
docker exec -ti $app mysql -usomedbuser -pUSERPASS_HERE somedb
show databases;
use somedb;
show tables;
^D

Usage

Access the instance as SQL root

app=mariadbprod
    docker exec -ti $app mysql -uroot -p
    show databases;

and also check as an SQL user

docker exec -ti $app mysql -u DUDE -p DUDE-PASSWORD dbname
show tables;

Network access

Check that you can access that DB from another container

app=tmpcheckmaria
docker run -ti --name $app -h $app --link mariadbprod:mariadb custom/ubuntu bash
cat /etc/hosts
ping -c1 mariadb
nc -z -v mariadb 3306
apt -y update && apt -y install mariadb-client

mysql -uroot -pPASSWORD_HERE -h mariadb
# --protocol=TCP

show databases;
use somedb;
show tables;
^D

Note we’re connecting to host defined by the --link :alias (here mariadb).

Note. custom/ubuntu already contains the ping & netcat packages.

Maintenance / Operations

Operating Docker Containers

References

https://mariadb.com/kb/en/mariadb/installing-and-using-mariadb-via-docker/

https://hub.docker.com/r/_/mariadb/

https://github.com/dockerfile/mariadb

sqlite & enhancements

http://litereplica.io/

https://github.com/rqlite/rqlite

https://www.sqlite.org/whentouse.html

https://www.sqlite.org/howtocorrupt.html

http://www2.sqlite.org/cvstrac/wiki?p=SqliteNetwork