Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Search
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Brand Logo

Cloudron Forum

Apps | Demo | Docs | Install
  1. Cloudron Forum
  2. LAMP
  3. Connecting Cloudron LAMP Database to Desktop Applications

Connecting Cloudron LAMP Database to Desktop Applications

Scheduled Pinned Locked Moved LAMP
4 Posts 3 Posters 148 Views 3 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • I Offline
      I Offline
      IniBudi
      wrote on last edited by
      #1

      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.

      1 Reply Last reply
      0
      • BrutalBirdieB Offline
        BrutalBirdieB Offline
        BrutalBirdie
        Partner
        wrote on last edited by BrutalBirdie
        #2

        ❓ 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 🙂

        Like my work? Consider donating a drink. Cheers!

        I 1 Reply Last reply
        2
        • J Offline
          J Offline
          joseph
          Staff
          wrote on last edited by
          #3

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

          1 Reply Last reply
          1
          • BrutalBirdieB 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 🙂

            I Offline
            I Offline
            IniBudi
            wrote on last edited by
            #4

            @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.

            1 Reply Last reply
            0
            Reply
            • Reply as topic
            Log in to reply
            • Oldest to Newest
            • Newest to Oldest
            • Most Votes


              • Login

              • Don't have an account? Register

              • Login or register to search.
              • First post
                Last post
              0
              • Categories
              • Recent
              • Tags
              • Popular
              • Bookmarks
              • Search