Connect to Mysql database from another docker application-CodePudding

I have 2 Laravel applications running in docker environment.

The first application docker-compose file (which called mobile app)

version: '3.8'

networks:
    laravel:

services:
    nginx:
        build:
            context: .
            dockerfile: dockers/nginx/Dockerfile
        container_name: mobile-server-nginx
        ports:
            - "8188:80"
        volumes:
            - ./:/var/www/
        depends_on:
            - php
            - mysql
        networks:
            - laravel
    mysql:
        image: mysql:8.0.28
        container_name: mobile-server-mysql
        tty: true
        ports:
            - "5306:3306"
        volumes:
            - ./mysql:/var/lib/mysql
        environment:
            MYSQL_DATABASE: ${DB_DATABASE}
            MYSQL_USER: ${DB_USERNAME}
            MYSQL_PASSWORD: ${DB_PASSWORD}
            MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
            SERVICE_TAGS: dev
            SERVICE_NAME: mysql
        networks:
            - laravel
    php:
        build:
            context: .
            dockerfile: dockers/php/Dockerfile
        container_name: mobile-server-php
        extra_hosts: 
            - "host.docker.internal:host-gateway"
        volumes:
            - ./:/var/www/
        ports:
            - "9001:9000"
        networks:
            - laravel
        depends_on:
            - mysql

The second application docker-compose file: (which called resident app)

version: '3.7'

networks:
    resident:

services:
    nginx:
        build:
            context: .
            dockerfile: dockers/nginx/Dockerfile
        container_name: resident-backend-nginx
        ports:
            - "8088:80"
        volumes:
            - ./:/var/www/
        depends_on:
            - php
            - mysql
        networks:
            - resident
    mysql:
        image: mysql:5.7.22
        container_name: resident-backend-mysql
        tty: true
        ports:
            - "6306:3306"
        volumes:
            - ./mysql:/var/lib/mysql
        environment:
            MYSQL_DATABASE: ${DB_DATABASE}
            MYSQL_USER: ${DB_USERNAME}
            MYSQL_PASSWORD: ${DB_PASSWORD}
            MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
            SERVICE_TAGS: dev
            SERVICE_NAME: mysql
        networks:
            - resident
    php:
        build:
            context: .
            dockerfile: dockers/php/Dockerfile
        container_name: resident-backend-php
        extra_hosts: 
            - "host.docker.internal:host-gateway"
        volumes:
            - ./:/var/www/
        ports:
            - "9002:9000"
        networks:
            - resident
        depends_on:
            - mysql

2 applications work separated perfectly. Now, in mobile application, I want to connect to resident application database to retrieve some data. In the mobile application:

In config/database.php, I added 1 more database connection:

        'resident_mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_RESIDENT_URL'),
            'host' => env('DB_RESIDENT_HOST', '127.0.0.1'),
            'port' => env('DB_RESIDENT_PORT', '3306'),
            'database' => env('DB_RESIDENT_DATABASE', 'forge'),
            'username' => env('DB_RESIDENT_USERNAME', 'forge'),
            'password' => env('DB_RESIDENT_PASSWORD', ''),
            'unix_socket' => env('DB_RESIDENT_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then, I set env variables in .env file:

DB_RESIDENT_HOST=127.0.0.1
DB_RESIDENT_PORT=6306 // exposed mysql port in `resident_app`
DB_RESIDENT_DATABASE={database name}
DB_RESIDENT_USERNAME={database_user}
DB_RESIDENT_PASSWORD={database_password}

Create an api to test in api.php:

Route::get('test', function () {
    $user = \DB::connection('resident_mysql')->select('select * from users');
    return $user;
});

Finally, test api with postman, but it shows error SQLSTATE[HY000] [2002] Connection refused (SQL: select * from users)

I've searched for this error but cannot find any answer.

UPDATE

According from @David Maze, I've changed some config in docker-compose file in mobile app.
In docker-compose file, add external network

networks:
    laravel:
        driver: bridge
    residentbackend_resident: // this is network in `resident` app, which type is `bridge`
        external: true

Then add external network to php service in networks section:

        networks:
            - laravel
            - residentbackend_resident

In .env, change DATABASE_HOST to mysql service name of resident app

DB_RESIDENT_HOST=mysql1

But it still return the same error as above.

CodePudding user response：

DB host cannot be 127.0.0.1, Since it is a separate docker container you have to add the mysql docker private IP or the name

DB_RESIDENT_HOST=mysql

example for your reference

CodePudding user response：

There are a couple of things to consider here.

First, each container is a separate node in a bigger network.
So setting DB_RESIDENT_HOST=127.0.0.1 will not work, as it does not reference your local machine, but the (PHP) container itself.

Second, each docker-compose.yml creates a separate network, where all the containers defined are attached too. They can only communicate with each other and the host.
You need to attach an external network to the containers, that should be able to communicate across the stacks. But that, it seems like, you have already done by now.

Third, you used the port 6306, which is the exposed port on your local machine. Internally, the container still uses the port 3306. And as those containers communicate now directly (through the external docker network), this is the port to use.

And another thing I noticed is, that those containers have the same names across each stack (compose definitions, not container names). When accessing the mysql container, it probably routes to the container in the same file.
Maybe the hostname keyword solves this, but I haven't used that.
What definitely works, is either renaming them or setting the config to the internal IP address of the container itself.
Run docker inspect resident_mysql (replace the name with the correct one), search for "NetworkSettings", then "Networks", find the external network, that is attached to both containers, and use the value in "IPAddress".