个人中心
gpt4 book ai didi

mysql - 如何从我的主机连接到在 docker 容器中运行的 mysql 服务器？

转载 作者：行者123 更新时间：2023-12-02 18:09:52 30 4
gpt4 key购买 nike

我无法从我的主机 (OSX 10.13.6) 连接到在 docker 容器中运行的 mysql 服务器。

如果我在我的主机上启动一个 mysql 服务器(mariadb)(不使用 docker)，我可以使用 mysql 客户端成功连接到 mysql 服务器:

~$ mysql.server start
Starting MariaDB
.200805 13:41:03 mysqld_safe Logging to '/usr/local/var/mysql/My-MacBook-Pro-2.local.err'.
200805 13:41:03 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
 SUCCESS! 

~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.5-10.4.13-MariaDB Homebrew

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from people;
+----+--------+------+
| id | name   | info |
+----+--------+------+
|  1 | 7stud  | abc  |
|  2 | Beth   | xxx  |
|  3 | Diane  | xyz  |
|  4 | Kathy  | xyz  |
|  5 | Kathy  | xyz  |
|  6 | Dave   | efg  |
|  7 | Tom    | zzz  |
|  8 | David  | abc  |
|  9 | Eloise | abc  |
| 10 | Jess   | xyz  |
| 11 | Jeffsy | 2.0  |
| 12 | XXX    | xxx  |
| 13 | XXX    | xxx  |
+----+--------+------+
13 rows in set (0.00 sec)

mysql> exit
Bye

~$ mysql.server stop
Shutting down MariaDB
.. SUCCESS!

接下来，如果我在 docker 容器内运行 mysql 服务器，我可以从容器内成功连接到 mysql 服务器:

~$ docker run --rm --name my-mysql -e MYSQL_ROOT_PASSWORD=kathy -p 3606:3606 -d mysql
7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7

~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                         NAMES
7457d62496b1        mysql               "docker-entrypoint.s…"   2 seconds ago       Up 2 seconds        3306/tcp, 33060/tcp, 0.0.0.0:3606->3606/tcp   my-mysql

~$ docker exec -it my-mysql bash
root@7457d62496b1:/# mysql -u root -p
Enter password: kathy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye
root@7457d62496b1:/# exit
exit
~$

但是，如果我尝试从我的主机连接到在 docker 容器中运行的 mysql 服务器，我会失败:

~$ mysql -h 127.0.0.1:3306 -u root -p
Enter password: kathy

ERROR 2005 (HY000): Unknown MySQL server host '127.0.0.1:3306' (0)

