How to connect mysql workbench to running mysql inside docker?
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
If this is the fresh installation - grab the default password:
docker logs mysql57 2>&1 | grep GENERATED
Connect using
mysql
client directly to the mysqld in docker:docker exec -it mysql57 mysql -uroot -p
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 |+-----------+---------------+
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 psdocker 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.
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.
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"