0

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.

CvRChameleon
  • 103
  • 1
  • 3
  • So, how do you tell DBeaver it has to use the tunnel? – Gerard H. Pille Aug 14 '20 at 10:45
  • The Magme SSL settings, aren't they to allow connecting to Magmi via https? – Gerard H. Pille Aug 14 '20 at 10:47
  • @GerardH.Pille I have tested it using DBeaver' MySQL connection settings - if I try to connect just using connection straight to DB it will fail. I enable SSH Tunnel, and input my server SSH login details there. So as soon as I do that, it will then verify by port (I assume it runs user@'localhost' at that point?) and I will be able to login. Do you need me to update with screenshots? – CvRChameleon Aug 14 '20 at 10:57
  • Not sure regarding SSL settings via https - the web interface is is already @ `https://site.local/magmi/web/magmi.php` so why it want to do that again? Its more like it uses that information to **authenticate MySQL user** – CvRChameleon Aug 14 '20 at 10:59
  • Spare me the sceenshots. Now, do you know how to create an SSH tunnel using Putty on Windows? If not, learn it: https://blog.devolutions.net/2017/4/how-to-configure-an-ssh-tunnel-on-putty. If you do that, say local port 13306 connected to remote localhost port 3306 (if that is the port MySQL is listening on), you can tell Magmi the database is on localhost (or 127.0.0.1, port 13306, using the remote DB's username and password. Make sure the loopback connector on Windows is enabled, otherwise localhost/127.0.0.1 won't be useable. – Gerard H. Pille Aug 14 '20 at 12:59
  • I have tried setting up SSH Tunnel using Putty yesterday before I asked this question. I will go through this article, maybe there is something I missed. Thanks – CvRChameleon Aug 14 '20 at 13:12
  • I may have gotten something wrong: where is Magmi running? – Gerard H. Pille Aug 14 '20 at 18:53
  • Let us [continue this discussion in chat](https://chat.stackexchange.com/rooms/111813/discussion-between-cvrchameleon-and-gerard-h-pille). – CvRChameleon Aug 15 '20 at 08:22

0 Answers0