Domhnall Murphy

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.