How to setup a MySQL database in a Docker Container for local development on MacOS

Docker is a tool for simplifying deployment of applications. However, it can also make your local development process easier and faster!

Typical local development involves manual database installations or connecting to test databases in the cloud. Instead, you can use a Docker Container to run multiple versions of the same database that are easily accessible and support quick restore from backups.

Prerequisites

Install the latest version of Docker.

To verify the installation, open terminal and run docker --version. You should see an output with the version and build number as below.

The Setup

To start a simple MySQL instance, run the following command in terminal:

This will output a container id, which means that the container is running in the background properly.

Here,

  • some-mysql is the name of the container
  • my-secret-pw is the password for the MySQL root user
  • mysql is the name of the official docker image for MySQL
  • 5.7 is the tag specifying the MySQL version you want. The list of available versions can be found here
  • -d stands for detach and means that a docker container runs in the background

To verify if MySQL is running, run the command docker ps. This will output the container details as below.

MySQL Command Line

If you don’t have MySQL Workbench/Shell already installed, you can install it from here.

To use the MySQL command line client within the container, against the above created MySQL instance, run the below command.

Here,

  • some-mysql is the name of the docker container that we created earlier
  • root is the default root username
  • my-secret-pw is the password that we set earlier

Docker Compose

If you don’t enjoy running long commands in the terminal, you can use a docker-compose.yml file for setting up the MySQL container as below.

To start the MySQL instance in the background, run  docker-compose up -d from the same directory as the file.

Access From Outside

Currently, the MySQL instance is only accessible from within the docker container. To access it from outside, say from MySQL Workbench or from your application, you need to specify the ports during the docker container creation as below.

Here, 3306:3306 maps the port 3306 of your localhost to the port 3306 of the container.

Now, the MySQL instance can be accessed at localhost:3306 with the username root and password my-secret-pw.

If you already have a MySQL local installation running on your machine, the above command will fail with the error docker: Error response from daemon: Ports are not available: listen tcp 0.0.0.0:3306: bind: address already in use.

In such cases, change the port to say 3307 as below.

The MySQL instance is now accessible at localhost:3307.

Load Data

When a container is started for the first time, it will execute files with extensions  .sh.sql and  .sql.gz that are found in  /docker-entrypoint-initdb.d. To load data, you can mount a SQL dump into this directory as below.

Here, ./sql-scripts is the folder path on the local machine that contains the scripts.

The  docker-entrypoint-initdb.d mechanism only runs the first time a docker container starts up, with an empty database. If you make any changes to the init scripts, you’ll need to explicitly  docker-compose rm your containers to cause the current database to be deleted, and then docker-entrypoint-initdb.d mechanism will be re-triggered on the next container start.

Persist Data

To persist data even after the container is deleted, you can use volumes or bind mounts.

Volumes

Volumes are stored in a part of the host filesystem which is managed by Docker. Volumes are the best way to persist data in Docker. You can use volumes as below.

To remove the persisted storage, run docker-compose down -v. This deletes the volumes used to store the data.

Bind Mounts

Bind mounts may be stored anywhere on the host system. You can use bind mounts as below.

Here, ./database is the folder path on the local machine that will be used as persistent storage.

To remove the persisted storage, stop the container and delete the ./database folder.

Start, Stop, Restart & Delete the MySQL Container

With Docker Run

  • To start, run docker start some-mysql
  • To stop, run docker stop some-mysql
  • To restart, run docker restart some-mysql
  • To delete, stop and then run docker rm some-mysql

Here, some-mysql is the name that was specified during the docker container creation earlier.

With Docker Compose

  • To create & start, run docker-compose up
  • To start, run docker-compose start
  • To stop, run docker-compose stop
  • To restart, run docker-compose restart
  • To delete, stop and then run docker-compose rm
  • To stop & delete, run docker-compose down

References

Leave a Reply

Your email address will not be published. Required fields are marked *