How to use MySQL on Docker for Rails local development (switching MySQL versions made easy!)

#docker #mysql #rails

Header image by Noah Boyer on Unsplash

Recently, I joined a Rails project that requires MySQL 5.6. But I'd already been using MySQL 8 (the latest version as of this writing) for a different project, installed locally via Homebrew.

Installing both versions using Homebrew and doing brew switch mysql@<version> turned out to be a nightmare, so I decided to use MySQL on Docker.
I am no Docker expert, and it took some time to figure out. But it's actually very straightforward and easy to use, so I highly recommend it if you need multiple versions of MySQL.

This guide walks you through setting up Docker containers for different versions of MySQL, and the necessary Rails configuration (only the last part is Rails-specific). Also, no worries if you're not familiar with Docker commands :) I provided explanations for the command options to make this as beginner-friendly as I could.

Note: This guide is for MacOS.

Steps

For the purpose of this guide, let's say you're trying to use the latest version of MySQL and version 5.6. Here are the steps:

Install Docker Desktop
Start a Docker container (MySQL version: latest)
Stop the container to free up port 3306
Start another Docker container (MySQL version: 5.6)
Configure Rails to use MySQL on Docker

1. Install Docker Desktop

Install Docker Desktop if you haven't yet. After it's installed, you can find it under ~/Applications.

2. Start a Docker container (MySQL version: latest)

Let's boot up a container with the latest version of MySQL first.
For any Docker beginners out there, docker run creates a new container with the specified image (mysql:latest in this case) and runs it.



docker run \
-p 3306:3306 \ # Maps the container's port 3306 (default port for MySQL) to your machine's port 3306.
--name test-mysql-8 \ # Name your container whatever you like, as long as it's unique.
-e MYSQL_ROOT_PASSWORD=password \ # MySQL requires that you set this. Will error out otherwise.
-d \ # Stands for "detach"; keeps the container running in the background.
mysql:latest

Warning: docker run requires that you enter any options before the image name. For example, docker run mysql:latest -p 3306:3306 ... does not work.

Next, let's check that it's actually running with docker ps.



$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
f89e544f49d5        mysql:latest        "docker-entrypoint.s…"   5 minutes ago      Up 5 minutes       0.0.0.0:3306->3306/tcp, 33060/tcp   test-mysql-8

You can also check from the Docker Desktop dashboard:

3. Stop the container to free up port 3306

Using the command in step 2, now you can boot up any other MySQL containers you need. But you can't run two containers mapped to port 3306 at the same time. So first, let's stop the currently running container to free up the port (you can restart this container later).

There are two ways to do this.

From the command line: docker stop <container name> (use the name you specified under --name)
From the Docker Desktop dashboard: Click the stop button

Note: If you try to start another container mapped to port 3306 without stopping the current one, you will get this error: Bind for 0.0.0.0:3306 failed: port is already allocated.

4. Start another Docker container (MySQL version: 5.6)

You can use the same command as step 2 to boot up containers for other versions of MySQL. Just make sure to specify:

The tag (version) you need, like mysql:5.6 (list of available tags here)
A different name under --name

And voila! You have two versions of MySQL now (you can check via docker ps/dashboard).

Whenever you need to switch versions, you can just stop the running container as shown in step 3, and start the necessary container by docker start <container name> or the start button in the dashboard.

5. Configure Rails to use MySQL on Docker

When you try to create the MySQL databases for your Rails project by running commands like rails db:setup, you may still get an error like this: Mysql2::Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

It turns out, MySQL has a unique definition of localhost (docs).

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs: the client connects using a Unix socket file. The --socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

Long story short, 127.0.0.1 and localhost are not the same thing for MySQL. So you need to specify in the database settings that you're using 127.0.0.1 (not localhost). There are two ways to do this:

Configure config/database.yml ```yaml

default: &default
adapter: mysql2
# Other settings...
username: root
password: password # The MYSQL_ROOT_PASSWORD you specified
host: 127.0.0.1 # <- Specify the host

- Configure `DATABASE_URL`
  - `export DATABASE_URL=mysql2://root:@127.0.0.1`

Either of these should do the trick. The second option is useful if you're joining a team project and can't edit `config/database.yml` as you please.

Thank you for reading and please let me know if I missed anything!

---

### References
- [Docker run reference](https://docs.docker.com/engine/reference/run/)
- [MySQL Docker images](https://hub.docker.com/_/mysql)

Top comments (6)

Fabio Perrella • May 29 '20

Hi, after doing this, I've got an error Mysql2::Error: Access denied for user 'root'@'172.17.0.1' (using password: YES when trying to run rake db:create.

Does anybody have this problem too?

Risa Fujii • May 30 '20 • Edited

That error message looks familiar... I think the password was wrong in my case.
Does the MYSQL_ROOT_PASSWORD that you set while booting up the Docker container match the password in your database.yml?

Also, there may be something in this thread to help you: github.com/docker-library/mysql/is...