Oracle MySQL remote access with Docker Engine, sockets, and Workbench on OCI

10 min readOct 10, 2023

When using a cloud to host your containers, it’s also helpful to setup remote access for ease of administration and development.

This article will explain how to setup a MySQL container with:

Docker engine in Ubuntu ARM.
Docker socket for remote access over SSH.
Docker client on a Windows workstation.
MySQL Workbench to access the container over SSH.

The environment will be setup on Oracle Cloud Infrastructure, for the Ubuntu operating system, and ARM64 architecture.

Install Docker

Firstly, we need to install the Docker Engine on the OCI host. We will install the latest Docker Engine from Docker.

If required, uninstall the previous Docker Engine

If reusing an existing server then may want to uninstall an old Docker, if present.

# uninstall Docker (Ubuntu)
for pkg in docker.io docker-doc docker-compose podman-docker containerd runc; do sudo apt-get remove $pkg; done

Install Docker Engine

To install a Docker release, it’s possible to get it from a native repository. However to get the latest Docker release, the easiest way is to download and execute a script from Docker.

Note — the convenience script is only recommended for testing and development environments. Also doesn’t include the BuildKit plugin.

# download convenience script (for latest Docker release)
# Note: Only recommended for testing and development environments.
curl -fsSL https://get.docker.com -o get-docker.sh

# dry-run install
sudo sh ./get-docker.sh --dry-run

# install
sudo sh get-docker.sh

It’s also helpful to quickly test Docker functionality by running a container.

# note:  The docker service starts automatically on Debian based distributions.
sudo systemctl start docker

# test
sudo docker run hello-world

Then permit automatic sudo by allowing the user account to become a member of the docker group.

# post tasks
sudo usermod -aG docker $USER

# reload the user mod
newgrp docker

Now do a quick verify.

# verify
docker run hello-world

Docker post-install

For post-install tasks, the automatic restart of services is helpful:

# start on reboot
sudo systemctl enable docker.service
sudo systemctl enable containerd.service

Also setup container log file management, so that a log file does not become too big. Also setup the ability to use Docker Build Kit when building images:

sudo vim /etc/docker/daemon.json

{
  "log-driver": "json-file",
  "log-opts": {
    "max-size": "10m",
    "max-file": "3"
  },
  "features": {
    "buildkit": true
  }
}

Setup SSH

Any remote access to a cloud requires a secure connection. For this reason, we need to setup the secure shell (ie SSH).

Out client will be on a Windows workstation.

Setup SSH on the client

As all remote access will be over SSH, so we’ll need an appropriate public/private key to be available. SSH will be required for accessing the Docker server socket.

The ssh-keygen command can be used within the Windows command prompt, or bash (including ‘git for Windows’).

ssh-keygen -t rsa -b 4096 -C "put-the-owner-of-the-public-key-in-here"

Follow the prompts, and inside your client .ssh directory (in Windows), you’ll have public and private keys present.

Configure SSH on the server

Now add the SSH public key of the client, to the authorized_keys file of the server.

vim ~/.ssh/authorized_keys

Then do a quick test to ensure that a connection from Windows to the server via SSH will work:

ssh -i ~/.ssh/id_rsa ubuntu@<put-IP-of-the-server-here>

Install and setup Docker

With SSH operating normally, we now need to setup Docker.

Install the Docker CLI on the client

For remote access to the Docker Engine, we need to get the Windows client binary from here:

https://download.docker.com/win/static/stable/x86_64/

Then unzip the file, and you should see the docker CLI file ‘docker.exe‘:

cd %HOMEPATH%\docker-24.0.6\docker

dir

Test connectivity between a temporary Docker daemon and a Docker CLI.

Next, is to setup the socket for the Docker daemon. Initially, we’ll create a temporary Docker daemon for testing connectivity before creating a permanent configuration later.

Now on the server, stop any existing Docker daemon.

# stop the existing Docker daemon
sudo systemctl stop docker

Setup a socket for a temporary Docker daemon running in a session.

# on the server-side, setup the socket for the temporary Docker daemon
sudo dockerd -H unix:///var/run/docker.sock

Now leave this Docker daemon running for the time being.

On the Windows-side, do the following using the DOCKER_HOST environment variable:

set DOCKER_HOST=ssh://ubuntu@<put-IP-of-the-server-here>

Your Docker CLI has been configured to read a socket, so use the Docker client CLI in Windows as if it you are using it on the server.

docker info

If all is well, then you should get an output that has 2 sections. The client section will be like the following:

Then the server section will have various details, including the hardware details that we are using on the server.

This output displays the fact that we are using Ubuntu and the ARM architecture on the server side.

Configure a persistent Docker context on the client

To use the socket for the Docker daemon on a permanent basis, we’ll define a persistent context in Windows.

To use a context, the first thing we need to do is ensure the DOCKER_HOST environment variable used previously is cleared in the Windows session.

set DOCKER_HOST=

Now we can create the persistent context.

docker context create ^
  --docker host=ssh://ubuntu@<put-IP-of-the-server-here> ^
  --description="Remote engine" ^
my-remote-engine

Noet: the persistent context will persist after reboots of Windows.

Now switch your session to the persistent context that we just created.

docker context use my-remote-engine

All going well we can list all the contexts present, including the default ones.

docker context ls

Other contexts can be switched to, including switching to the default one, if you need to manage any of the persistent contexts.

docker context use default
docker context rm my-remote-engine
docker context ls

With connectivity with the temporary Docker daemon confirmed, we can now stop it and create a permanent configuration for the persistent Docker daemon.

Configure a socket for the persistent Docker daemon on the server

Using systemd, we can create a permanent socket on the server-side for the Docker daemon service.

# Configuring remote access with systemd unit file
sudo vim /lib/systemd/system/docker.service