~$ docker inspect my-mysql
[
    {
        "Id": "7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7",
        "Created": "2020-08-05T19:49:32.0561306Z",
        "Path": "docker-entrypoint.sh",
        "Args": [
            "mysqld"
        ],
        "State": {
            "Status": "running",
            "Running": true,
            "Paused": false,
            "Restarting": false,
            "OOMKilled": false,
            "Dead": false,
            "Pid": 7109,
            "ExitCode": 0,
            "Error": "",
            "StartedAt": "2020-08-05T19:49:32.4423804Z",
            "FinishedAt": "0001-01-01T00:00:00Z"
        },
        "Image": "sha256:0d64f46acfd1af4ee6a162f80c6e07e843761bf14d412060023bf0e69e720fb4",
        "ResolvConfPath": "/var/lib/docker/containers/7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7/resolv.conf",
        "HostnamePath": "/var/lib/docker/containers/7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7/hostname",
        "HostsPath": "/var/lib/docker/containers/7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7/hosts",
        "LogPath": "/var/lib/docker/containers/7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7/7457d62496b109fda41b57dea026cc6762bb2b7e754cb3e1987228b55ec461f7-json.log",
        "Name": "/my-mysql",
        "RestartCount": 0,
        "Driver": "overlay2",
        "Platform": "linux",
        "MountLabel": "",
        "ProcessLabel": "",
        "AppArmorProfile": "",
        "ExecIDs": null,
        "HostConfig": {
            "Binds": null,
            "ContainerIDFile": "",
            "LogConfig": {
                "Type": "json-file",
                "Config": {}
            },
            "NetworkMode": "default",
            "PortBindings": {
                "3606/tcp": [
                    {
                        "HostIp": "",
                        "HostPort": "3606"
                    }
                ]
            },
            "RestartPolicy": {
                "Name": "no",
                "MaximumRetryCount": 0
            },
            "AutoRemove": true,
            "VolumeDriver": "",
            "VolumesFrom": null,
            "CapAdd": null,
            "CapDrop": null,
            "Capabilities": null,
            "Dns": [],
            "DnsOptions": [],
            "DnsSearch": [],
            "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": [],
            "BlkioDeviceReadBps": null,
            "BlkioDeviceWriteBps": null,
            "BlkioDeviceReadIOps": null,
            "BlkioDeviceWriteIOps": null,
            "CpuPeriod": 0,
            "CpuQuota": 0,
            "CpuRealtimePeriod": 0,
            "CpuRealtimeRuntime": 0,
            "CpusetCpus": "",
            "CpusetMems": "",
            "Devices": [],
            "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/bd8cf726bca86b4a5da2253ba4d8fd809e7888bd0a8585337e365f00d4ce0a3c-init/diff:/var/lib/docker/overlay2/d28436ec5b86d97829d4db76ecac79feedf9d7de78be7fac9364b9030e5501c6/diff:/var/lib/docker/overlay2/b4ccb4e64dccd4466971f59b4ddefc0d69943e8a0bb6327dbeccd602d2550378/diff:/var/lib/docker/overlay2/b033c732901687d589a6bd69df8249f661fc9221b8305b32084dfd506dd6014f/diff:/var/lib/docker/overlay2/7078174167da7480c3bea6bf1bb831c1de53479e6c866ac88634ba83560c702e/diff:/var/lib/docker/overlay2/413086cc620e9c9a9bfab7fccaa0cb7e5836419ad52453ae76accf6c9f09be8d/diff:/var/lib/docker/overlay2/0a2c718ad2009d757554e539995cf5901bca5cd1a5ffb9d430ee137fb843adc8/diff:/var/lib/docker/overlay2/00e175c2a0975b7663ccadde84d5bd2f8ea957e9e302ab5c20a9840e34ddd0c5/diff:/var/lib/docker/overlay2/7be82f60f3b9fcb23d0d6336005987dd2c98667622833b58fa9f6362a2a02ba5/diff:/var/lib/docker/overlay2/8c347ec6ae30e0109728c0a056d36b5c64b8f907ec482dc9680bc2d8cfe6e9ad/diff:/var/lib/docker/overlay2/aaf2ed3d56a5312f4daf6efe6f456d95e54cda92e4efe972862c43489d891d3d/diff:/var/lib/docker/overlay2/88bacf56afbf61f9f8c94037a48df63c9fd549682ecf2c446a705ae661f6fe51/diff:/var/lib/docker/overlay2/3b9cf88d35e75201fd69adf020d396d079f7a513b64bd799c118459fe13446af/diff",
                "MergedDir": "/var/lib/docker/overlay2/bd8cf726bca86b4a5da2253ba4d8fd809e7888bd0a8585337e365f00d4ce0a3c/merged",
                "UpperDir": "/var/lib/docker/overlay2/bd8cf726bca86b4a5da2253ba4d8fd809e7888bd0a8585337e365f00d4ce0a3c/diff",
                "WorkDir": "/var/lib/docker/overlay2/bd8cf726bca86b4a5da2253ba4d8fd809e7888bd0a8585337e365f00d4ce0a3c/work"
            },
            "Name": "overlay2"
        },
        "Mounts": [
            {
                "Type": "volume",
                "Name": "f11c80d7d01140465818fac7d5f7e6083ff509abe8591166a20caa1f568c7646",
                "Source": "/var/lib/docker/volumes/f11c80d7d01140465818fac7d5f7e6083ff509abe8591166a20caa1f568c7646/_data",
                "Destination": "/var/lib/mysql",
                "Driver": "local",
                "Mode": "",
                "RW": true,
                "Propagation": ""
            }
        ],
        "Config": {
            "Hostname": "7457d62496b1",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "ExposedPorts": {
                "3306/tcp": {},
                "33060/tcp": {},
                "3606/tcp": {}
            },
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "MYSQL_ROOT_PASSWORD=kathy",
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "GOSU_VERSION=1.12",
                "MYSQL_MAJOR=8.0",
                "MYSQL_VERSION=8.0.21-1debian10"
            ],
            "Cmd": [
                "mysqld"
            ],
            "Image": "mysql",
            "Volumes": {
                "/var/lib/mysql": {}
            },
            "WorkingDir": "",
            "Entrypoint": [
                "docker-entrypoint.sh"
            ],
            "OnBuild": null,
            "Labels": {}
        },
        "NetworkSettings": {
            "Bridge": "",
            "SandboxID": "d7ae1d7c552ff3bd8e3b457646a314cddad73082d463ccca4c8d67a4c77aa574",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "3306/tcp": null,
                "33060/tcp": null,
                "3606/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "3606"
                    }
                ]
            },
            "SandboxKey": "/var/run/docker/netns/d7ae1d7c552f",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "a642243a0c85b09821ab0bb4e42d310e02a8c29e4b6949212776938c37d23019",
            "Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:02",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "ce024a8603c56f96f22700049e12c7687e804c4f1aa00638ed9156ddab36b152",
                    "EndpointID": "a642243a0c85b09821ab0bb4e42d310e02a8c29e4b6949212776938c37d23019",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:02",
                    "DriverOpts": null
                }
            }
        }
    }
]

