3

i'm trying to establish connection to mariadb database running on docker container. It is starting using docker compose:

  local_maria_db:
    image: mariadb:10.1
    ports:
      - "6603:5432"
    environment:
      MYSQL_ROOT_PASSWORD: "password"
      MYSQL_DATABASE: "testdb"
      ACCEPT_EULA: "Y"
    volumes:
      - ${MARIA_CNF_PATH}:/etc/mysql/conf.d/
    networks:
      - vpc1
    restart: always

Here is my .cnf file:

bind-address = 0.0.0.0

When i'm trying to access that DB outside of docker container using mysql:

mysql --user=root --password=password --port=6603 --host=localhost testdb

I'm getting

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

When im trying to establish connection using Sequelize running on different container but in the same network with this data:

 maria_db_connection: {
    database: "testdb",
    dialect: "mariadb",
    username: "root",
    password: "password",
    host: "local_maria_db",
    port: 5432
  }

Im getting ECONNREFUSED

When I execute mysql command inside docker container

mysql --user=root --password=password testdb

It connects with no problem

MariaDB [testdb]>

EDIT:

Docker container inspect:

[
    {
        "Id": "0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a",
        "Created": "2021-03-21T17:40:43.015346154Z",
        "Path": "docker-entrypoint.sh",
        "Args": [
            "mysqld"
        ],
        "State": {
            "Status": "running",
            "Running": true,
            "Paused": false,
            "Restarting": false,
            "OOMKilled": false,
            "Dead": false,
            "Pid": 26023,
            "ExitCode": 0,
            "Error": "",
            "StartedAt": "2021-03-21T17:49:19.57736344Z",
            "FinishedAt": "2021-03-21T17:49:11.732752669Z"
        },
        "Image": "sha256:4a2fed152fe12cbc1e1adbebcc89035df6f230b7c0c0c955c90827826e3bdf59",
        "ResolvConfPath": "/var/lib/docker/containers/0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a/resolv.conf",
        "HostnamePath": "/var/lib/docker/containers/0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a/hostname",
        "HostsPath": "/var/lib/docker/containers/0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a/hosts",
        "LogPath": "/var/lib/docker/containers/0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a/0a841717401c08b7153d88010d55ce204a9fda4f6cb679819a7d0da818d1e25a-json.log",
        "Name": "/pccr_local_maria_db_1",
        "RestartCount": 0,
        "Driver": "overlay2",
        "Platform": "linux",
        "MountLabel": "",
        "ProcessLabel": "",
        "AppArmorProfile": "",
        "ExecIDs": null,
        "HostConfig": {
            "Binds": [
                "/Users/krystiank/db/maria_cnf:/etc/mysql/conf.d:rw"
            ],
            "ContainerIDFile": "",
            "LogConfig": {
                "Type": "json-file",
                "Config": {}
            },
            "NetworkMode": "pccr_vpc1",
            "PortBindings": {
                "5432/tcp": [
                    {
                        "HostIp": "",
                        "HostPort": "6603"
                    }
                ]
            },
            "RestartPolicy": {
                "Name": "always",
                "MaximumRetryCount": 0
            },
            "AutoRemove": false,
            "VolumeDriver": "",
            "VolumesFrom": [],
            "CapAdd": null,
            "CapDrop": null,
            "CgroupnsMode": "host",
            "Dns": null,
            "DnsOptions": null,
            "DnsSearch": null,
            "ExtraHosts": null,
            "GroupAdd": null,
            "IpcMode": "private",
            "Cgroup": "",
            "Links": null,
            "OomScoreAdj": 0,
            "PidMode": "",
            "Privileged": false,
            "PublishAllPorts": false,
            "ReadonlyRootfs": false,
            "SecurityOpt": null,
            "UTSMode": "",
            "UsernsMode": "",
            "ShmSize": 67108864,
            "Runtime": "runc",
            "ConsoleSize": [
                0,
                0
            ],
            "Isolation": "",
            "CpuShares": 0,
            "Memory": 0,
            "NanoCpus": 0,
            "CgroupParent": "",
            "BlkioWeight": 0,
            "BlkioWeightDevice": null,
            "BlkioDeviceReadBps": null,
            "BlkioDeviceWriteBps": null,
            "BlkioDeviceReadIOps": null,
            "BlkioDeviceWriteIOps": null,
            "CpuPeriod": 0,
            "CpuQuota": 0,
            "CpuRealtimePeriod": 0,
            "CpuRealtimeRuntime": 0,
            "CpusetCpus": "",
            "CpusetMems": "",
            "Devices": null,
            "DeviceCgroupRules": null,
            "DeviceRequests": null,
            "KernelMemory": 0,
            "KernelMemoryTCP": 0,
            "MemoryReservation": 0,
            "MemorySwap": 0,
            "MemorySwappiness": null,
            "OomKillDisable": false,
            "PidsLimit": null,
            "Ulimits": null,
            "CpuCount": 0,
            "CpuPercent": 0,
            "IOMaximumIOps": 0,
            "IOMaximumBandwidth": 0,
            "MaskedPaths": [
                "/proc/asound",
                "/proc/acpi",
                "/proc/kcore",
                "/proc/keys",
                "/proc/latency_stats",
                "/proc/timer_list",
                "/proc/timer_stats",
                "/proc/sched_debug",
                "/proc/scsi",
                "/sys/firmware"
            ],
            "ReadonlyPaths": [
                "/proc/bus",
                "/proc/fs",
                "/proc/irq",
                "/proc/sys",
                "/proc/sysrq-trigger"
            ]
        },
        "GraphDriver": {
            "Data": {
                "LowerDir": "/var/lib/docker/overlay2/d6da8b7be1c593baffb442523f3ffd6fb96a9a2036d8bdac5996a5897c6efd90-init/diff:/var/lib/docker/overlay2/85a09a45a7059dff07262e9a3d89b65303a75b514a789335fe3d84fa81e23a5c/diff:/var/lib/docker/overlay2/33fc523d3c873464f8a5a394dfe0ece4990a2b68d2dddfbcb1f7ae86e87e0cb6/diff:/var/lib/docker/overlay2/503b045a316c1977d0d0fae6d336094b56fa591682d812d52a5371cc450b9596/diff:/var/lib/docker/overlay2/5fe0709b633a59d99c60d4f122fcc3aaaed2913e3d6718dd90d4568173e75b11/diff:/var/lib/docker/overlay2/bb60c3716445387fd4c526f72bd2b93c263babbce38f6bb1276985c34d28aa6f/diff:/var/lib/docker/overlay2/602c47432472dae0a7eef2dcf91f270090ed9c8ff2d6e0e5688b6f1792895c11/diff:/var/lib/docker/overlay2/24e5c83a0e9ac4f4fe38c9ba349db2cee1c79327da8a663e230bad3a22eea957/diff:/var/lib/docker/overlay2/35307c36b0e275d1dcd6e671e6092a5313af6cdbd60a7671de1dc1346d7f6cb0/diff:/var/lib/docker/overlay2/4639a60f3e8609d8da66e0d8cbb8b4f5a5f00c697a8b2adba4f11325e34ac21b/diff:/var/lib/docker/overlay2/6c01eeaf5c7c886f684f052b9528d8495322768502c916959ddf585998c3b92c/diff:/var/lib/docker/overlay2/9f41f16ff822550d90ad62513def9f0cfe5229e37bb34b909a72f1a683d6fb07/diff:/var/lib/docker/overlay2/ea2fc40c561756f7d9ce97f2741ddadded740f81a009e5875cfb1ac567f8a8f6/diff:/var/lib/docker/overlay2/6b90e9b7b847bb24a9cc8394f10c5be77d10521be3544515d2af3ff5f8c67d9a/diff",
                "MergedDir": "/var/lib/docker/overlay2/d6da8b7be1c593baffb442523f3ffd6fb96a9a2036d8bdac5996a5897c6efd90/merged",
                "UpperDir": "/var/lib/docker/overlay2/d6da8b7be1c593baffb442523f3ffd6fb96a9a2036d8bdac5996a5897c6efd90/diff",
                "WorkDir": "/var/lib/docker/overlay2/d6da8b7be1c593baffb442523f3ffd6fb96a9a2036d8bdac5996a5897c6efd90/work"
            },
            "Name": "overlay2"
        },
        "Mounts": [
            {
                "Type": "bind",
                "Source": "/Users/krystiank/db/maria_cnf",
                "Destination": "/etc/mysql/conf.d",
                "Mode": "rw",
                "RW": true,
                "Propagation": "rprivate"
            },
            {
                "Type": "volume",
                "Name": "d51df909be41a03544fba8ead271e3c26b69bacdb62e16bdd42b201018c43ba6",
                "Source": "/var/lib/docker/volumes/d51df909be41a03544fba8ead271e3c26b69bacdb62e16bdd42b201018c43ba6/_data",
                "Destination": "/var/lib/mysql",
                "Driver": "local",
                "Mode": "",
                "RW": true,
                "Propagation": ""
            }
        ],
        "Config": {
            "Hostname": "0a841717401c",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "ExposedPorts": {
                "3306/tcp": {},
                "5432/tcp": {}
            },
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "MYSQL_ROOT_PASSWORD=password",
                "MYSQL_DATABASE=rssmonster",
                "ACCEPT_EULA=Y",
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "GOSU_VERSION=1.12",
                "GPG_KEYS=177F4010FE56CA3336300305F1656F24C74CD1D8",
                "MARIADB_MAJOR=10.1",
                "MARIADB_VERSION=1:10.1.48+maria-1~bionic"
            ],
            "Cmd": [
                "mysqld"
            ],
            "Image": "mariadb:10.1",
            "Volumes": {
                "/etc/mysql/conf.d": {},
                "/var/lib/mysql": {}
            },
            "WorkingDir": "",
            "Entrypoint": [
                "docker-entrypoint.sh"
            ],
            "OnBuild": null,
            "Labels": {
                "com.docker.compose.config-hash": "3980c17ef35aa15944f74bb088a21955c6e2d26fe859dc69fa2deb133f6d6579",
                "com.docker.compose.container-number": "1",
                "com.docker.compose.oneoff": "False",
                "com.docker.compose.project": "pccr",
                "com.docker.compose.project.config_files": "docker-compose.yml,docker-compose.local.yml",
                "com.docker.compose.project.environment_file": ".env",
                "com.docker.compose.project.working_dir": "/Users/krystiank/repos/pccr/pccr",
                "com.docker.compose.service": "local_maria_db",
                "com.docker.compose.version": "1.27.4"
            }
        },
        "NetworkSettings": {
            "Bridge": "",
            "SandboxID": "a1af979cc81d1f1f41ea8293f9f4d6adfdaca8a597b6e3d95bc02204f5b70edc",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "3306/tcp": null,
                "5432/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "6603"
                    }
                ]
            },
            "SandboxKey": "/var/run/docker/netns/a1af979cc81d",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "",
            "Gateway": "",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "",
            "IPPrefixLen": 0,
            "IPv6Gateway": "",
            "MacAddress": "",
            "Networks": {
                "pccr_vpc1": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": [
                        "local_maria_db",
                        "0a841717401c"
                    ],
                    "NetworkID": "2eab3acc35ed4d269cf0f90a6f90f747b5e75f761af52c34bb4fe09e157e7542",
                    "EndpointID": "16e194805019936efa4a7df95357da0de558fd1825327ff782e979656031b113",
                    "Gateway": "10.1.0.1",
                    "IPAddress": "10.1.0.4",
                    "IPPrefixLen": 24,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:0a:01:00:04",
                    "DriverOpts": null
                }
            }
        }
    }
]

