Switch from docker mysql container to host's mysql without using network-mode="host"

Question

My company has a docker-container based website (Ubuntu containers), deployed via GitHub => CircleCI => AWS. (This was set up by a consultant, who we are not currently working with. I'm trying to make sense out of everything on my own.)
I've copied the source files to my Windows 10 development PC.
Locally, the website is running successfully, until it tries to access MySQL data.

I've installed MySQL (8) locally, with default settings (port 3306) and location (C:\ProgramData\MySQL\MySQL Server 8.0\Data), and imported the database. I've created root user. Running test queries as root from MySQL Workbench works.

I've read Q&A's such as How to connect locally hosted MySQL database with the docker container.

I've successfully bridged to host's IP (on EthernetV; Docker running in Hyper-V) 172.26.92.81 for other purposes, specifically to have XDebug in my website container talk to phpstorm on my Windows 10 host. (I could use host.docker.internal for IP; but I'm making everything as "concrete" as possible, until everything works.)

I just don't understand what I have to change where, for redirecting MySQLi queries (in php as create web pages) to the (development pc) host. (Assume I'm clueless about both MySQL and Docker and Apache, until proven otherwise.)

Relevant parts of (original) docker-compose.yml:

mywebsite:
    build:
    context: ./mywebsite/
    dockerfile: Dockerfile
    ...
    depends_on:
    - mysql
    links:
    - mysql
    environment:
    MYSQL_HOST: mysql
    MYSQL_USER: root
    MYSQL_PASS: xxx
    MYSQL_SCHEMA: xxx
    MYSQL_PORT: 3306
    MYSQL_CHARSET: utf8

mysql:
    image: mysql:5.7
    ports:
    - 3305:3306
    command: mysqld --sql_mode=""
    environment:
    MYSQL_DATABASE: xxx
    MYSQL_ROOT_PASSWORD: xxx
    volumes:
    - data:/var/lib/mysql

NOTE: Not going for "best practices" above; I need to see a simple approach working. Security comes later.

I'd prefer a solution that doesn't involve "network_mode: "host" -- solving it that way would avoid details that I need to understand. [The linked Q&A shows that host mode is the simplest solution, but that is "too simple" - obscures some important considerations.]

Host has MySQL on its port 3306. The mysql container shown above: is it using its own copy of MySQL? Or is that already attempting to connect to host's MySQL? And why does it have the port mapping 3305:3306? (I can't change that to 3306:3306; if do so, it is unable to assign the port number. I assume that is because host's MySQL already uses 3306, and that port line is exposing the mysql container's MySQL?)

The volume mapping data:... is where I should move the data to, if I want to use the mysql container as is? That's probably what I will do for now - which may make this SO question moot - but I'd still like to know how to do what I ask.

I'm assuming it is its own database, because it has its own version number (5.7).
From php inside mywebsite:

$connection = new \mysqli(
            $_ENV["MYSQL_HOST"],
            $_ENV["MYSQL_USER"],
            $_ENV["MYSQL_PASS"],
            'INFORMATION_SCHEMA',
            $_ENV["MYSQL_PORT"]
        );
$result = $connection->query("SELECT VERSION();");
# breakpoint: $result > one row > ['VERSION()'] = 5.7.25

which is the version number of that mysql image, not the host's mysql.

similarly, list of databases doesn't include some databases I see on the host mysql from Workbench.

What changes do I need to make in docker-compose.yml?

Do I also need to make changes in mywebsite's Dockerfile?

mywebsite is based on apache2 + php 7.3. Dockerfile:

FROM php:7.3.3-apache-stretch
...
RUN apt-get update && ... docker-php-ext-install ... mysqli \
    && docker-php-ext-enable mysqli ...
...
COPY /php.ini /usr/local/etc/php/
COPY /src/ /var/www/html/

To be clear: there are multiple similar questions about docker accessing mysql on host. All the ones I've found focus on how to bridge the IP address; I've included a link to one of those. It doesn't help me; I don't know what to do with the information given there - and I've already successfully bridged from the docker container to the host for xdebug - but I have no idea what to change where to make use of that IP for mysql. An ideal answer would *not* involve "putting the IP into the connect string"; IMHO the website container should be isolated from such details, so I can deploy it as is. — ToolmakerSteve, Mar 31 '19 at 13:47

score 2 · Accepted Answer · answered Apr 01 '19 at 07:31

First of all, the declaration:

links:
    - mysql

in the mywebiste service is not needed and actually stays in the way of you solving your issue. You should remove it.

After doing that, you can try 2 things (both of them worked for me):

change MYSQL_HOST to point to your computer's IP (not 'localhost', not '127.0.0.1' but your local network IP address) and start only the mywebsite service.
in version 3.0 or higher of docker-compose file you can add extra hosts:

mywebsite:
...
  extra_hosts:
    - "mysql:<your ip>"

I hope this helps

Switch from docker mysql container to host's mysql without using network-mode="host"

1 Answers1