当前位置：theitroad>如何将 mysql 工作台连接到在 docker 中运行 mysql？

如何将 mysql 工作台连接到在 docker 中运行 mysql？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/33827342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 21:25:53 　来源:igfitidea点击:

How to connect mysql workbench to running mysql inside docker?

mysqldockermysql-workbench

提问by Pawan Kumar

I am using mysql server inside docker container and able to access inside docker. How to create connection in mysql workbench running on my local(Host Machine).

我在 docker 容器内使用 mysql 服务器并且能够在 docker 内访问。如何在本地（主机）上运行的 mysql 工作台中创建连接。

回答by Andrey Lebedenko

By default after deployment MySQL has following connection restrictions:

默认情况下，部署后 MySQL 具有以下连接限制：

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

Apparently, for the security purposes you will not be able to connect to it outside of the docker image. If you need to change that to allow root to connect from any host (say, for development purposes), do:

显然，出于安全目的，您将无法在 docker 映像之外连接到它。如果您需要更改它以允许 root 从任何主机连接（例如，出于开发目的），请执行以下操作：

  1. Start your mysql image with all port mappings required:

    docker run -p 3306:3306 --name=mysql57 -d mysql/mysql-server:5.7

  1. 使用所需的所有端口映射启动您的 mysql 映像：

    docker run -p 3306:3306 --name=mysql57 -d mysql/mysql-server:5.7

or, if the complete port mapping is required:

或者，如果需要完整的端口映射：

docker run -p 3306:3306 -p 33060:33060 --name=mysql57 -d mysql/mysql-server:5.7

  1. If this is the fresh installation - grab the default password:

    docker logs mysql57 2>&1 | grep GENERATED

  2. Connect using mysqlclient directly to the mysqld in docker:

    docker exec -it mysql57 mysql -uroot -p

  3. If this is the fresh installation you will be asked to change the password using ALTER USERcommand. Do it.

  4. Run SQL:

    update mysql.user set host = '%' where user='root';

  5. Quit the mysqlclient.

  6. Restart the container:

    docker restart mysql57

  1. 如果这是全新安装 - 获取默认密码：

    docker logs mysql57 2>&1 | grep GENERATED

  2. 使用mysql客户端直接连接到docker中的mysqld：

    docker exec -it mysql57 mysql -uroot -p

  3. 如果这是全新安装，您将被要求使用ALTER USER命令更改密码。做吧。

  4. 运行 SQL：

    update mysql.user set host = '%' where user='root';

  5. 退出mysql客户端。

  6. 重启容器：

    docker restart mysql57

Now you will be able to connect from MySQL Workbench to

现在您将能够从 MySQL Workbench 连接到

host: `0.0.0.0` 
port: `3306`

After all the changes the query will show:

完成所有更改后，查询将显示：

select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+

回答by Krishna

You have to do few configuration in you docker container. Please follow the following steps.

你必须在你的 docker 容器中做一些配置。请按照以下步骤操作。

  1. Specify mysql configuration block in your docker-compose.yml. I have following mysql block under servicesobject in my docker-compose.yml file.

    services:
    db:
        image: mysql
        volumes:
            - "./.data/db:/var/lib/mysql"
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: mydb
            MYSQL_USER: user
            MYSQL_PASSWORD: pass
        ports:
            42333:3306

  2. Restart docker container and run following commands to get to the bash shell in the mysql container

    docker ps
docker exec -it <mysql container name> /bin/bash?

    Inside the container, to connect to mysql command line type,

    mysql -u root -p

    Use MYSQL_ROOT_PASSWORDas specified in the docker-compose.yml . Execute following commands to create new user.

    create user 'user'@'%' identified by 'pass';
grant all privileges on *.* to 'user'@'%' with grant option;
flush privileges;

    The percent sign (%) means all ip's. Restart the docker container.

  3. In your MySQL Workbench provide the connection details. Use MYSQL_PASSWORDas specified in your docker-compose.yml file.

    enter image description here

  1. 在 docker-compose.yml 中指定 mysql 配置块。我的 docker-compose.yml 文件中的services对象下有以下 mysql 块。

    services:
    db:
        image: mysql
        volumes:
            - "./.data/db:/var/lib/mysql"
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: mydb
            MYSQL_USER: user
            MYSQL_PASSWORD: pass
        ports:
            42333:3306

  2. 重启docker容器并运行以下命令进入mysql容器中的bash shell

    docker ps
