MySQL connection from docker container php:8.1.17-apache to docker container mysql:8.0.32 doesn't work

Question

I am trying to connect from this docker container:

version: '3.8'

services:
  vidslide-api:
    image: php:8.1.17-apache
    ports:
      - "80:80"
    volumes:
      - ./htdocs:/var/www/html
    command: >
      sh -c "
      docker-php-ext-install mysqli && \
      docker-php-ext-enable mysqli && \
      apache2-foreground
      "

to this docker container:

version: '3.8'
services:
  vidslide-db:
    image: mysql:8.0.32
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ***
    ports:
      - "3306:3306"
    volumes:
      - /var/lib/vidslide_mysql-db:/var/lib/mysql
volumes:
  mysql_data: 

I can connect to the mysql server with prisma and mysql workbench but not with php.

When I try to connect with mysqli:

$conn = mysqli_connect($host, $user, $pass, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

I get: Fatal error: Uncaught mysqli_sql_exception: No such file or directory in /var/www/html/index.php:14 Stack trace: #0 /var/www/html/index.php(14): mysqli_connect('localhost', 'root', '***', 'vidslide-db') #1 {main} thrown in /var/www/html/index.php on line 14

my mysqli config from phpinfo();

I don't understand the error message.
What do I have to do to make it work? Are the versions not compatible?

Answer 1

There is a lot to unpack here.

1. Consider each docker-compose.yml a different "project". For each project docker-compose will create its own virtual network. Without additional config in each project those virtual networks will not be able to talk to each other

2. The declarations in the ports: section will map the port out to the host machine's localhost:port but that is it.

   • Note: actually ALL interfaces will now be listening on that port, not just local.

3. "localhost" is only local to the individual container. Each container has its own, and none of them are the host machine's "localhost" either.

4. Most mysql drivers [including PHP's] map the name localhost to the local unix socket which is only available if you're running the code on the same host [or container, in this case] as Mysql.

#4 is why you're getting the quizzical "file not found" error on connection, because it can't find the socket file, and everything else is why it wouldn't have worked anyway.

The simple solution is to combine the two services into a single docker-compose.yml and then use the mysql service's hostname instead of localhost.

version: '3.8'

services:
  vidslide-api:
    image: php:8.1.17-apache
    ports:
      - "80:80"
    volumes:
      - ./htdocs:/var/www/html
    command: >
      sh -c "
      docker-php-ext-install mysqli && \
      docker-php-ext-enable mysqli && \
      apache2-foreground
      "
  vidslide-db:
    image: mysql:8.0.32
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ***
    ports:
      - "3306:3306"
    volumes:
      - /var/lib/vidslide_mysql-db:/var/lib/mysql
volumes:
  mysql_data: 

Docker-compose creates local DNS names for each service, so your mysql hostname will be vidslide-db instead of localhost.

Additionally, you don't want to be installing anything every time your container starts, unless you really like waiting for extensions to compile every time the container starts. For this you'll want to build your own image that includes this, and Docker-compose makes it easy to build on demand.

Add this as Dockerfile-php in the same dir as your docker-compose.yml:

FROM php:8.1.17-apache

RUN docker-php-ext-install mysqli && \
    docker-php-ext-enable mysqli

And change the vidslide-api service to simply:

  vidslide-api:
    build:
      dockerfile: Dockerfile-php
      context: ./
    ports:
      - "80:80"
    volumes:
      - ./htdocs:/var/www/htm

Answer 2

In this case, localhost is actually the container rather than your host. Therefore, the connection fails (because the PHP container doesn't host a MySQL Database).

To use your host in Docker Desktop, use host.docker.internal as the IP.
If you want this solution to work on *nix as well, refer to this answer.

For production instances, you should definitely use docker networks to connect the container together and use links or hostname to access the database.