Docker-Compose persistent data MySQL

Question

I can't seem to get MySQL data to persist if I run $ docker-compose down with the following .yml

version: '2'
services:
  # other services

  data:
    container_name: flask_data
    image: mysql:latest
    volumes:
      - /var/lib/mysql
    command: "true"

  mysql:
    container_name: flask_mysql
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass' # TODO: Change this
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes_from:
      - data
    ports:
      - "3306:3306"

My understanding is that in my data container using volumes: - /var/lib/mysql maps it to my local machines directory where mysql stores data to the container and because of this mapping the data should persist even if the containers are destroyed. And the mysql container is just a client interface into the db and can see the local directory because of volumes_from: - data

Attempted this answer and it did not work. Docker-Compose Persistent Data Trouble

EDIT

Changed my .yml as shown below and created a the dir ./data but now when I run docker-compose up --build the mysql container wont start throws error saying

  data:
    container_name: flask_data
    image: mysql:latest
    volumes:
      - ./data:/var/lib/mysql
    command: "true"

  mysql:
    container_name: flask_mysql
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass' # TODO: Change this
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes_from:
      - data
    ports:
      - "3306:3306"


flask_mysql | mysqld: Can't create/write to file '/var/lib/mysql/is_writable' (Errcode: 13 - Permission denied)
flask_mysql | 2016-08-26T22:29:21.182144Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
flask_mysql | 2016-08-26T22:29:21.185392Z 0 [ERROR] --initialize specified but the data directory exists and is not writable. Aborting.

Excide

See my docker article and my minimal example – Rodroda

Check user ID for files in ./data – Maker

Answer 1

The data container is a superfluous workaround. Data-volumes would do the trick for you. Alter your docker-compose.yml to:

version: '2'
services:
  mysql:
    container_name: flask_mysql
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass' # TODO: Change this
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes:
      - my-datavolume:/var/lib/mysql
volumes:
  my-datavolume:

Docker will create the volume for you in the /var/lib/docker/volumes folder. This volume persist as long as you are not typing docker-compose down -v

Dignity

Starting with MySQL 5.7.6 there will (again) be permission problems with the mysql Docker image. You can instead use the mariadb Docker image, which works flawlessly with Docker volumes. – Quiz

This solution worked for me. A couple of side notes/precisions: 1. when you use the above configuration in docker-compose.yml and you deploy your service stack using "docker stack deploy -c docker-compose.yml mystack", there is no need to create the volume manually, it will be created as /var/lib/docker/volumes/mystack_my-datavolume automatically for you (notice that it prepends "mystack_" to the volume name). 2. I did not have to change permissions on my directory. Even if I have files owned by mysql:root in my container, they were created as 27:root on my Docker host without issues. – Jacquelinjacqueline

Why do you ever want to store it in var/lib/docker/volumes instead of having directory in your project folder like data/mysql? – Amido

@TheGodfather My guess would be if you don't want to deploy MySQL data to your production machine; otherwise, that's a very sound idea keeping everything together. – Dashtikavir

Can i create a folder in my root proyect and use it as database volumes? – Lanchow

It's simply not working on my end, the MySQL data doesn't persist on restart – Algorism

For readers who use "mysql/mysql-server" image, you will need to use "MYSQL_PASSWORD" instead of "MYSQL_PASS". Otherwise the config will not work. – Vaticination

@TheGodfather - This doesn't answer your question completely, but I was wary about using this solution because I was worried I would inadvertently do a docker-compose down and lose my volume. As it turns out, you have to do docker-compose down -v in order to permanently lose your volume, which made me feel much better about this whole approach. I think a better question may be, why create a directory yourself when docker can take care of it for you entirely? – Omsk

@Omsk I think because I feel myself more confident when all parts of my app and data are stored somehow together, and I have no dependencies on some volume from somewhere – Amido

- my-datavolume:/var/lib/mysql:Z dit it for me (workaround for permission problem ) – Wamble

Does anybody know why we cannot use our persistent volume folder (e.g. ./data/) but required to use the volume from docker? I tried the former on Linux and MacOS Mojave and both work fine. Only Windows failed. – Bronez

I'd say that mysql volume can be kept inside the project, just git/docker ignore it to don't have the data copied on production server (it depends on the deployment process thought) – Borrowing

/var/lib/docker/volumes will be created in host or docker? – Upcoming

Seems like /var/lib/docker/volumes will exist on the Docker host for Linux hosts, not inside the container. Also, for macOS hosts, this is NOT true. (you will not find it there). Read about it here: #38532983 – Narva

Answer 2

There are 3 ways:

First way

