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
localhostspecially, 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--socketoption or theMYSQL_UNIX_PORTenvironment 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
- Configure
DATABASE_URLexport 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!
Oldest comments (6)
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."
Hi, after doing this, I've got an error
Mysql2::Error: Access denied for user 'root'@'172.17.0.1' (using password: YESwhen trying to runrake db:create.Does anybody have this problem too?
That error message looks familiar... I think the password was wrong in my case.
Does the
MYSQL_ROOT_PASSWORDthat you set while booting up the Docker container match thepasswordin yourdatabase.yml?Also, there may be something in this thread to help you: github.com/docker-library/mysql/is...
Sorry, it was my fault, the password was wrong!
Glad it worked!
I am running my rails project on another container, how can I connect that mysql to my project