Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect mysql workbench to running mysql inside docker?

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).

like image 313
Pawan Kumar Avatar asked Nov 20 '15 12:11

Pawan Kumar


People also ask

How do I connect to a MySQL Docker database?

Here are the steps you can follow to install the Dockerhub MySQL Container: Step 1: Pull the Docker Image for MySQL. Step 2: Deploy and Start the MySQL Container. Step 3: Connect with the Docker MySQL Container.


4 Answers

By default after deployment MySQL has following connection restrictions:

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:

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

    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 mysql client 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 USER command. Do it.

  4. Run SQL:

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

  5. Quit the mysql client.

  6. Restart the container:

    docker restart mysql57

Now you will be able to connect from MySQL Workbench to

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     |
+-----------+---------------+
like image 75
Andrey Lebedenko Avatar answered Oct 22 '22 08:10

Andrey Lebedenko


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

  1. Specify mysql configuration block in your docker-compose.yml. I have following mysql block under services object 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_PASSWORD as 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_PASSWORD as specified in your docker-compose.yml file.

    enter image description here

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

like image 29
Krishna Avatar answered Oct 22 '22 08:10

Krishna


Suppose you have the next content of your docker-compose file:

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

like image 32
Deadpool Avatar answered Oct 22 '22 08:10

Deadpool


2 docker-related conditions:

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

    docker run -p host:container
    

(for instance: 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
    

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

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

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


2 mysql-related conditions:

  • 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"
    
like image 16
VonC Avatar answered Oct 22 '22 09:10

VonC