DEV Community

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

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

risafj profile image Risa Fujii 惻Updated on 惻4 min read

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:

  1. Install Docker Desktop
  2. Start a Docker container (MySQL version: latest)
  3. Stop the container to free up port 3306
  4. Start another Docker container (MySQL version: 5.6)
  5. 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

You can also check from the Docker Desktop dashboard:
Alt Text

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

Alt Text

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
default: &default
  adapter: mysql2
  # Other settings...
  username: root
  password: password # The MYSQL_ROOT_PASSWORD you specified
  host: 127.0.0.1 # <- Specify the host
Enter fullscreen mode Exit fullscreen mode
  • 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

Discussion (5)

pic
Editor guide
Collapse
fabioperrella profile image
Fabio Perrella

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?

Collapse
risafj profile image
Risa Fujii Author • 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...

Collapse
fabioperrella profile image
Fabio Perrella

Sorry, it was my fault, the password was wrong!

Thread Thread
risafj profile image
Risa Fujii Author

Glad it worked!

Collapse
rob117 profile image
Rob Sherling • Edited

This was so helpful. No more "cannot connect to socket" errors with local installs and worrying about database sanity.

This should be the first google result for "easy mysql setup."