docker exec -it <mysql container name> /bin/bash?

    在容器内部，连接到mysql命令行类型，

    mysql -u root -p

    使用docker-compose.yml 中指定的 MYSQL_ROOT_PASSWORD 。执行以下命令来创建新用户。

    create user 'user'@'%' identified by 'pass';
grant all privileges on *.* to 'user'@'%' with grant option;
flush privileges;

    百分号 (%) 表示所有 ip。重新启动 docker 容器。

  3. 在您的 MySQL Workbench 中提供连接详细信息。使用docker-compose.yml 文件中指定的 MYSQL_PASSWORD。

    在此处输入图片说明

You should now be able to connect to your mysql container.

您现在应该能够连接到您的 mysql 容器。

回答by VonC

2 docker-related conditions:

2 docker相关条件：

  • first, your docker run must map the mysql port to an host port: 

    docker run -p host:container

  • 首先，您的 docker run 必须将 mysql 端口映射到主机端口： 

    docker run -p host:container

(for instance: docker run -d -p 3306:3306 tutum/mysql)

（例如：docker run -d -p 3306:3306 tutum/mysql）

  • second, if you are using docker in a VM (docker-machine, with boot2docker), you need to use the ip of docker-machine ip <VMname>, with the host mapped port.

    http://$(docker-machine ip <VMname>):hostPort

  • 其次，如果您在 VM（docker-machine，带 boot2docker）中使用 docker，则需要使用 ip 和docker-machine ip <VMname>主机映射端口。

    http://$(docker-machine ip <VMname>):hostPort

If you need to use localhost, you would need to do some port forwarding at the VirtualBox level:

如果需要使用localhost，则需要在 VirtualBox 级别进行一些端口转发：

VBoxManage controlvm "boot2docker-vm" natpf1 "tcp-port3306,tcp,,3306,,3306"
VBoxManage controlvm "boot2docker-vm" natpf1 "udp-port3306,udp,,3306,,06"

(controlvmif the VM is running, modifyvmis the VM is stopped) (replace "boot2docker-vm" by the name of your vm: see docker-machine ls)

（controlvm如果 VM 正在运行，modifyvmVM 是否已停止）（将“ boot2docker-vm”替换为您的 vm 名称：请参阅docker-machine ls）

2 mysql-related conditions:

2 mysql相关条件：

  • As illustrated in nkratzke/EasyMySQL/Dockerfile, you need to enable remote access:

    # Enable remote access (default is localhost only, we change this
# otherwise our database would not be reachable from outside the container)
RUN sed -i -e"s/^bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/" /etc/mysql/my.cnf

  • You need to create users when startig your database in your docker image.
    See for instance nkratzke/EasyMySQL/start-database.sh, which is called by the Dockerfile CMD:

    /usr/sbin/mysqld &
sleep 5
echo "Creating user"
echo "CREATE USER '$user' IDENTIFIED BY '$password'" | mysql --default-character-set=utf8
echo "REVOKE ALL PRIVILEGES ON *.* FROM '$user'@'%'; FLUSH PRIVILEGES" | mysql --default-character-set=utf8
echo "GRANT SELECT ON *.* TO '$user'@'%'; FLUSH PRIVILEGES" | mysql --default-character-set=utf8
echo "finished"

  • 如图所示nkratzke/EasyMySQL/Dockerfile，您需要启用远程访问：

    # Enable remote access (default is localhost only, we change this
# otherwise our database would not be reachable from outside the container)
RUN sed -i -e"s/^bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/" /etc/mysql/my.cnf

  • 在 docker 映像中启动数据库时，您需要创建用户。
    参见例如nkratzke/EasyMySQL/start-database.sh，它由以下调用Dockerfile CMD：

    /usr/sbin/mysqld &
