Run MySQL on Port 3307 Using Docker Compose

Question

I am trying to create multiple Prisma database services on a single machine. I have been unable to create a MySQL database on a port other than 3306 using Docker Compose.

docker-compose.yml

version: '3'
services:
hackernews:
    image: prismagraphql/prisma:1.8
    restart: always
    ports:
    - "${CLIENT_PORT}:${INTERNAL_PORT}"
    environment:
    PRISMA_CONFIG: |
        port: $INTERNAL_PORT
        managementApiSecret: $PRISMA_MANAGEMENT_API_SECRET
        databases:
        default:
            connector: mysql
            host: mysql
            port: $SQL_INTERNAL_PORT
            user: root
            password: $SQL_PASSWORD
            migrations: true
mysql:
    image: mysql:5.7
    restart: always
    environment:
    MYSQL_ROOT_PASSWORD: $SQL_PASSWORD
    volumes:
    - ./custom/:/etc/mysql/conf.d/my.cnf
    - mysql:/var/lib/mysql
volumes:
mysql:

docker-compose.override.yml

version: '3'
services:
mysql:
    expose:
    - "${SQL_INTERNAL_PORT}"
    ports:
    - "${SQL_CLIENT_PORT}:${SQL_INTERNAL_PORT}"

Error:

hackernews_1  | Exception in thread "main" java.sql.SQLTransientConnectionException: database - Connection is not available, request timed out after 5008ms.
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
hackernews_1  |     at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83)
hackernews_1  |     at slick.jdbc.hikaricp.HikariCPJdbcDataSource.createConnection(HikariCPJdbcDataSource.scala:18)
hackernews_1  |     at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:439)
hackernews_1  |     at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:47)
hackernews_1  |     at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:38)
hackernews_1  |     at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:218)
hackernews_1  |     at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:217)
hackernews_1  |     at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:38)
hackernews_1  |     at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:239)
hackernews_1  |     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
hackernews_1  |     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
hackernews_1  |     at java.lang.Thread.run(Thread.java:748)
hackernews_1  | Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=mysql)(port=3307)(type=master) : Connection refused (Connection refused)
hackernews_1  |     at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:161)
hackernews_1  |     at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.connException(ExceptionMapper.java:79)
hackernews_1  |     at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1040)
hackernews_1  |     at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:490)
hackernews_1  |     at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:144)
hackernews_1  |     at org.mariadb.jdbc.Driver.connect(Driver.java:90)
hackernews_1  |     at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
hackernews_1  |     at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:341)
hackernews_1  |     at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:193)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:430)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:64)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:570)
hackernews_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:563)
hackernews_1  |     at java.util.concurrent.FutureTask.run(FutureTask.java:266)

docker ps

CONTAINER ID        IMAGE                      COMMAND                  CREATED             STATUS                                  PORTS                              NAMES
ab721996469d        mysql:5.7                  "docker-entrypoint.s…"   42 minutes ago      Up 55 seconds                           3306/tcp, 0.0.0.0:3307->3307/tcp   two_mysql_1
7aab98e2b8d7        prismagraphql/prisma:1.8   "/bin/sh -c /app/sta…"   2 hours ago         Restarting (1) Less than a second ago                                      two_hackernews_1

.env

SQL_PASSWORD=myuniquepassword
SQL_INTERNAL_PORT=3307
SQL_CLIENT_PORT=3307

Solution 1

Variable SQL_INTERNAL_PORT probably has 3307 value. You need to change it to 3306.

Also, you can remove

    expose:
    - "${SQL_INTERNAL_PORT}"

lines. Mysql already exposes 3306 port.

All of the applications into cluster use internal ports (3306 in mysql case). External ports (in section ports) you need only for external word communication.

If you want to have several databases, you need to change docker-compose something like this:

version: '3'
services:
hackernews:
    image: prismagraphql/prisma:1.8
    restart: always
    ports:
    - "${CLIENT_PORT}:${INTERNAL_PORT}"
    environment:
    PRISMA_CONFIG: |
        port: $INTERNAL_PORT
        managementApiSecret: $PRISMA_MANAGEMENT_API_SECRET
        databases:
        default:
            connector: mysql
            host: mysql_first
            port: 3306
            user: root
            password: $SQL_PASSWORD
            migrations: true
        second:
            connector: mysql
            host: mysql_second
            port: 3306
            user: root
            password: $SQL_PASSWORD
            migrations: true
mysql_first:
    image: mysql:5.7
    restart: always
    environment:
    MYSQL_ROOT_PASSWORD: $SQL_PASSWORD
    ports:
     - 3307:3306
    volumes:
    - ./custom/:/etc/mysql/conf.d/my.cnf
    - mysql:/var/lib/mysql

 mysql_second:
    image: mysql:5.7
    restart: always
    environment:
    ports:
     - 3308:3306
    MYSQL_ROOT_PASSWORD: $SQL_PASSWORD

Solution 2

In my case I could not change the Mysql port rather than 3306 until I add MYSQL_TCP_PORT

So sample:

version: '3.8'

volumes:
  mysql_data:
    driver: local

services:
  mysql:
    image: mysql:5.7
    volumes:
      - mysql_data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: keycloak
      MYSQL_USER: keycloak
      MYSQL_PASSWORD: password
      MYSQL_TCP_PORT: 3307
    ports:
    - 3307:3307
    expose:
      - 3307
  keycloak:
    image: quay.io/keycloak/keycloak:latest
    environment:
      DB_VENDOR: MYSQL
      DB_ADDR: mysql
      DB_DATABASE: keycloak
      DB_USER: keycloak
      DB_PASSWORD: password
      DB_PORT: 3307
      KEYCLOAK_USER: admin
      KEYCLOAK_PASSWORD: Pa55w0rd
      # Uncomment the line below if you want to specify JDBC parameters. The parameter below is just an example, and it shouldn't be used in production without knowledge. It is highly recommended that you read the MySQL JDBC driver documentation in order to use it.
      #JDBC_PARAMS: "connectTimeout=30000"
    ports:
      - 8040:8080
    depends_on:
      - mysql

Comments

[Pasha]

Thank you Bukharov! You are absolutely correct, the machine had ran out of memory and as a result I was assuming that I was doing something wrong (hence why I wanted to run the MySQL database on a different port than 3306).

[Igor Melão]

Thanks Muhammed Ozdogan! I also only could change the port when I add MYSQL_TCP_PORT