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)