sleep 5
echo "Creating user"
echo "CREATE USER '$user' IDENTIFIED BY '$password'" | mysql --default-character-set=utf8
echo "REVOKE ALL PRIVILEGES ON *.* FROM '$user'@'%'; FLUSH PRIVILEGES" | mysql --default-character-set=utf8
echo "GRANT SELECT ON *.* TO '$user'@'%'; FLUSH PRIVILEGES" | mysql --default-character-set=utf8
echo "finished"

回答by Deadpool

Suppose you have the next content of your docker-composefile:

假设您有docker-compose文件的下一个内容：

database: image: mysql:5.6 volumes: - dbdata:/var/lib/mysql environment: - "MYSQL_DATABASE=homestead" - "MYSQL_USER=homestead" - "MYSQL_PASSWORD=secret" - "MYSQL_ROOT_PASSWORD=secret" ports: - "33061:3306"

database: image: mysql:5.6 volumes: - dbdata:/var/lib/mysql environment: - "MYSQL_DATABASE=homestead" - "MYSQL_USER=homestead" - "MYSQL_PASSWORD=secret" - "MYSQL_ROOT_PASSWORD=secret" ports: - "33061:3306"

For localhost just use host 127.0.0.1 and 33061 port enter image description here

对于本地主机，只需使用主机 127.0.0.1 和 33061 端口 在此处输入图片说明

回答by andrew

@Krishna's answer worked but with a minor change - user was added as follows

@Krishna 的回答有效，但有一个小的变化 - 用户添加如下

create user 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'pass';

see Authentication plugin 'caching_sha2_password' cannot be loaded

请参阅无法加载身份验证插件“caching_sha2_password”

回答by DNick

  1. Specify your configuration docker-compose.yml. More details here. Example:

    version: '3.1'

services:
  mysql:
    image: mysql:5.6
    container_name: test-mysql
    ports:
      - 3306:3306
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
  2. Run this comandsdocker-compose upand another terminal run docker psto see your container.
  3. Access your docker: docker exec -it test-mysql bash
  4. Inside the container, to connect to mysql command line type,run mysql -u root -p.
  5. Create a new user
  6. Finally config your MySQL Workbench

  1. 指定您的配置docker-compose.yml。更多细节在这里。例子：

    version: '3.1'

services:
  mysql:
    image: mysql:5.6
    container_name: test-mysql
    ports:
      - 3306:3306
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
  2. 运行此命令docker-compose up并运行另一个终端docker ps以查看您的容器。
  3. 访问您的泊坞窗： docker exec -it test-mysql bash
  4. 在容器内，连接到 mysql 命令行类型，运行mysql -u root -p.
  5. 创建一个新用户
  6. 最后配置你的 MySQL Workbench

enter image description here

在此处输入图片说明

回答by Pawan Kumar

I got solution for this by setting field value in Hostname: 127.0.0.1(Localhost), port by default 3306 with your creds.

我通过在Hostname: 127.0.0.1(Localhost), port by default 3306 with your creds 中设置字段值得到了解决方案。

回答by Pawan Kumar

I followed instructions shown in mysql docker hub. wrote this docker-compose.yml

我按照 mysql docker hub 中显示的说明进行操作。写了这个 docker-compose.yml

version: '3.1'

services:
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: example
    ports:
      - 3306:3306

Go to mysql workbench when you are making connection make sure to clear password in the parameters. now enter password(in my case example)

建立连接时转到mysql工作台确保清除参数中的密码。现在输入密码（在我的情况下example）

回答by Luiz Soto

You will need to configure Host/Guest IP to 0.0.0.0and Host/Guest Port to 3306in "Settings->Network->Port Forwarding".

您需要在“设置->网络->端口转发”中配置主机/来宾IP0.0.0.0和主机/来宾端口3306。

相关推荐

MySQL：错误 1227 (42000)：访问被拒绝 - 无法创建用户

MySQL：错误 1227 (42000)：访问被拒绝 - 无法创建用户

在 Centos7 上更改 mysql root 密码

在 Centos7 上更改 mysql root 密码

从 MySQL 中的一个结果集中的多个表中获取数据

从 MySQL 中的一个结果集中的多个表中获取数据

MYSQL 搜索字符串是否包含特殊字符？

MYSQL 搜索字符串是否包含特殊字符？

相关推荐
最近更新
标签