You need specify the directory to store mysql data on your host machine. You can then remove the data container. Your mysql data will be saved on you local filesystem.

Mysql container definition must look like this:

mysql:
  container_name: flask_mysql
  restart: always
  image: mysql:latest
  environment:
    MYSQL_ROOT_PASSWORD: 'test_pass' # TODO: Change this
    MYSQL_USER: 'test'
    MYSQL_PASS: 'pass'
volumes:
 - /opt/mysql_data:/var/lib/mysql
ports:
  - "3306:3306"

Second way

Would be to commit the data container before typing docker-compose down:

docker commit my_data_container
docker-compose down

Third way

Also you can use docker-compose stop instead of docker-compose down (then you don't need to commit the container)

Quiles

Can't I just have volumes: - /var/lib/mysql because it maps HOST:CONTAINER and if you don't specify w/ a colon it maps the same dir? – Excide

Not. unfortunately in this case docker map this container directory to random host folder like /var/lib/docker/volumes/ec3c543bc92f114c2c568733541e89381881e5a62996d7084e07793f86280535 – Quiles

Okay I thought volumes: - /var/lib/mysql was equivalent to volumes: - /var/lib/mysql:/var/lib/mysql – Excide

your thrid way will not work because docker does not commit data from volumes to the image. see github.com/moby/moby/issues/6999 – Dignity

In the first method, can two MySQL containers access and work on the same volume at once in parallel? – Lynda

perhaps a 4th way, is simply using docker compose restart instead of docker compose down – Bixby

the third way worked perfectly fine for me... thanks! – Valerle

shouln't volumes and ports be indented to the right? inside the mysql key? – Dinahdinan

Answer 3

first, you need to delete all old mysql data using

docker-compose down -v

after that add two lines in your docker-compose.yml

volumes:
  - mysql-data:/var/lib/mysql

and

volumes:
  mysql-data:

your final docker-compose.yml will looks like

version: '3.1'

services:
  php:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - 80:80
    volumes:
      - ./src:/var/www/html/
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    volumes:
      - mysql-data:/var/lib/mysql

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
volumes:
  mysql-data:

after that use this command

docker-compose up -d

now your data will persistent and will not be deleted even after using this command

docker-compose down

extra:- but if you want to delete all data then you will use

docker-compose down -v

Above

Answer 4

You have to create a separate volume for mysql data.

So it will look like this:

volumes_from:
  - data
volumes:
  - ./mysql-data:/var/lib/mysql

And no, /var/lib/mysql is a path inside your mysql container and has nothing to do with a path on your host machine. Your host machine may even have no mysql at all. So the goal is to persist an internal folder from a mysql container.

Beffrey

Wouldn't this be no different than changing my volumes under the data container to what you put under your volumes and simply had volumes_from: - data for mysql? Also attempted this and thew a new error. Says dir exists but not writable and mysql container will not run. – Excide

Of course you have to create a local folder with a path ./mysql-data (or whatever you would put before semicolon) – Beffrey

See my edit. Didn't get it in before you commented. But did create local dir. Looks like there is a permission issue now. – Excide

ERROR: Service "mysql" mounts volumes from "data", which is not the name of a service or container. – Six

I dont get it - what is volumes_from for? – Icon

Answer 5

Adding on to the answer from @Ohmen, you could also add an external flag to create the data volume outside of docker compose. This way docker compose would not attempt to create it. Also you wouldn't have to worry about losing the data inside the data-volume in the event of $ docker-compose down -v. The below example is from the official page.

version: "3.8"

services:
  db:
    image: postgres
    volumes:
      - data:/var/lib/postgresql/data

volumes:
  data:
    external: true

Drone

Answer 6

Actually this is the path and you should mention a valid path for this to work. If your data directory is in current directory then instead of my-data you should mention ./my-data, otherwise it will give you that error in mysql and mariadb also.

volumes:
 ./my-data:/var/lib/mysql

Crook

Both ways are valid if used correctly. The first, without the path notations is actually a named volume, which should be initialized before it can be used. In docker-compose file, we can have a top-level volumes section to configure the mapping of a name to a specific place on the host. After that the volume can be referenced by its name solely. – Istria

Answer 7

Feasible bind mount solution:

  mariadb:
    image: mariadb:latest
    restart: unless-stopped
    environment:
      - MARIADB_ROOT_PASSWORD=${MARIADB_ROOT_PASSWORD}
    volumes:
      - type: bind
        source: /host/dir
        target: /var/lib/mysql

Protectorate

Answer 8

In my case, I mounted a volume named 'mysql', and this word is reserved ! Think is !

Defalcation

What did you try to resolve the problem? – Cristicristian