Accessing remote databases using phpMyAdmin running locally

August 17, 2017

#Linux #MySQL #PHP #phpMyAdmin #Raspberry Pi #WordPress

I believe it makes a lot of sense to only ever use a local copy of the phpMyAdmin client typically used to manage MySQL databases – ie to access live servers remotely via the local client. Installing phpMyAdmin on an actual server creates an additional unnecessary potential vulnerability – another login point.

I have the phpMyAdmin client installed on the Raspberry Pi which is use as a local network web server for development. I can then access the client interface from any machine on the local network and use phpMyAdmin to manage the remote databases.

There are plenty of guides explaining this online. In brief – two steps involved in setting this up to access remote databases:

  1. Create new entries for each of the remote databases in the file.

  2. Use SSH tunnelling to create a secure connection from the local machine to the remote host. Eg: sudo ssh -fNL 3307:localhost:3306 sudo_user@REMOTE_HOST_IP_ADDRESS

NB: I found that the repository version of phpMyAdmin for my Linux distribution was always somewhat out of date. I decided, instead, to manually install phpMyAdmin under the local web server’s default root directory and to access it directly from there. I probably wouldn’t have wanted to do that if I were not running it from behind a NAT router.

Raspberry Pi local web server for development

July 17, 2017

#Apache #LAMP #Linux #MySQL #PHP #Raspberry Pi #Squid #SSHFS #WordPress

Ideally we never want to be working or testing on a live public-facing installation. But we want our test environment to mimic the actual domain – eg URLs etc. For a while I was using MAMP Pro running under macOS on my development machine. MAMP creates a local Apache/MySQL/PHP stack. Over time however I found this to be a relatively convoluted solution. In particular I found myself having to sometimes solve specific MAMP related issues. Or after a re-installation, I would have to work through how to do set everything up again under MAMP.

A headless Raspberry Pi + LAMP stack running on the local network is a great platform on which to run a local web server for development (it’s important that the Pi is running off a USB stick since constant write cycles would apparently quickly destroy an SD Card). I’m using a Pi 3 connected via wifi. I don’t need to remember a different set of ways of doing stuff – it’s more or less like the actually Linux server environments we work with.

Apache virtual hosting is configured exactly as if the server were on the live internet – with Squid set up such that the Pi also serves as a proxy server. This way all devices on the local network can potentially see the development domains as if they were on the real internet (eg is on the local network during development or testing). Testing or developing under macOS or a desktop Linux, edit /etc/hosts such that points to the local web server address – but on iPads, phones etc this is not trivial – hence the benefit of running a proxy server. Under iOS and Android the proxy settings can be appended to the network connection.

The local webserver is accessed exactly as if it were on the real internet – eg via SSH. Or we can use SSHFS to mount the remote file systems locally – such that the directories and files needed can be browsed locally or opened for editing. Under macOS we can use FUSE.

Visual Studio Code
Visual Studio Code with remote file system mounted via SSHFS using FUSE for macOS

Show All Posts