What am I doing wrong? :(

Mershel
  • 542
  • 1
  • 9
  • 17
  • Give an inspect in your container. docker container inspect – Yuri Melo Mar 21 '21 at 18:02
  • 1
    I've updated the question – Mershel Mar 21 '21 at 18:06
  • Are you trying connect to your mariaDB from outside your docker? – Yuri Melo Mar 21 '21 at 18:08
  • So, you can do like this: mysql --user=root --password=password --port=6603 --host=10.1.0.1 testdb – Yuri Melo Mar 21 '21 at 18:09
  • im getting `Can't connect to MySQL server on '10.1.0.1' (60)` – Mershel Mar 21 '21 at 18:18
  • can you show this ss -lptn | grep "0.0.0.0" also try replacing localhosby by 0.0.0.0. to confirm docker is listening on localhost. – Rahul Mar 21 '21 at 18:22
  • ss -lptn | grep "0.0.0.0": `LISTEN 0 128 127.0.0.11:37761 0.0.0.0:*` When i replace local host by 0.0.0.0 `Lost connection to MySQL server at 'reading initial communication packet', system error: 0` – Mershel Mar 21 '21 at 18:25
  • 1
    Mariadb's port is `3306`. Did you change that? – Saeed Mar 21 '21 at 19:00
  • I've just done one test here in my machine. Firstly, you entry as root in mariaDB, so you must do this: In this case, I'm using MySQL. 1. select user, host from mysql.user; 2. Look at the host of your user 3. Change it for localhost 4. mysql -u -h localhost -P -p – Yuri Melo Mar 21 '21 at 20:46

3 Answers3

1

So it turned out that mariadb does not care what ports i'm providing in docker compose, or maybe I have incorrect understanding of how this works - anyway mariadb is working on port 3306 inside docker container and I can access it with no problems from other container that is in the same network (vpc1) with:

    database: "testdb",
    dialect: "mariadb",
    username: "root",
    password: "password",
    host: "local_maria_db",
    port: 3306

To make it accessible from outside of the container and network, I had to change my docker compose ports from

ports:
  - "6603:5432"

to

ports:
  - "6603:3306"

So that when I call localhost on port 6603, it gets to actual port (3306) that mariadb is working on.

Case closed, I'm just not sure why is it always working on port 3306, the only way to change this port that I can see right now - add config (by volumes) that will be loaded as the last one and will override port config that mariadb has.

Adding this as an answer, maybe it will help someone in the future.

Mershel
  • 542
  • 1
  • 9
  • 17
  • 1
    For me there was a case of also adding --prococol=TCP when connecting to localhost: `mysql -u root -P 3306 --protocol=TCP -pTestPassword`. Leaving comment so it might help somebody, because googling problem brought me here. – DevilaN Sep 29 '21 at 08:38
1

mysql/mariadb default port is 3306. Changing the mapping port of the container side in a docker-compose file does not mean that the mysql/mariadb service port is changed. Actually, it maps a host port to the service port inside of the container.

To change mysql/mariadb port one can change port = 3306 in /etc/mysql/my.cnf for example.

MoYaMoS
  • 31
  • 1
  • 5
0

in your docker compose i see you are using vpc1, if this is AWS, check your security groups, and allow from which computeror VM, you are connecting to the container from, to be able to access this.

Shaqil Ismail
  • 1,794
  • 1
  • 4
  • 5