Docker mysql image - move internal container DB to the volume

Question

I have started using docker with docker-compose where I have the database defined. Since I did not have volumes defined in the configuration (my mistake), the database has been created inside the container and now next time when I run docker-pull, I will get this database re-created causing my data loss. What would be the best way to move the data out of the container without losing the data?

  db:
    image: "mydbimage"
    environment:
      MYSQL_ROOT_PASSWORD: "${DB_ROOT_PASSWORD}"
      MYSQL_DATABASE: "jirio"
      MYSQL_USER: "${DB_USERNAME}"
      MYSQL_PASSWORD: "${DB_PASSWORD}"

Dockerfile

FROM mysql:5.6
ADD schema.sql /docker-entrypoint-initdb.d/schema.sql

You can find all the files in `/var/lib/docker/` in your host filesystem if you haven't deleted your container. — Boynux, May 14 '17 at 09:06

Ricardo Branco · Accepted Answer · 2017-05-26T15:28:32.470

You won't lose your data if you run docker pull mysql:5.6 since you were running an instance of mydbimage.

Also, there is no data loss unless you remove the container with docker rm -v. Docker Compose does not remove volumes, either, unless you run docker-compose rm -v.

Get the name or ID of the container that was running mysql:5.6 with docker ps -a and run:

docker run --rm --volumes-from $container ubuntu tar -zcvf- /var/lib/mysql > /tmp/backup.tar.gz

Note: Substitute $container with the name or ID of the container.

Then you can decompress the tarball to some location on the host, say /var/mysql, update mydbimage if you want, and add this line to your Docker Compose YAML:

volumes:
  -v /var/mysql:/var/lib/mysql

NOTE: A docker commit never saves the volumes in a container.

That seems more precise than my answer. +1 – VonC May 14 '17 at 16:22 — VonC, May 14 '17 at 16:22

score 1 · Answer 2 · edited May 23 '17 at 10:31

1

If you still have the container (exited and stopped) with that data, your first step would be to commit it as an image in order to not loose the data.
See docker container commit. That will save the container content, not its volume... but there was no volume in it anyway.

If your container has defined its own internal volume, see if "How can I backup a Docker-container with its data-volumes?" applies to your case.

Then you can run a new container based on that committed image (with a shell as a command), this time creating and mounting a new data volume container.
Once running, in that shell, you can zip/copy your data into the volume path (which will actually write onto your data volume container)

Finally, you can reuse that data volume container into your existing docker-compose.yml

Knowing that the OP's image is docker mysql:5.6, its Dockerfile defines its own internal volume, which is not backed up by default.

In that case, if the container is still running, the easiest approach is to make a dump of the database:

# Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql

Once you have that data, you can run the image again, this time with an host folder mounted a volume, or with a data volume container.

See "MySQL on Docker: Building the Container Image"

But if the container is stopped, you cannot execute command in it (issue 30361)

edited May 23 '17 at 10:31

Community

1
1

answered May 14 '17 at 05:11

VonC

1,262,500
529
4,410
5,250

A `docker commit` never saves the volumes of a container. – Ricardo Branco May 14 '17 at 05:47
@RicardoBranco the all point of this question was that the container had *no* volume at all. The goal of the commit is to save whatever was written in the container. – VonC May 14 '17 at 05:48
It depends if the original image (mysql, postgres, etc.) defines a volume. – Ricardo Branco May 14 '17 at 05:52
@RicardoBranco possible, but the "Since I did not have volumes defined" told me otherwise. Let us wait for the OP's comment on this. – VonC May 14 '17 at 05:55
@RicardoBranco I have added a link to a volume backup process, in case the OP's container *did* include its own volume. That should cover both scenario. – VonC May 14 '17 at 05:58
Guys, thanks for answer. my configuration had no volumes at all. – Sergei Ledvanov May 14 '17 at 09:13
@ser OK. Hence my suggestion to freeze and commit the container. Still, check if the base image for that container has its own internal volume – VonC May 14 '17 at 09:15
@VonC thank you. Could you please let me know what exact steps and comomands I should invoke since the end going is to have running everything with docker-compose again. – Sergei Ledvanov May 14 '17 at 09:20
@ser first, do you still have your container with the data you want to preserve? – VonC May 14 '17 at 09:21
@VonC yes I have that container – Sergei Ledvanov May 14 '17 at 11:29
@SergeiLedvanov Did you manage to commit it as a new image, as described in the answer? – VonC May 14 '17 at 14:34
@SergeiLedvanov and is your container still running? (see my edited answer) – VonC May 14 '17 at 15:32

Docker mysql image - move internal container DB to the volume

2 Answers2