For many years I have equated localhost
and
the loopback address 127.0.0.1
.
Recently I became aware of some differences, and in particular how MySQL
treats these two addresses.
If you run MySQL in a docker container there is a good chance you will bump your head on this also.
Introduction
I make use of docker for a lot of my development work. Running different versions of MySQL, Sphinx, Redis, Node, Rails etc. for different projects on the same development machine can get complicated. Docker is invaluable for keeping on top of such version conundrums, allowing the developer to compartmentalise using containers.
However, sometimes the added indirection introduced by containers can cause some friction. I recently encountered such friction whilst trying to run a standard development task against a MySQL server running inside of docker.
The Setup
For most projects I usually start with a Dockerfile, to dockerize the application being developed, along with a docker-compose.yml file to define the other services that the application depends on. A standard web project might have a docker-compose file that looks something like this
version: '3'
services:
db:
image: mysql
command: --default-authentication-plugin=mysql_native_password
volumes:
- /usr/local/vectorlogic/mysql:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: "bestpasswordeva"
ports:
- "3307:3306"
app:
build:
context: .
dockerfile: ./docker/app/Dockerfile
command: bash -c "rm -f /production/tmp/pids/server.pid && bin/rails s -p 3000 -b '0.0.0.0'"
tty: true
stdin_open: true
volumes:
- .:/production
ports:
- "3000:3000"
environment:
PORT: "3000"
NODE_ENV: "development"
RAILS_ENV: "development"
links:
- db
depends_on:
- db
The Problem
So we spin up the environment with
docker-compose up
and try to
connect to mysql
running in the container:
mysql -u root -pbestpasswordeva -P 3307 -h localhost
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
This doesn't work, but guess what? If I use 127.0.0.1
it works:
mysql -u root -pbestpasswordeva -P 3307 -h 127.0.0.1
So what is going on?
The Lesson
It seems that when we reference localhost
then MySQL is trying to use a unix
socket for the communication. This will obviously lead to problems for our setup, as the
socket connection is file-based and we have made no attempt to share this file between
the container running the server and the host file system.
By contrast, when we provide the host as 127.0.0.1
MySQL will use TCP to connect
to the server, which works regardless of the servers containerization.
We actually can specify the host to be localhost
, but in this case we
need to explicitly tell MySQL to use TCP, rather than defaulting to socket communication. So
this should also work:
mysql -u root -pbestpasswordeva -P 3307 -h localhost --protocol=tcp
You can also set protocol=tcp
as a client setting in your my.cnf
and
never have to worry about the command-line flag. It will be like it never even happened.
Conclusion
Docker is great. MySQL is great. There are a lot of things that I know nothing about.
Now I know that when we reference localhost
on Unix-based systems MySQL will
assume that we are trying to connect via a socket, but when we specifiy the host as
127.0.0.1
then we are using TCP/IP.
This will likely cause you problems if you use localhost
when trying to connect
from your host machine to a MySQL server running inside a docker container.