SSL Connection to MySQL database


I have read some answers here on serverfault that suggests it is not a good idea to open a MySQL port to the firewall, so I am trying to setup accordingly.

Two options remain to connect to MySQL:

  • SSH Tunnel
  • SSL Configuration

I have successfully managed to connect to the database with SSH Tunnel when using a database management tool – DBeaver on Windows 10, so for normal database SQL queries I use manually I am able to work.

The problem is I use a 3rd party data importer for Magento 2 – Magmi which has a web-interface. (also written in PHP).

There are no settings to connect via SSH Tunnel, only SSL settings : _SSL CA, SSL Cert, SSL Key.
I assume these settings are specifically requesting for certificate file location.

NORMAL BEHAVIOUR of Magmi:

**TO LOGIN to the web interface, you specify MySQL database details of Magento 2, and it verifies against a database user. In other words before you can access the /magmi.php, it verifies again **

  • How would I go to setup SSL configuration so that I will be able to connect to MySQL that way?
  • Do I need to create a new SSL certificate/key set, or can I use the Nginx one – I have the local Nginx certificate/key, they are not CA signed, in otherwords just the cert/key created internally

From the SSL Configuration settings of Magmi web interface:

SSL Settings – If incorrect settings are used here, you may lock yourself out of these settings, only way to fix is to manually edit magmi.ini.
Keep value empty if one of the SSL Certificate settings are not used (for instance only SSL CA certificate)

NOTE: I have tried using the Nginx cert/key set (readable as web-server user) (leaving CA value empty), but it does not authenticate me correctly. (prompting over and over for login).

I guess I need to do something extra so that it will work with MySQL.
I assume for DBeaver specifically the cert needs to be installed on Windows OS.

1 – DBeaver interface (so that I can test it directly from Windows)

2 – From web server (Ubuntu) where Magmi is located at the same destination as web site /var/www/html/magento2

Setup

At the moment Local network, but I would like to make sure the setup will work once site go onto internet hosted setup

  • Windows 10 which I am working from
  • Ubuntu Web Server which I can access via SSH/Putty/DBeaver from Windows 10
    • Server setup:
      Apache web server,
      Nginx proxy with SSL configured forwarding to Varnish,
      Varnish cache on port 6081,
      MySQL Server on host

SSL works, I am able to connect to https://localsite.

TLDR

SSL works, I need setup assistance for MySQL to work with Magmi/DBeaver via SSL option so that I can get a MySQL database user authenticated on web-interface login. (as there is no SSH tunnel/MySQL option). I need the web interface of Magmi to change settings, import files etc.