Remote access to MariaD

First install the LEMP stack on the server like so (to make sure we are prepared for what comes later when I will also use the database to serve a website).

To make the database accessible I first downloaded it from my hosting provider’s through myphpadmin. And then used this tutorial to load it.

I set up the local environment on the client (windows laptop) from which we will access the database. On the Windows laptop I use Conda as environment manager.

conda create -n db-test
conda activate db-test
pip install mariadb

Make sure you run these with root permissions (or as administrator on Windows 10).

We need to allow access to the database on the server through the firewall. Of course we restrict to local IP addresses only.

sudo ufw allow proto tcp from 192.168.178.0/24 to any port 3306

From there we follow the steps here, using this version:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

In that last file change the bind address tobind-address = 0.0.0.0

Set up a user with access from your specific client IP address in Madiadb on your server.

CREATE USER 'user'@'you.rcl.ien.t' IDENTIFIED BY 'password';
SELECT User, Host, Password FROM mysql.user;
GRANT SELECT, INSERT, UPDATE, DELETE, DROP  ON your_db.* TO 'test'@'you.rcl.ien.t';

For connecting I use the python connector.

With this script:

#!/usr/bin/env python

import mariadb
import sys

# Define mariadb connection configuration
config = {
  'user': 'user',
  'password': 'password',
  'host': 'you.rse.rve.r',
  'database': 'your_db',
  'port': 3306
}
# Instantiate Connection
try:
    conn = mariadb.connect(**config)
    print(f'Yup! {conn}')
    conn.close()
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

Python and LEMP stack

Get pip, venv, and git:

sudo apt install python3-pip
apt-get install python3-virtualenv
sudo apt install python-is-python3
sudo apt install git

Set up LEMP stack following this tutorial.

New install

Switching to the NVIDIA proprietary graphics driver led to a crash. I did not have a live disk, so I had to do a full re-install.

Couple of tweaks. First of all, there is a more rigorous fix for the NVIDIA driver boot issue here. At least there is now a Grub menu so that debugging is possible.

I have not yet dared to use the NVIDIA driver again. But in the start-up logs there is still an error related to the open source driver which seems to slow down the boot process.

To see boot errors:

journalctl -b | grep error

One of the things I try is this to solve the NXDOMAIN error that I saw in the boot log:

sudo rm -r /etc/resolv.conf
sudo ln -s /run/systemd/resolve/resolv.conf /etc/resolv.conf

Also followed this tip and re-installed Gnome:

sudo apt-get install --reinstall systemd gnome-settings-daemon gnome-settings-daemon-common

And enabled canonical-livepatch with a new token, as described here.

sudo snap install canonical-livepatch
sudo canonical-livepatch enable [#yourverylongtoken#]

Graphics driver and remote desktop

Regularly I got a ‘white noise’ screen after closing/opening the laptop lid without logging out. Apparently that is a graphics driver issue. Luckily there is a simple way to switch over to the proprietary Nvidia driver.

As I was sitting on the coach I wanted to do this via remote desktop from my Windows computer. Also that was quite straightforward.

Still have to test the new graphics driver, though…

And update, one day later:

OUCH. Restart does not work. Everything dead. Not ssh connection. Reboot aborts. Black screen. Need to re-install everything.

Close and open the lid

Errors:

  • When not logged in on the laptop: Closing the lid of the laptop would kill the ssh connection; and laptop would not wake after re-opening the lid.
  • When logged in on the laptop: Closing and reopening the lid of the laptop gives color noise

This solved both the ssh connection and made sure the screen woke up again after re-opening: https://askubuntu.com/questions/1059705/keep-a-laptop-running-with-closed-lid

Closing the lid for a long time when logging still gives color noise. Apparently it is an issue with the open source driver. There is however a NVIDIA proprietary driver. More about that later.

Memory upgrade

My old MacBook Pro (mid 2010) had 2x2GB RAM installed.

Maximum capacity is 2x4GB.

It is easy to upgrade, following this tutorial.

Do the Samba

SMB is the service that sets your Linux box up as a file server.

Quite a bit seems to have changed in Windows security settings and there are quite a lot of outdated howto ages out there.

I found one tutorial that works… almost.

  • Be careful that your Samba user is a Linux user as well, like so.
  • Testing the share locally, the workgroup is not recognized (“SMB1 disabled — no workgroup available”). The recommended solution does not work for me.
  • In the end I did not see my Ubuntu server in the Network tab in Windows Explorer, but I could add it as a Network drive, a bit like here. And that is good enough for now.
  • I don’t have all user rights fully configured, but considering that this is only one folder on a home network, this is not a hot issue.

Enabling SSH from outside home network

First find your public IP address:

curl https://ipinfo.io/ip

That way we know what to connect to.

At this point, SSH from phone on 4G with public IP address does not work, whereas it works over wifi using internal IP address.

Next we need to adjust the firewall, following the relevant part of this tutorial.

But that is still not enough:

To find the right port on the right device behind the Ziggo Connect Box, we need to allow port forwarding.

Out-of-the-box, Ziggo has disabled port forwarding. They need to push an update of the firmware in order to enable this. You can ask via twitter @ZiggoSupport, chat or phone 0900-1884. And they fix it the same day (in my case).

Once that is set-up (Check if port forwarding show under “Geavanceerd > Beveiliging” in het Connect Box menu!) follow this tutorial.

And…. BANG. I can get into my own linux box via 5G using (JuiceSSH on my phone).

By the way, after validating that it worked I have upped firewall protection so that I can only access the server from inside my home network.

Get ssh working

Installed JuiceSSH client on Samsung phone. Following the reco from here.

On Ubuntu box needed to install openssh-server:

sudo apt-get install openssh-server

And net-tools also did not come pre-installed:

sudo apt install net-tools

Once that is in place, check the IP address like so:

ifconfig

On the Windos laptop I already had Putty installed.

Just to be sure, I added some security measures, following this guide.

The secret key I generated on the client (windows laptop) using Puttygen as explained here https://www.u.tsukuba.ac.jp/en-puttygen-keypair/.

Next step will be to configure the firewall for external access.