Append the socket config to the ExecStart line of the docker.service file.

# edit as required
[Service]
ExecStart=<the-existing-configs-are-here> -H unix:///var/run/docker.sock

So the docker.service file will now look similar to below:

ExecStart=/usr/bin/dockerd -H fd:// — containerd=/run/containerd/containerd.sock -H unix:///var/run/docker.sock

Now restart the Docker service so the new configuration will be used.

# reload and restart
sudo systemctl daemon-reload
sudo systemctl restart docker.service

As we have setup the remote access to the Docker socket, we can now do all Docker administration from our Windows client.

Install and setup MySQL

Pull the MySQL image

With a socket for the Docker Engine available for remote access, we are now ready to setup MySQL.

The Oracle container registry does offer images for AMD and ARM for MySQL.

https://container-registry.oracle.com/ords/ocr/ba/mysql/community-server

As we are using the ARM architecture, we need to ensure any images pulled are for ARM.

docker pull container-registry.oracle.com/mysql/community-server:8.1.0-aarch64

Now show all the images present so far.

docker images

Create a Docker user-bridge network for MySQL

Remote access to MySQL will require exposing a container port to the server, and that requires a bridge network.

A bridge network has the following features:

— the port of the container is not published directly, and is instead mapped to a host port.

— containers in a bridge network will not share the host IP address, and will instead have their own IP address.

It’s best practice not to use the default bridge network, so instead we will create a user-bridge network for our MySQL container.

docker network create mysql-net

Now view the configuration of the user-bridge network.

docker network ls

docker network inspect mysql-net

The listing will contain the reference to the subnet as 172.18.0.0. So when we start the MySQL container, it will be on that subnet.

Create the MySQL container

As MySQL is an Oracle product, we are going to configure the container to execute under a Linux account named oracle. Create a Linux account for the container as below.

adduser oracle

Then for security purposes, remove the ability of the oracle account to login by using a /usr/sbin/nologin clause.

sudo vim /etc/passwd

oracle:x:1002:1002:,,,:/home/oracle:/usr/sbin/nologin

Note: the UID for the oracle account in the passwd file is 1002. Compared with a default which may be 1001.

Now using the user-bridge network created previously, and the user UID for the oracle account of 1002, the container can be created and executed with a port mapping of 3306.

docker run -d ^
  -p 3306:3306 ^
  --user=1002 ^
  --restart on-failure ^
  --name=mysql1 ^
  --network mysql-net ^
container-registry.oracle.com/mysql/community-server:8.1.0-aarch64

Note: for our container, we will not use any persistent storage on the host.

Now list all the containers present.

docker ps -a

# alternative
docker container ls -a

From the host, it’s easy to verify that MySQL is running in the oracle account from inspection of the top processes:

top

So MySQL is running and the mysqld process is owned by the oracle account.

Determine the IP address of the MySQL container

Now check the container information from the Docker CLI.

docker container inspect mysql1

The IP address listed as 172.18.0.2. So that IP address will be used to connect from the host.

The IP is also able to be pinged from the host.

# ping the container from the host
ping -c 2 172.18.0.2

MySQL administration

Before we can use MySQL, we need to create users and change passwords.

The MySQL container does generate a default root password, and this password can be determined from the MySQL logs.

docker logs mysql1 2>&1 | find "GENERATED"

There will be an entry shown for the MySQL root user password that is similar to:

[Entrypoint] GENERATED ROOT PASSWORD: /P%?.@P9P6@8oUs0L3qDtlt8.7d

Now we need to change the root password before MySQL can be administered. Access the MySQL shell (ie mysql) from the Docker CLI, and input the root password.

docker exec -it mysql1 mysql -uroot -p

Then change the MySQL root password as follows.

ALTER USER 'root'@'localhost' IDENTIFIED BY '<put-new-password-in-here>';

Also add some new accounts in order to login remotely.

CREATE USER 'developer'@'%' IDENTIFIED BY 'developer$$password';

GRANT ALL PRIVILEGES ON *.* TO 'developer'@'%';

Now test the login of the localhost developer user from the remote Docker socket.

docker exec -it mysql1 mysql -udeveloper -p

MySQL Workbench

Download and install MySQL Workbench

The download for MySQL Workbench is here:

MySQL :: Download MySQL Workbench

MySQL Workbench provides DBAs and developers an integrated tools environment for: Database Design & Modeling SQL…

dev.mysql.com

After the download, you can do an MD5 check of the file, and compare with the checksum on the same website.

certutil -hashfile mysql-workbench-community-8.0.34-winx64.msi MD5

Note if you have ‘git for Windows’ then you can use the following:

md5sum --check <(echo ef5294cd0807979c060e1808fc488006 mysql-workbench-community-8.0.34-winx64.msi)

From here the file can be used to install MySQL Workbench.

Connect to MySQL

Previously, we found the IP address of the docker container to be 172.18.0.2. Now open MySQL Workbench, and connect to the container with the following details:

Connection name. (eg Demo on Docker)
Connection method. (ie Standard TCP/IP over SSH)
IP address of the host of Docker (ie SSH host), followed by the port 22.
SSH username. (eg ubuntu)
Path to your private SSH key on your workstation. (eg ~/.ssh/id_rsa)
MySQL Hostname. (eg 172.18.0.2)
MySQL port. (eg 3306)
MySQL remote username. (eg developer)

You will be asked for the MySQL developer remote password, which was defined earlier as ‘developer$$password’.

As the image that we pulled was the the community version of MySQL, we will be reminded that it may not be fully compatible with MySQL Workbench.

Continue anyway.

Now we have a remote MySQL connection.

Paul Guerin has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). He was awarded as a most valued contributor for the My Oracle Support Community (2019), and continues to be a participant of the Oracle ACE program.