Connecting Cloudron LAMP Database to Desktop Applications

[IniBudi]

Hi Cloudron Team,

I would like to edit the database on our LAMP setup using software like Sequel Pro or MySQL Workbench.

My question is: How can I connect the Cloudron-installed database (using LAMP) to desktop applications such as Sequel Pro or MySQL?

While phpMyAdmin is available, I'm interested in alternative methods for managing the database via a desktop application.

Thank you.

[BrutalBirdie]

Personal Question Back

Would you pay for this information / guide?
Just curious, since this is pretty much out of scope and could be considered a pro usage level.
Or the exact oposite since many non-pro users want/need UI tools to manage DBs

In any case, an answer to that question is valuable only to me to get a better understanding what scope of users would want this type of feature.

---

Too long; didn't read - Just the Solution

Connect with a terminal to your Cloudron server with an ssh-tunnel:

ssh -L 3306:172.18.30.1:3306 root@my.DOMAIN.TLD -N

Get the credentials of your App in the web-terminal:

printenv | grep -i mysql

In your client connect to 127.0.0.1:3306 with the credentials of the app.
Terminal example:

mysql --user 5f2742fa64a75866 --database 5f2742fa64a75866 --password --host 127.0.0.1

---

General Information

Hey @IniBudi I've done this for pgAdmin on Windows 11 Pro Client for a Zabbix Server.
Atleast pgAdmin has the option to set up an ssh-tunnel.

• Sequel Pro has this: https://sequelpro.com/docs/get-started/get-connected/remote
• MySQL Workbench has https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-methods-ssh.html

That would be easy. Just plug your root and ssh-key or password* and then connect to localhost.

*(if you are using a root password for ssh connections, I'll throw a book atcha! docs.cloudron.io/security/#securing-ssh-access)

---

Detailed step-by-step guide

But now for the trickery
Since the mysql running on the root level of cloudron, only manages cloudron itself and not the apps.
A normal ssh-tunnel from such UIs would only give you access to the Cloudron root mysql.

We now need the IP of the mysql docker container

# with yq installed
docker inspect mysql | yq -r .[0].NetworkSettings.Networks.cloudron.IPAddress
172.18.30.1

# without yq installed
docker inspect mysql | grep -i "IPAddress"
            "SecondaryIPAddresses": null,
            "IPAddress": "",
                    "IPAddress": "172.18.30.1",

# the "correct" way :P
docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql
172.18.30.1

Just probed 3x other Cloudron servers, and they all have 172.18.30.1 as the docker mysql ip. So you might just try that IP.

Now again, we need ssh-tunnel but with instead of forwarding 3306 from localhost (the root server localhost) we want the docker mysql localhost. (this might be a bit confusing)

From your local desktop setup run:

ssh -L 3306:172.18.30.1:3306 root@my.DOMAIN.TLD -N

Now you should be able to access 127.0.0.1:3306 with any sql client on your Desktop.

mysql --user 5f2742fa64a75866 --database 5f2742fa64a75866 --password --host 127.0.0.1

Done

[joseph]

Workbench example is here - https://docs.cloudron.io/guides/connect-mysql/#mysql-workbench

[IniBudi]

@BrutalBirdie Thank you for the incredibly comprehensive tutorial. It seems this guide is best suited for users with advanced knowledge, so I’ll need to take some time to study and understand every detail you’ve provided. Much appreciated! Thank you @joseph for the documentation reference. I'll look into it.