Docker-Compose Using PHP (MySQLi) to Connect to MySQL database

Question

Ok, so here's the deal: I'm using Docker-Compose to create 3 services: one for the platform, one for db (mysql) and the third for PHPMyAdmin

I'm trying to use mysqli to connect to the database on port 5001.

What’s interesting is that I can connect to the database using SQL Workbench and PHPMyAdmin with the same parameters, but I get an error (seen below) when connecting using PHP MySQLi

Platform:

  $db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

The host is 127.0.0.1, the DB_USER is root with its respective password, I've provided a DB_Name and the port is 5001.

My Docker-Compose.yml looks like this:

version: '3.3'

services:
  platform:
    build: .
    ports:
      - "5000:80"
    links:
      - db:mysql

  db:
     image: mysql:5.7
     volumes:
       - db_data:/var/lib/mysql
     restart: always
     ports:
       #- "5001:80"
       - "5001:3306"
     environment:
       MYSQL_ROOT_PASSWORD: some_great_password_here
       MYSQL_DATABASE: DB_NAME_HERE
       MYSQL_USER: USERNAME
       MYSQL_PASSWORD: PASSWORD

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db:mysql
    ports:
      - 8181:80
    environment:
      MYSQL_USERNAME: USERNAME
      MYSQL_ROOT_PASSWORD: PASSWORD

volumes:
    db_data:

For some reason, I keep getting the error:

mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '127.0.0.1' (111) in ...

Any tips or a solution would be much appreciated!

Deon Khanna · Accepted Answer · 2018-07-30 16:08:53Z

I found a solution to my problem after churning through documentation and other Stackoverflow posts.

When attempting to connect to the database from inside a container, we need to use the container / service name as opposed to 'localhost' or 127.0.0.1. This is because when we call "localhost," it's looking for localhost inside each container. We need to connect to a MySQL database inside a different container, via a network bridge.

The way we do this is to simply alias the container name as the host, such that:

$db = new mysqli("db", DB_USER, DB_PASS, DB_NAME, DB_PORT);

Notice that the Hostname is the container name "db," as defined in the docker-compose.yml file. This works because of the docker network bridge that is created between containers. The port is the default MySQL port of 3306.

It's important to note however, if we want to use an "external" app like Sequel Pro or Workbench to connect to the database, we can use localhost or 127.0.0.1 to connect to the database since we are not dealing directly with containers. It should be noted however, that the default port of 3306 won't work in this situation. We need to map an external port with 3306. In my case, that would be 5001, as shown in my docker-compose.yml file.

piy26 · Accepted Answer · 2018-07-28 01:44:07Z

0

You have mapped mysql port to 3306 as per

- "5001:3306"

You should try to connect at port 3306 instead of 5001. Make sure that your containers are using Bridge Networking for them to communicate with each other.

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, 3306);

edited Jul 28, 2018 at 1:44

answered Jul 28, 2018 at 1:39

piy26

1,61213 silver badges22 bronze badges

4 Comments

Deon Khanna Over a year ago

I tried connecting to 3306 directly to no avail. How do I make sure bridge networking is enabled? I thought it was by default. What’s weird is that if I can connect to the database using SQL workbench on port 5001

piy26 Over a year ago

Can you try once connecting using hostname as localhost instead of 127.0.0.1.

Deon Khanna Over a year ago

Yeah I tried that too. I prefer using 127.0.0.1 over localhost

piy26 Over a year ago

Try connecting with DB_HOST=mysql. Also verify /etc/hosts entry in the phpadmin container as you have exposed mysql service using - db:mysql.

Collectives™ on Stack Overflow

Docker-Compose Using PHP (MySQLi) to Connect to MySQL database

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related