~$ mysql -h 172.17.0.2:3306 -u root -p
Enter password: kathy
ERROR 2005 (HY000): Unknown MySQL server host '172.17.0.2:3306' (0)

~$ mysql -h localhost -P 3306 --protocol=tcp -u root -p
Enter password: kathy
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (61)

~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                         NAMES
7457d62496b1        mysql               "docker-entrypoint.s…"   10 minutes ago      Up 10 minutes       3306/tcp, 33060/tcp, 0.0.0.0:3606->3606/tcp   my-mysql

我做错了什么？

尝试 Neo Anderson 的建议:

~$ docker run --rm --name my-mysql -e MYSQL_ROOT_PASSWORD=kathy -p 3306:3306 -d mysql
4cba45a3d758f53256fe88aa9e33ea709a23d0c55c035d4009bbc5685d2e09ee

~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
4cba45a3d758        mysql               "docker-entrypoint.s…"   4 seconds ago       Up 4 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   my-mysql

~$ mysql --host=127.0.0.1 -P 3306 --user=root -p
Enter password: kathy 
ERROR 2026 (HY000): SSL connection error: unknown error number

我没有安装 docker-machine，所以我安装了它，但我得到:

~$ docker-machine ip default
Docker machine "default" does not exist. Use "docker-machine ls" to list machines. Use "docker-machine create" to add a new one.

~$ docker-machine ls
NAME   ACTIVE   DRIVER   STATE   URL   SWARM   DOCKER   ERRORS

..

好的，知道了! SSL 连接错误的原因:

mysql8 can not be accessed by mysql clients lower then version 8

https://www.digitalocean.com/community/questions/error-2026-hy000-ssl-connection-error-unknown-error-number

在我的主机上，我有 mysql 版本:

~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.10, for osx10.9 (x86_64) using  EditLine wrapper

我正在使用 mysql 5.7.10 附带的 mysql 客户端尝试连接到在 docker 容器中运行的 mysql 服务器。但是，docker容器中运行的镜像是mysql:latest，mysql的最新版本是8.x。为了解决所有问题，我改用了 docker 镜像 mysql:5.7:

~$ docker run --rm --name my-mysql -e MYSQL_ROOT_PASSWORD=kathy -p 56789:3306 -d mysql:5.7
94dfc72013bec3fada45225153974173a816ffc4b8a424eea735d57fecf50c4c

现在我可以使用我主机上的 mysql 5.7 客户端从我的主机连接到在 docker 容器中运行的 mysql 服务器:

~$ mysql -P 56789 -u root -p
Enter password: kathy
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

错误是由于 tcp 不是默认协议(protocol)，因此您可以将 localhost 指定为 -h 127.0.0.1，这会隐式设置协议(protocol)为 tcp，或者您可以使用 --protocol=tcp 显式设置协议(protocol):

~$ mysql -h 127.0.0.1 -P 56789 -u root -p
Enter password: kathy

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

或:

~$ mysql -P 56789 --protocol=tcp -u root -p
Enter password: kathy

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

最佳答案

默认情况下，mysql图像在端口 3306 上监听。
在您的 docker run 命令中，您使用的是 -p 3606:3606。这会创建一个从主机 3606 到同一端口上的容器的 DNAT 规则 - 没有服务正在监听。

尝试使用此命令启动容器(请注意，源可以是您主机上未使用的任何端口 - 在下面的示例中，我保留了您的初始端口 3606):

docker run --rm --name my-mysql -e MYSQL_ROOT_PASSWORD=kathy -p 3606:3306 -d mysql

要将 mysql 客户端从主机连接到容器，请添加 -P(端口)并明确指定您不使用默认端口:

mysql --host=127.0.0.1 -P 3606 --user=root -p

如果这仍然不起作用，请在您的 Mac 上运行 docker-machine ip default 并将 127.0.0.1 替换为该 IP 地址。

关于mysql - 如何从我的主机连接到在 docker 容器中运行的 mysql 服务器？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/63272789/

30 4 0
文章推荐： php - urlencode 但忽略某些字符
文章推荐： javascript - 限制诺基亚 map 的缩放(Javascript Api)
文章推荐： r - 一种更优雅的方式，将两个向量组合为单独的列(或数据帧)，匹配行，并在不匹配的地方具有 NA
文章推荐： javascript - 向左水平滚动无法正常工作
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com