Commit data in a mysql container

Question

I created a mysql container using the officially supported mysql image. I ran the image mounting a folder that contains a sql dump, then I created a new database in the container and imported the .sql dump in it:

sudo docker run --name mysql-psat1 -v /opt/Projets/P1/sqldumps:/mnt -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest
sudo docker exec -it mysql-psat1 bash
> mysql -uroot -psecret -e 'create database liferay_psat1;'
> mysql -uroot -psecret liferay_psat1 < /mnt/liferay_sql_dump.sql

Then I listed the running containers to get that container's id:

sudo docker ps -a

Then, I commited the container (with the imported sql) as a new container image

sudo docker commit -m "Imported liferay sql dump" <id-of-the-container> jihedamine/mysql-psat1:v1

However, when if I start a container using that new image, the mysql database doesn't contain the newly created database liferay_psat1.

sudo docker run -ti jihedamine/mysql-psat1:v1 bash
> mysql -uroot -psecret
# show databases;

What am I doing wrong?

Thanks for your help!

Answer 1

The official mysql image stores data in a volume. Normally this is desired so that your data can persist beyond the life span of your container, but data volumes bypass the Union File System and are not committed to the image.

You can accomplish what you're trying to do by creating your own mysql base image with no volumes. You will then be able to add data and commit it to an image, but any data added to a running container after the commit will be lost when the container goes away.

Answer 2

Try to connect to the running container outside via the liking containers:

sudo docker run --name mysql-psat1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest
docker run --rm -i -t -v /opt/Projets/P1/sqldumps:/mnt --link mysql-psat1:mysqlserver mysql:latest /bin/bash
> mysql --host $MYSQLSERVER_PORT_80_TCP_ADDR --port $MYSQLSERVER_PORT_80_TCP_PORT -uroot -psecret -e 'create database liferay_psat1;'
> mysql --host $MYSQLSERVER_PORT_80_TCP_ADDR --port $MYSQLSERVER_PORT_80_TCP_PORT -uroot -psecret liferay_psat1 < /mnt/liferay_sql_dump.sql

Then stop both containers and commit the servers container which has name mysql-psat1

Answer 3

If you are using docker-compose, there is no need to build a custom image. Just add the flag --datadir=/var/lib/mysql-no-volume to your command when pulling the mysql image.

Example:

version: '2.4'
services:
  mysql:
    image: mysql:5.7
    command: "mysqld --character-set-server=utf8 --collation-server=utf8_general_ci --datadir=/var/lib/mysql-no-volume"

Answer 4

Based on the @dekin research, I did this to solve the issue:

Dockerfile:

FROM mysql:latest

RUN cp -r /var/lib/mysql /var/lib/mysql-no-volume

CMD ["--datadir", "/var/lib/mysql-no-volume"]

Build & run:

docker build . -t my-mysql
docker run -e MYSQL_ROOT_PASSWORD=root -it my-mysql

Answer 5

Based on @Robert answer, I ended up with this Dockerfile:

FROM mysql:5.6.22

RUN cp -r /var/lib/mysql /var/lib/mysql-no-volume

RUN sed -i -e "s|/var/lib/mysql|/var/lib/mysql-no-volume|" /etc/mysql/my.cnf

The CMD override didn't work for me, container stopped with a strange error:

2019-02-21 15:18:50 1 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2019-02-21 15:18:50 1 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

I guess that the original CMD command was doing more things that is now missing (in @Robert answer) so I did it in a different approach. I didn't try it with latest version but I think it should work.