Today I am going to explain the procedure to test the public cloud hosted MySQL docker in Ubuntu Server to your local MySQL WorkBench.
We configured a Ubuntu Server on public cloud and installed docker.
Ping the public IP address to make sure, it is reachable to your pc/laptop.
Configure System
Install MySQL Docker Image
# docker pull mysql/mysql-server:latest
Check the images
#docker imagesYou should be able to view the downloaded mysql server image
Start the MySQL Server
# docker run --name=mysql1 -p 33061:3306 -d mysql/mysql-server
-d : Run in daemon mode
-p host_port:container_port
--name: container name
The mapped public ubuntu server port is 33061, which is mapped to the mysql server port 3306.
Check the containers
# docker psYou should be able to view your mysql docker containers with container id and ports information
Get the MySQL root user password
MySQL generates a one-time password for docker container and writes to the log.- To view the log, execute the following command
# docker logs mysql1
- To get the generate one-time password, filter the log with keyworkd "GENERATED"
# docker logs mysql1 2>&1 | grep GENERATED
- You should be able to get the line like below
GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs
- Copy the one-time password. In the next step, we will use this to login.
Reset MySQL root user password
- Connect to MySQL server
# docker exec -it mysql1 -uroot -p
- Paste the generated root password
- Change the password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpwd';
mysql> FLUSH PRIVILEGES;
- Though you have the root user credentials, you won't be able to connect.
Root cause: root user belongs to localhost, not for the public host usage
Solution: create another user with all privileges
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'youpwd';
mysql> GRANT ALL PRIVILEGES on *.* TO USER 'username'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
- Exit the mysql
mysql> exit;
- Restart the docker
# docker restart mysql1
Connect from MySQL Workbench
- Open MySQL Workbench
- Click on add new connection icon
- Enter the "Connection name"
- Enter host name "public IP address" or "URL"
- Enter username as "dev" (Above created user)
- Click on Password "Store in vault" button and enter the "dev" user password
- Click on "Test Connection" button
- You should be able to view the "Successfully made the SQL Connection" pop-up
Hope this tutorial helps you to get connected with your public hosted mysql docker instance to your local mysql workbench.
Please send your feedback and comments to psrdotcom@gmail.com
