[Note] Mysql in Docker

 Get docker image

$ docker pull mysql


If db files on host is desired, 

Prep local file system:

$ mkdir /data/db/mysql
$ mkdir /data/db/mysql/data
$ mkdir /data/db/mysql/conf.d
$ mkdir /data/db/mysql/log

local file owner/group must match with what's on docker.  Either:

[1] change UID/GID of mysql process in the container, change all the file UID/GID in the container

[2] or, use --user option



[1] manually changing UID/GID in container

Instantiate mysql container for inspection.

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=<password> -p 3306:3306 -d mysql:latest

$ docker logs -f mysql

Get UID/GID on host and container for mysql

$ docker exec -ti mysql bash

e.g.
run these commands on both host and container:
$ id -u mysql
$ id -g mysql

Let say host UID/GID = 129, 139, and in container, it's 999, 999.  Record this.

Also check /etc/mysql/my.cnf in container to check datadir, and includedir -- record it:

    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    # Custom config should go here
    !includedir /etc/mysql/conf.d/

Change the UID/GID in container for 'mysql' UID/GID with the UID/GID on host:
$ usermod -u 129 mysql
$ groupmod -g 137 mysql 

Then change all the files' UID/GID:
$ find / -user 999 -exec chown -h 129 {} \; 
$ find / -group 999 -exec chgrp -h 137 {} \;

Exit out of the container, and commit the change as new image, then remove the container you just created:

$ docker commit mysql mysql-kkim  # commit and create new image
$ docker image list               # check if there is mysql-kkim
$ docker stop mysql               # stop and remove
$ docker rm mysql

Re-create again with proper volume mapping:

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=<password> \
    -v /data/db/mysql/data:/var/lib/mysql                   \
    -v /data/db/mysql/log:/var/log/mysql                    \
    -v /data/db/mysql.conf:/etc/mysql/conf.d                \
    -d mysql-kkim:latest

I don't recommend this -- unless you have to do this way for some reason.

[2] Using --user option,

I did not try this - it's on mysql container page.

Assuming the data directories are created, run:

$ docker run --name mysql --user 129:137
    -e MYSQL_ROOT_PASSWORD=<password>            \
    -v /data/db/mysql/data:/var/lib/mysql        \
    -v /data/db/mysql/log:/var/log/mysql         \
    -v /data/db/mysql.conf:/etc/mysql/conf.d     \
    -d mysql:latest


Dump data

$ docker exec mysql_container sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

Restore data from dump
 
$ docker exec -i mysql_container sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" schema_name' < /some/path/on/your/host/all-databases.sql

e.g. 
$ docker exec -i mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" dev' < *.sql


Using MySQL Workbench

If SNAP version is installed, make sure to give more permission



Closing

I didn't go either [1] or [2] but using container managed volumes.  It's easier/cleaner and I don't see the need.  I can always dump the DB and backup.  Don't attempt to back up database file directory and restore -- it's a lot of headache/hassle.  Third way is using docker volume and use it in the container.



REFERENCE

• https://hub.docker.com/_/mysql
• https://severalnines.com/database-blog/mysql-docker-containers-understanding-basics
• https://codeblog.dotsandbrackets.com/persistent-data-docker-volumes/