Koozali.org: home of the SME Server

MySQL Workbench Named Pipe Connection to SME Server 10

Offline rrizley

  • 16
  • +0/-0
MySQL Workbench Named Pipe Connection to SME Server 10
« on: May 19, 2022, 07:02:06 PM »
I am having difficulty connecting to my SME Server 10 with MySQL Workbench v8.0.28

Online Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: MySQL Workbench Named Pipe Connection to SME Server 10
« Reply #1 on: May 20, 2022, 04:16:58 PM »
my red car does not work. what is the issue?

help us to help you.


what did you try?
what were you expecting?
what result have you obtained?
what says your mariadb logs?
what says the client ?

also remember we do not use mysql bu mariadb. check with mariadb if it is supported and for alternatives. 

Offline rrizley

  • 16
  • +0/-0
Re: MySQL Workbench Named Pipe Connection to SME Server 10
« Reply #2 on: May 20, 2022, 04:25:21 PM »
I apologize for the nebulous request but I was not certain what to ask.  Standard TCP/IP has always worked for me.  But now it is my understanding that Local Socket/Pipe is required.  I am attempting to connect via Local Socket/Pipe.  It is asking for Socket/Pipe Path I am not certain what to enter.  I tried /var/lib/mysql/mysql.sock but it says Failed to Connect

Online Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: MySQL Workbench Named Pipe Connection to SME Server 10
« Reply #3 on: May 20, 2022, 05:25:29 PM »
where is installed the workbench app?
socket is for local (read localhost) use.

tcp ip is disabled by default as it is a security issue.  unless you configure tls in mariadb for your user, then all exchange is in clear text.

alternative could be to to ssh the socket as a local port on your machine



https://serverfault.com/questions/127794/forward-local-port-or-socket-file-to-remote-socket-file

or enable the network use and configure tls. see wiki for enabling network use. see mariadb doc to create a tls access

Offline mmccarn

  • *
  • 2,626
  • +10/-0
Re: MySQL Workbench Named Pipe Connection to SME Server 10
« Reply #4 on: May 21, 2022, 02:51:02 PM »
Assuming you have installed MySQL Workbench on a workstation (and not directly on the SME server):

1. Enable networking on the SME Server
Code: [Select]
config setprop mariadb LocalNetworkingOnly no
signal-event e-smith-mysql-update

2. Verify that port 3306 is listening for connections:
Code: [Select]
# netstat -an |grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

3a [INSECURE - data and credentials visiible in plain text on your local network]
Point MySQL WorkBench at your SME server on port 3306

3b [SECURE using SSH]
i. Establish an SSH tunnel
From the workstation running MySQL Workbench, tunnel port 3306 to the SME server
Code: [Select]
#NOTE: Run a command similar to this ON THE WORKSTATION, not on the SME server
ssh root@[sme-server] -L 3306:localhost:3306
Notes:
*  [sme-server]: can be an IP address.  If it's a name, it must resolve correctly from your workstation
*  3306: the first 3306 is the port that will be opened on your workstation.  If you're already running mysql or mariadb locally  on the workstation you may need to use a different port here.  Any unused port will do.
*  :localhost:3306 tells ssh that at the remote end the tunnel should connect to port 3306 on "localhost" - in this case the SME server.


ii. Configure MySQL Workbench to talk to "localhost' (the workstation) using the first port number in the ssh command (3306 in this example)
NOTE:
*  I tested this process connecting the wordpress database on my SME server.  This reminded me that the only non-root user with access to that database is 'wp-user'@'localhost' -- I could not connect with this user until I created an ssh tunnel.
* If port 3306 is already in use on the workstation, you could (for example) use "-L 3307:localhost:3306", then tell MySQL Workbench to connect to "localhost:3307"

3c. [SECURE using TLS]
Sorry, I have no idea how to do this.  I did not see any simple option for enabling TLS in the SME templates, so I suspect this will be somewhat tricky...

Online Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: MySQL Workbench Named Pipe Connection to SME Server 10
« Reply #5 on: May 21, 2022, 04:06:47 PM »

3c. [SECURE using TLS]
Sorry, I have no idea how to do this.  I did not see any simple option for enabling TLS in the SME templates, so I suspect this will be somewhat tricky...

indded will need some custom template
enabling
https://mariadb.com/docs/security/data-in-transit-encryption/enable-tls-server/

requiring
https://mariadb.com/docs/security/data-in-transit-encryption/require-tls-server/

setting a user with tls
https://mariadb.com/kb/en/securing-connections-for-client-and-server/