Docker DB Migration/Deployment to DigitalOcean

Question

Warning: I am fairly new to docker and cloud hosting, this is likely a dumb question.

I have a local web app which has 3 images associated with it, the app itself, the db and a phpmyadmin image. All works well locally, and if I transfer all the files to my digital ocean droplet and bring up my containers it works fine there as well, but this is not how I want to deploy having every file from every library residing in my droplet.

I have been experimenting with creating a docker-machine on my droplet and deploying my containers remotely to it. This seems to work fine other than the fact that my db image does not reference my database and is simply an empty db. I tried to migrate the db in this fashion which I saw in a tutorial:

docker-compose run --rm web db:create db:migrate

But got the following error, I assume this is because my dev machine is running Windows 10 not Linux, but I cannot find anywhere what the equivalent command would be for a Windows machine.

Error response from daemon: OCI runtime create failed: container_linux.go:346: starting container process caused "exec: \"db:create\": executable file not found in $PATH": unknown

I know I am probably missing something really stupid and easy but I am having difficulties figuring out how to migrate the data for my db image. Thanks in advance.

UPDATE: As requested here is my docker-compose:

    version: "3.4"

services:
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    environment:
      - PMA_ARBITRARY=1
      - PMA_HOST=db
    restart: always
    ports:
      - 80:80
    volumes:
      - /sessions
    depends_on:
      - db
  db:
    image: mysql:latest
    environment: 
      MYSQL_ROOT_PASSWORD: mypass
      MYSQL_DATABASE: mydb
    ports:
      - "3306:3306"
    volumes:
      - ./data:/docker-entrypoint-initdb.d
    restart: always
  web:
    depends_on:
      - db
    build: .
    ports:
      - "8080:8080"
    restart: always
volumes:
  data:

UPDATE #2: transfered db file to /docker-entrypoint-initdb.d (I tried this yesterday too but couldn't get it working) and created a new production docker-compose-prod.yml I must be missing something still though as the DB is still empty. Below is my new docker-compose-prod.yml:

version: "3.4"

services:
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    environment:
      - PMA_ARBITRARY=1
      - PMA_HOST=db
    restart: always
    ports:
      - 80:80
    volumes:
      - /sessions
    depends_on:
      - db
  db:
    image: mysql:latest
    environment: 
      MYSQL_ROOT_PASSWORD: mypass
      MYSQL_DATABASE: mydb
    ports:
      - "3306:3306"
    volumes:
      - /docker-entrypoint-initdb.d
    restart: always
  web:
    depends_on:
      - db
    build: .
    ports:
      - "8080:8080"
    restart: always

Answer 1

Your strategy is sound.

Actually, you can take it a further step by automating the Droplet provisioning to e.g. use a container-oriented OS and access your Compose file. But that's not this question ;-)

I think it is not relevant that you're using Windows and probably makes little difference; it may require some answer tweaks but that's about it.

The challenge is that you need to move (or recreate) the database state on the remote machine. There are several ways that the DB state could be persisted: in-container (not ideal); using volume mounts (good), other.

Each is "moveable" but it would help if you could add your Compose file to your question so that we may see which approach is being used.

In full-disclosure Im not familiar with the approach that you referencesd but that does not mean that it's inaccurate; I'm just not familiar with it.

Update: docker-entrypoint-initdb.d

See: "Initializing a fresh instance" on MySQL

So, any files within that directory are run to initialize the database container when it's created from the image.

In your Compose file you mount your host's ./data directory into this file. Presumably that directory contains >=1 file that performs your intended initialization.

NB The section volumes: data: at the end of the Compose file appears redundant. You're actually using a host-mounted directory ./data not this volume.

When you run the Compose file on the Droplet, those files aren't present and you'll need to copy them.

The simplest way to do this is to use scp and this provides 2 alternatives:

Either retain the data directory:

IP=[DROPLET-IP]
scp -r ./data root@${IP}:/data

NB The remote destination is /data not ./data. You will need to revise the Compose file on the Droplet (!) too:

volumes: - /data:/docker-entrypoint-initdb.d

Or move the files directly to the Droplet's /docker-entrypoint-initdb.d:

scp -r ./data root@${IP}/docker-entrypointy-initdb.d

NB Now there's no need for the volume mapping. You may remove:

volumes: - ./data:/docker-entrypoint-initdb.d

Update: repro (works)

I used a tweaked docker-compose.yaml but it's essentially the same:

version: "3.4"

services:
  db:
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: mypass
      MYSQL_DATABASE: mydb
    ports:
      - "3306:3306"
    volumes:
      - ${PWD}/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    restart: always

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Then mkdir ${PWD}/docker-entrypoint-initdb.d and created a file in it called freddie.sql:

create database if not exists frederik;

use frederik;

create table treats (
 TreatID INT NOT NULL AUTO_INCREMENT,
 TreatName VARCHAR(255) NOT NULL,
 PRIMARY KEY (TreatId));

insert into treats (TreatName)
values
 ("Dried Salmon"),
 ("Meatballs");

Then docker-compose rm --force && docker-compose up

I was able to browse the adminer UI (:8080), login (root|mypass) and browse the database frederik: