localhostand 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.
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.
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
So we spin up the environment with
docker-compose up and try to
mysql running in the container:
This doesn't work, but guess what? If I use
mysql -u root -pbestpasswordeva -P 3307 -h localhost ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
So what is going on?
mysql -u root -pbestpasswordeva -P 3307 -h 127.0.0.1
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:
You can also set
mysql -u root -pbestpasswordeva -P 3307 -h localhost --protocol=tcp
protocol=tcpas a client setting in your
my.cnfand never have to worry about the command-line flag.
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.