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/
No comments:
Post a Comment