iKVM
Secure Login
Network Cards
Hard drives
SSHFS
Basic DNS
Website DNS
Certbot SSL
Apache and PHP
Maria DB
Mail Server Basics
Mail Server SSL
Mail Server Filters
Checklist
Backup
Maria DB
Note: This article series covers configuring Debian 12 for hosting multiple domains and web sites on a single dedicated server. As such, some strategies may be inappropriate for your environment. Sockets for example are appropriate for communication between services hosted on the same machine but not suited to a set up with distributed services (where you'd use ports). Please consult the overview for more information.
I used to use MySQL for my website databases before Oracle stopped giving it away for free.
Luckily, someone forked MySQL and created a drop in replacement called Maria DB and it's pretty easy to set up securely.
This article covers using the root database user but I recommend you create less powerful users for your web sites so they can't use the login details maliciously.
I'll let you Google how you can do that but setting up the database is fairly simple.
On your server, first update your package manager:
sudo apt update
Then install Maria DB:
sudo apt install mariadb
Run the secure installation script, keep the secure socket, give the root user a password and delete the test database. The secure socket permission is very useful because it means that if you run maria db (mysql) as root then it assumes you're the root user and doesn't ask you for a password, useful for running backups because it means we don't have to store the password on the disk anywhere:
mariadb-secure-installation
By default, Maria DB runs on a local port (3306) on the local server (127.0.0.1) which means you have to be logged in on the local server to access the database server. We're going to leverage that. I like to use Mysql Workbench as the GUI for my databases but it crashes on my Ubuntu when I try to connect via SSH over TCP/IP so I'm going to show you a secure workaround (which will also work for any extra users you want to add!).
We're going to use SSH to forward that local port (127.0.0.1:3306) to our client computer, then we'll use a standard TCP/IP connection in our Mysql Workbench to access that new local port.
Add a new user to your server
Log in to your server and add a new user, you can pick a name if you don't like the one I've chosen:sudo adduser mariadb
Create a new SSH Certificate pair on your client
Name your new certificate pair mariadb - Use the path ~/.ssh/mariadb:ssh-keygen
Add the public certificate to your new user (.ssh/authorized_keys file) and set the permissions.
First, get the new public key and copy it:
cat ~/.ssh/mariadb.pub
Then log on to your server and put your new key into the authorized_keys file for your new user (mariadb), you might need to create .ssh and set the correct permissions:
sudo mkdir -p /home/mariadb/.ssh
sudo echo "PASTE_PUBLIC_KEY_HERE" >> /home/mariadb/.ssh/authorized_keys
sudo chown -R mariadb:mariadb /home/mariadb/.ssh
sudo chmod 700 /home/mariadb/.ssh
sudo chmod 600 /home/mariadb/.ssh/authorized_keys
Forward the port and set up a script to do this automatically
First of all, create the script:nano ~/mariadb.sh
We want to forward the local port (127.0.0.1:3306) to 3307:
#!/bin/bash ssh -L 3307:127.0.0.1:3306 -p 2222 -i ~/.ssh/mariadb mariadb@74.201.177.82Save the file and don't forget to add the execute permission:
chmod +x ~/mariadb.sh
Add an alias so you can quickly get the script from the command line. Add it to .bash_aliases on Ubuntu or .bashrc if you're using another flavour of Linux:
nano .bash_aliases
alias db="~/mariadb.sh"Now you'll need to close the terminal and create a new session to pick up the new alias. So when you want to do a bit of database admin, type db in the terminal to connect the port and then run Mysql Workbench, it'll log in to your server so you'll need to keep that terminal window open whilst you're working.
In Mysql Workbench, add a new connection, select TCP/IP and enter 127.0.0.1 for the address and 3307 for the port (that's the port we forwarded our server port 3306 to!) and the user and password for database access (for this example use root and the password you set up at the beginning of this article). Once you've connected it'll save your connection so you won't have to keep re-entering it!
Nice, so now you can administrate your database over an encrypted connection without advertising that port to the outside world and if you want to add more users you can either add certificates to authorized_keys in your existing mariadb user or create new users on your server and add public certificates to them instead!
So what's actually happening here?
By default, Maria DB opens local port 3306 but it can't be accessed from the outside world and is only usable if you're on the local server. What we've done is log in with SSH and forward the local 3306 port from your server to our client computer (on port 3307). Then we've set up MySQL Workbench to connect to that forwarded port (3307). As far as Maria DB is concerned someone is connecting to its local port but because it's SSH everything is encrypted!You'll also need to create Maria DB users to administrate the databases.
A note about creating users in MySQL Workbench
I had problems creating new users using the 'Users and Privileges' tool in MySQL Workbench and found I had to create the user by running a query first and then I could grant permissions as usual:CREATE USER 'myuser'@localhost IDENTIFIED BY 'mypassword';
