How to connect mysql workbench to running mysql inside docker?
MysqlDockerMysql WorkbenchMysql Problem Overview
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).
Mysql Solutions
Solution 1 - Mysql
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:
-
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
2. If this is the fresh installation - grab the default password:
`docker logs mysql57 2>&1 | grep GENERATED`
3. Connect using mysql
client directly to the mysqld in docker:
`docker exec -it mysql57 mysql -uroot -p`
4. If this is the fresh installation you will be asked to change the password using ALTER USER
command. Do it.
-
Run SQL:
update mysql.user set host = '%' where user='root';
-
Quit the
mysql
client. -
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 |
+-----------+---------------+
Solution 2 - Mysql
You have to do few configuration in you docker container. Please follow the following steps.
-
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
-
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.
You should now be able to connect to your mysql container.
Solution 3 - Mysql
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"
Solution 4 - Mysql
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 instancenkratzke/EasyMySQL/start-database.sh
, which is called by theDockerfile 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"
Solution 5 - Mysql
-
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
-
Run this comands
docker-compose up
and another terminal rundocker ps
to see your container. -
Access your docker:
docker exec -it test-mysql bash
-
Inside the container, to connect to mysql command line type,run
mysql -u root -p
. -
Finally config your MySQL Workbench
Solution 6 - Mysql
I was trying to connect from Mysql Workbench
but it wasn't allowing me. Turned out, I forgot to mention the port
. Here is the complete command to run and then connect from workbench:
Step 1 - Run docker container:
docker run --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=testdb -e MYSQL_USER=admin -e MYSQL_PASSWORD=root -d mysql:8.0.20
Explanation of above command:
-
Mysql v8.0.20
mysql:8.0.20
-
Run as a detached container (ctrl + c won't stop the container)
-d
-
Container name
--name mysql8
-
Port expose
-p 3306:3306
-
Set environment variables
-e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=testdb -e MYSQL_USER=admin -e MYSQL_PASSWORD=root
You can see live logs of the container by:
docker logs mysql8 -f
Step 2 - Connect from Mysql Workbench:
host: localhost
port: 3306
user: admin
password: root
Solution 7 - Mysql
@Krishna's answer worked but with a minor change - user was added as follows
create user 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'pass';
Solution 8 - Mysql
I found a video that showed another way to get this to work. You can specify the IP address when passing in the port number. That is, something like -p 127.0.0.1:3307:3306
instead of just -p 3307:3306
I've never seen that before
https://www.youtube.com/watch?v=20om-9Gwuc0#t=7m
Example start command
docker run -d -e MYSQL_ROOT_PASSWORD=test --name mysql8 -p 127.0.0.1:3307:3306 mysql:8
Then I was able to use MYSQL Workbench to connect to 127.0.0.1
at port 3307
.
Solution 9 - Mysql
I got solution for this by setting field value in Hostname: 127.0.0.1 (Localhost), port by default 3306 with your creds.
Solution 10 - Mysql
I followed instructions shown in mysql docker hub. wrote this 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
)
Solution 11 - Mysql
You will need to go in Settings -> Network -> Port Forwarding
and configure:
Host/Guest IP
to 0.0.0.0
Host/Guest Port
to 3306
Solution 12 - Mysql
I solved it downloading the last version of MySQL Workbench, then I used host 0.0.0.0
with port 3306
.