(Notes) Notes (2020)

Deploy data to dockerized MySQL and MariaDB

There are two official MySQL documentation how to start MySQL in Docker and also common docker documentation.

But this installation and uplodaing data is not so simple, for example without additional parameters MySQL usually lost connection MySQL server has gone away. This is full list of running MySQL options in last MySQL version:



And there are a lot of various issues, for example MySQL not support option COMMENT, this is usual option of MariaDB databases.



MySQL can be started in docker in different way, for example in official MySQL documentation is recommended to start MySQL with random generated password:



Also, MySQL can be started in docker with external database (stored in Docker Volume) and external config files. And also there are different way to start MySQL:



So, this is my workable example of mysql.yml file. YML file is descriptive, rather than instructive Dockerfile. 

services:
        image: mysql
        volumes:
            - "db:/var/lib/mysql"
        environment:
            MYSQL_ROOT_PASSWORD: gh3DLaRuBadagfhjddczcvsde5mecis
            MYSQL_DATABASE: cryptodb1
            MYSQL_USER: cryptouser1
            MYSQL_PASSWORD: cryptopass1
        command: --connect_timeout  43200
                 --max_allowed_packet 1073741824
                 --net_buffer_length 1048576
        ports:
           - 42555:3306

Start MySQL from YML file sudo docker-compose --file mysql.yml up. Command mysqld will be performed by script docker-entrypoint.sh with parameters from YML file --connect_timeout 43200 --max_allowed_packet 1073741824 --net_buffer_length 1048576.



After service started we can see Stack in Portainer:



Container:



Volume with database:



To produce prototype of config file need to start MySQL as sudo docker run -it mysql cat /etc/mysql/my.cnf > my1.cnf



Than you can start MySQL with volume contains custom config sudo docker run -v /home/wwwadmin/:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=qwerty1 mysql, but firstly need to delete recursion in config flie (by comment in config line !includedir /etc/mysql/conf.d/)

In this type of start EntryPoint is docker-entrypoint.sh what perform command mysqld without parameters.



Also you can force MySql docker engine docker-entrypoint.sh perform bash interpreter sudo docker run -it mysql bash



Each start docker container create temporary files, for example I was start MySQL today ten times with different options, this process leave 10 temporary files.



Another alternative is tune something in docker, for example I will set to docker root password, than commit this docker with temporary file to image, than add tag, and than execute this image a lot of times or upload it to docker hub. Commit is alternative way to creation Image, rather than from Dockerfile.

# sudo docker run --name=qwerty -e MYSQL_ROOT_PASSWORD=qwerty1 -d mysql:latest 
# sudo docker commit e2a4ab052023c51c7722ec6ecb3f902f89f211751273ab6f9aedb7be3cb51583
# sudo docker tag 652d3178875f9cdb55e8b519de130160541a04dd88feb65189251ee6fb11db5d mysql:qwerty
# sudo docker run -it mysql:qwerty


Its a good idea to create own implementation of MySQL, because standard MySQL docker is extremely compact and contains only minimum as possible packages as adding to Linux core.



For uploading data, I done backup, share directory and uploading data. Alternative way is uploading with Workbench or PhpAdmin.

# mysqldump -u cryptochest -p cryptochest > cryptochest-db-$(date +%F).sql

This is example of creation initial database in Image creation time (pay attention each RUN instruction working in different layer, therefore to apply many commands to same layer need to write cumulative script or multiline command). So, I have included backup file to docker image.

FROM mysql
ENV MYSQL_ROOT_PASSWORD=qwerty1
ADD http://XXX.XXX.XXX.XXX/cryptochest-db-2020-11-30.tar /home/db_dump.sql
RUN mysqld_safe

# sudo docker build -f Dump-Dockerfile -t mysql:withdump .


And than run image contains file need to upload to db.

# sudo docker run -it -e MYSQL_ROOT_PASSWORD=qwerty1  mysql:withdump



And a couple of word about mariadb docker. Any my attempt to start this docker by YML was filed.



Therefore I start mariadb with pass all parameters by command line. In this case all working fine. Also it's possible to add -d before docker name mariadb, in this case log will be written to system log, not in ssh session with mariadb docker started.

# sudo docker run --name mariadb-serverQQQ --restart=always -p 42333:3306 -v /home/wwwadmin/db:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxxxx -e MYSQL_DATABASE=yyyyyy  -e  MYSQL_USER=zzzzzzzz -e MYSQL_PASSWORD=vvvvvvvvv  mariadb --connect_timeout  43200 --max_allowed_packet  1073741824


If you want start server with different parameters need to commit existing docker to image, than start new commited image with different parameters. In this case I want to add two options for start server --net_read_timeout 100 --innodb_buffer_pool_size 64M --verbose.



Interesting, that in some SQL-script Workbench causes error "Error Code: 2013. Lost connection to MySQL server during query". In this case I found another solution, start the same image as container and run SQL-script directly on the same machine. 

# sudo docker exec -i mariadb-server2 sh -c 'exec mysql -uroot -p"xxxxx"' < /home/wwwadmin/tst1.sql


And in the same way I restarted MySQL client interactively.

# sudo docker exec -it mariadb-server2 sh -c 'exec mysql -uroot -p"xxxxxxxx" -A'


For interactively start in this case, for example, I can unlock tables.


# select CONNECTION_ID();
# show open tables from `cryptochest`;
# show processlist;
# kill 10;




Comments ( )
Link to this page: //www.vb-net.com/DockerDatabase/Index.htm
< THANKS ME>