Containerized Databases for Developers

What would you do if your laptop was stolen?

Have I got your attention? Yes? Good :)

Now think about all the unsanitised databases that were on that laptop.

Think about ALL THOSE COMMANDS you have to run to pull down that database from your QA environment, only to realise now that you have a copy of your users credentials on your laptop.

Database workflow is generally perceived as "complex and secure" or "simple and insecure".

Why can't we have both?

MySQL Toolkit is a project aimed at giving developers a simple AND secure workflow for running sanitised databases locally.

In this blog post I will be demonstrating how MySQL Toolkit works.

Use Case

I would like to introduce you the personas who will be helping me for this demo:

• Sam the Sys Op - Paranoid that developers have sensitive information on their development environments eg. credentials, users accounts etc
• Developer Dave - Just wants a database! Give me that database!

Sam will be building and pushing a sanitised database to Docker Hub (Registry), allowing Dave to pull and run the database in his local Docker Compose environment.

Sam the Sys Op

Installation

Sam gets started by installing the MySQL Toolkit project from the releases page:

https://github.com/previousnext/mysql-toolkit/releases

Configuration

Sam now needs to create some configuration which will be used to build an image.

config.yml

Sam has created a simple config file which sanitises a users email and password, while also ignoring irrelevant data (cache) to shrink the size of the image.

sanitize:
  tables:
    - name: users_field_data
      fields:
        - name: mail
          value: "SANITIZED_MAIL"
        - name: pass
          value: "SANITIZED_PASSWORD"nodata:
  - cache_page

ignore:
  - __ACQUIA_MONITORING__

Our full example of what we run nightly can be found here

Dockerfile

The Dockerfile provides instructions for how to build a working MySQL server and database image.

Sam has chosen to use this example Dockerfile from the MySQL Toolkit project.

FROM previousnext/mysql:alpine

# Add the database from the compiled source.ADD db.sql /tmp/db.sql
# Import with upstream scripts.RUN database-import drupal drupal local /tmp/db.sql

Package

Time for the fun part!

Sam dumps a sanitised database using the following command:

mtk db dump --hostname=localhost \
            --username=root \
            --password=password \
            --database=myapp \
            --config=config.yml \
            --file=db.sql

With Docker installed on her local environment, Sam chooses to use the "build with Docker" command:

mtk build docker --file=db.sql \
                 --dockerfile=dockerfile.yml \
                 --docker-username=myusername \
                 --docker-password=mypassword \
                 --docker-image=myorg/mysite-db:dev-latest

The database has been containerized and deployed to the Docker Hub, ready to share with Dave!

But first.... some access controls.

Permissions

Sam ensures that only Dave can access the containerized database image using Docker Hubs teams structures.

https://docs.docker.com/docker-hub/orgs

Success! Dave can now use this image for his local development.

Developer Dave

Dave is thrilled! Gone are the days where Dave has to jump through all these hoops to get access to a copy of the database eg. via SSH or pinging Sam directly.

By updating 2 files, Dave now has levelled up his local development environment!

docker-compose.yml

version: "3"

services:
  db:
    image: myorg/mysite-db:dev-latest

settings.php

$databases['default']['default'] = [
  'driver' => 'mysql',
  'database' => 'local',
  'username' => 'drupal',
  'password' => 'drupal',
  'host' => 'db',
];

Secure?

Providing databases to developers can be scary, take the Red Cross data leak for example:

https://www.troyhunt.com/the-red-cross-blood-service-australias-largest-ever-leak-of-personal-data

This post by Troy Hunt covers the Red Cross data leak from 2016 where 1.2 million donor records where exposed by a public facing database backup.

My takeaway from this breach was that convenience trumped security.

If the database was easy to access, chances are the file wouldn't be in the webroot.

If the database was sanitised, donor data would not have been exposed.

MySQL Toolkit gives you commands to create safe images.

Containers provides convenience, lowering the chances of databases ending up where they shouldn't be.

Building images remotely

Don't want to manage the compute for building images. We've got you covered!

AWS, Microsoft Azure and Google Cloud Platform all have options for building your container images remotely.

Right now we have support for AWS CodeBuild.

However, databases being passed to AWS CodeBuild should already be sanitised on your infrastructure.

I'm running on a Drupal PaaS

Most Drupal PaaS providers give developers access to an API for downloading database backups.

Currently we have support for Acquia.

Conclusion

MySQL Toolkit has been a huge level up to our development workflow.

The issue queue for MySQL Toolkit is open and ready to recieve your feature requests... and bugs.

Expect a part 2 in the coming weeks which will cover how we integrated MySQL Toolkit with Kubernetes.