I want to highlight one of my favorite tools as a MySQL DBA for local testing:

I’ve put together a video below. If you don’t want to watch that, keep reading.

Ok, consider the scenario that your MySQL schema needs to change. How do you test that you have the right command?

In production?!

Probably not for the initial change. For one thing, you need to make sure the change works for your application before it hits production.

Perhaps you have a shared dev or staging environment.
That could work, but if you break that you might impact your teammates work!

That’s why I recommend using a local setup for testing simple changes like modifying schema or writing the correct query to avoid production issues.

You’ll gain the confidence without impacting your team or your customers.

The old days: Virtualization

For decades, the best way to set up a dev environment was to have a dev virtual machine using virtualization .

This was to ensure you have everything you need rather than just running directly on your laptop or desktop. It also allowed a first pass of mimicking production operating systems.

However, virtual machines come with slow startup times, which put a lot of friction between you and testing your change.

The reason for slow startup times is because virtual machines are very resource intensive on memory, CPU, and disk; with each machine having its own operating system and boot process.

Due to this overhead, you ended up extremely limited to the architectures you could deploy: typically just one, maybe two, virtual machines!

Introducing Docker

Then along comes Docker: a simple packaging of an old Linux concept called cgroups into containers.

This is basically a way to contain and separate processes, with the benefit of assigning resources to these containers.

With Docker, you don’t have to load a new OS for each container. This means resource usage and boot times are drastically reduced.

With the reduced overhead, you can easily spin up multiple containers to meet a variety of use-cases that you couldn’t do with virtual machines.

And as a big benefit, Docker will run on Linux, Mac and Windows, and installation is pretty easy.

Let’s compare the startup time of a single virtual machine from a suspended state using Vagrant and Virtualbox:

DistributedDBA $ time vagrant up
Bringing machine 'ddba_dev' up with 'virtualbox' provider...
==> ddba_dev: Checking if box 'ubuntu/bionic64' version '20200916.0.0' is up to date...
==> ddba_dev: Resuming suspended VM...
==> ddba_dev: Booting VM...
==> ddba_dev: Waiting for machine to boot. This may take a few minutes...
*snip*
real	0m21.277s
user	0m4.654s
sys	0m1.639s

With the startup time of two MySQL Docker containers that can be used in a replication topology:

DistributedDBA $ time docker-compose up -d
Starting primary   ... done
Starting secondary ... done

real	0m1.580s
user	0m0.499s
sys	0m0.094s

1.5 seconds for two Docker containers vs 21 seconds for a single virtual machine. Imagine adding a second or third virtual machine to this start time.

Note: The Docker environment will take longer the first time you start it due to having to pull the appropriate images to your machine.

We defined the docker environment using the docker-compose wrapper. This allows us to easily define our architecture in terms of docker containers with proper networking and configuration.

version: "2.0"
services:
  mysql_primary:
    container_name: primary
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=secret
    volumes:
      - ./primary.conf.d:/etc/mysql/conf.d
    ports:
      - "13306:3306"
    networks:
      - backend

  mysql_secondary:
    container_name: secondary
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=secret
    volumes:
      - ./secondary.conf.d:/etc/mysql/conf.d
    ports:
      - "13307:3306"
    networks:
      - backend

networks:
  backend:

Configuring replication can be done automatically using other tools like Ansible, but is beyond the scope of this post.

Conclusion

If you get in the habit of validating assumptions locally, you’ll save yourself heartache in the future.

Docker will be a great tool in your MySQL DBA toolbox!

It allows you to quickly replicating semi-complex architectures so you can easily test SQL syntax for schema changes or even testing failovers of your High Availability solution.

This will give you increased confidence when running changes in production.

Of course, there’s a lot of things that a local environment can’t reproduce, such as production level workloads and most performance testing.