Container snapshots for fast database reloads

[mortona42]

Long ago, back when vagrant was our favorite local dev environment, I used this sahara plugin to take a snapshot of the box that is quicker to reload: https://github.com/jedi4ever/sahara.

This is fantastic for testing migrations or other operations that modify the database, which you need to revert to test again.

I'm a docker n00b so I'm not sure what the corresponding option is here. If there's a fast way to save and load a copy of the container, we could set up commands to switch it out.

[GoonerW]

You can always run lando db-export and lando db-import to backup and restore the database.

[mortona42]

Yep, that's what I'm doing now but it can take a while for a large database. I'm looking for something faster.

[GoonerW]

Fair enough

[pirog]

fwiw @mortona42 we are planning on a "lando save" command in Lando 4 that, under the hood, will take container snapshots

[xurizaemon]

Love to see this - we've been using a similar approach for fast-start developer and CI environments, building images separately (via Gitlab CI / kaniko) and then overriding the image URL in Lando configuration. Has worked well for the last couple years, and really nice for team to have access to consistent, fresh source data (for migration projects) where time cost is kept down to "pull and start the container".

If I recall right, an important detail for us was that when pre-building images for use in Lando, we found we had to base off the Bitnami image.

Comment mostly to say (1) yay, this feature will be welcome, (2) it's really useful and I think saved our team a lot of "wait on DB load" / "which version are you looking at?" work, and (3) it's possible to do much of this currently, using existing Lando and Docker tooling.

[dawidnawrot]

@xurizaemon can you share the idea on how you acheived that? It takes a lot of time to import db and it's always the pain. Any clues could be helpful.

[xurizaemon]

Hi @dawidnawrot, some notes / clues - hope it helps:

• Had done same with a PostgreSQL DB in the past so I had faith it was possible ... must have found docs
• Started from this MySQL example as an example.
  • It's significant how the multi-stage build works in that example. First it runs a MySQL server, loads a DB, which generates some /var/lib/mysql files on disk; the second stage copies the first stage's MySQL disk files into a fresh image.
  • Copying a DB dump into /docker-entrypoint-startdb.d/ in Dockerfile generates a runnable image, which takes longer to initialize (ungzip, load, index the DB) each time the container starts. Multistage build moves initialization time into your build process, speeding up start time for devs, CI, etc.
  • Initialization is supported by most base images, eg mysql/server, bitnami/mysql. TODO: Find docs for Postgres here.
  • Recommend to use the image's configuration via environment variables (MYSQL_USER etc) to configure what you can (optimal clarity IMO), then COPY foo.conf /path/to/mysql/conf/custom.conf to apply further server configuration, then place .sql and .sh files in the image initialization dir for any steps that remain (eg the DB data, which lands last).
  • Env vars have the benefits of visibility and that you can override them at runtime, eg from CI configuration.
• For Lando usage, we swapped that example's mariadb image for the Bitnami base image that Lando prefers. Something to do with permissions expectations I think; it did the trick, so I moved on.
• Further Docker build customisations:
  • Set ALLOW_EMPTY_PASSWORD in Dockerfile to meet some Lando DB requirement
  • COPY config/mysql/local.cnf /opt/bitnami/mysql/conf/zz_custom.cnf in Dockerfile to apply some server tuning in build phase (yes, really, the source DBs were big and defaults didn't cut it)
• At this point I was running it locally via a ./build.sh wrapper, and made sure I could run same from CI for scheduling
• Script retrieval of a pre-sanitized DB dump as .sql.gz from your backups
  • skpr/mtk may be of interest, as may drush sql-sanitize + some custom hooks, but sanitizing is a whole other topic. Sanitizing user data is rarely perfect, and it's still well worth doing.
• COPY tmp/last_night_backup.sql.gz /docker-entrypoint-initdb.d/ in Dockerfile
• Tag built image eg some-prebuilt-db, reference same tag in .lando.yml as image for database service, Lando can now start with the prebuilt image swapped.
• Push image from your wrapper to the container registry so tag is not local only (requires docker login working)

Automating this process can also provide a visible, regular check that DB backups can be restored.

Much learning was done as part of the process! I found out heaps about Lando's DB expectations, running MySQL in Docker in various environments (k8s, CI and local docker), build process. The end result was well worth the effort, fast start DBs are great to have.

[dawidnawrot]

Thanks a lot for the tips. Great you digged up your notes. This will be pretty helpful. Thanks 🙏