MySQL

Notes on moving MySQL (it was actually MariaDB on Debian) into a Docker container.

Back up databases

cd /green/BACKUPS/bellman_mysql for db in mysql asterisk owncloud yaris; do mysqldump -u root $db > $db.sql; done

Dockerize

2018-01-06 I followed my instructions and put MySQL into Docker on Dart today.

2017-08-27 today I created systemctl service for MySQL on Bellman, see /lib/systemd/system/docker-mysql.service

Notes: The local hostname does not matter because we publish port 3306, so the mysql service just looks like it's running on localhost. The link created by "--link db" in owncloud uses the name and the service there will appear on host "db" port 3306

I am using the official mariadb docker, during testing I did this on Bellman and Dart (both Debian 9 Linux)

Dart:

docker run -d --hostname=db --name=db -p 3306:3306 --user mysql \
-v /home/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=SECRET mariadb:latest

Bellman:

docker run -d --hostname=db --name=db -p 3306:3306 --user mysql \
-v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=SECRET mariadb:latest

That's how I start it for testing; in real life I use systemctl,

• create a file /lib/systemd/system/docker-mysql.service to issue that command (without the "-d")
• add a docker user 999 to /etc/password and /etc/group
• set ownership on /var/lib/mysql to docker:docker
• Start it with "systemctl start docker-mysql"

To run it on Murre (Windows 10) I tried this:

docker run -d --hostname=db --name=db -p 3306:3306 \
-v "//d/mysql_databases:/var/lib/mysql" -e MYSQL_ROOT_PASSWORD=SECRET mariadb:latest

In either case if it starts up correctly you will see files it creates in the shared directory (eg /home/mysql on Linux or D:/mysql_databases on Windows).

I named the Docker instance 'db' and gave it a hostname of 'db'. This allows linking with other databases by name and access from the host via the hostname. I can see it worked with

docker exec -it db mysql -u root -p mysql

select * from db;

I have to allow remote connections to the database so that asterisk can continue to use it. Likewise owncloud

CREATE DATABASE asterisk;
GRANT SELECT,INSERT,UPDATE,DELETE ON asterisk.* to asterisk@'%' IDENTIFIED BY 'SECRET';
CREATE DATABASE owncloud;
GRANT ALL ON owncloud.* to owncloud@'%' IDENTIFIED BY 'SECRET';
FLUSH PRIVILEGES;

You have to edit /etc/mysql/debian.cnf and my.cnf to change from connecting via a socket to protocol=tcp. You should be able to connect remotely with

mysql -u asterisk -p asterisk

You may have to edit /etc/odbc.ini to add Protocol = TCP.

After it's running then you no longer need the server package on the host, I had previously installed it on both Bellman and Dart in my case so I removed it.

apt-get remove mysql-server

That's it... if you are installing Owncloud go back to that page and continue.