Putting MySQL database file on external drive

Currently hosting websites on a Raspberry Pi 4, with all web data already on external SSD drive. But then why not put the database files also on the SSD? Since there’s no point to have fast SSD if DB query still capped by crappy SD card read/write speed.

I’d create a custom file /etc/mysql/mariadb.conf.d/99-local.cnf with your changes in it. That would override the original file without having to change it or worrying about it getting overwritten during a software upgrade.

# This is /etc/mysql/mariadb.conf.d/99-local.cnf
[mysqld]
datadir = /ssd/mysql

The external SSD drive must be mounted before the mariadb server starts, otherwise it will fail. You can verify the changes by using mysql command SHOW VARIABLES LIKE '%datadir%';

[email protected]:~# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 247
Server version: 10.5.10-MariaDB-0ubuntu0.21.04.1 Ubuntu 21.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE '%datadir%';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /ssd/mysql/ |
+---------------+-------------+
1 row in set (0.004 sec)

MariaDB [(none)]>

FTP user isolation on Windows Server 2019

Run these commands on PowerShell with Admin Privilege;

# add FTP site
# -Name [any name you like]
# -IPAddress [listening IP address] (below is 0.0.0.0 (all))
# -Port [listening port]
PS C:\Users\Administrator> New-WebFtpSite -Name "FTPRoot" -IPAddress "*" -Port 21 

Name             ID   State      Physical Path                  Bindings
----             --   -----      -------------                  --------
FTPRoot          2    Started                                   ftp *:21:

# set physical folder that is used for FTP site
# example below, create a [FTPSite01] folder under the [C:\inetpub\ftproot] that is created by default and set it
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name physicalPath -Value 'C:\inetpub\ftproot' 

# set SSL/TLS setting (example below is allowing No SSL)
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name ftpServer.security.ssl.controlChannelPolicy -Value "SslAllow" 
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name ftpServer.security.ssl.dataChannelPolicy -Value "SslAllow" 

# set basic authentication
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name ftpServer.security.authentication.basicAuthentication.enabled -Value $true 

# set read and write authority to all local users
PS C:\Users\Administrator> Add-WebConfiguration "/system.ftpServer/security/authorization" -Location FTPRoot -PSPath IIS:\ -Value @{accessType="Allow";users="*";permissions="Read,Write"} 

# set user isolation
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name ftpServer.userIsolation.mode -Value "IsolateRootDirectoryOnly" 

# set external IP address (the one client computers can connect - completely optional, if there is no host firewall on your server)
PS C:\Users\Administrator> Set-ItemProperty "IIS:\Sites\FTPRoot" -Name ftpServer.firewallSupport.externalIp4Address -Value "10.0.0.101" 

# create the [LocalUser] folder under the Path you set as physical path of FTP site (it is needed on this setting)
# if Domain users, create [(FTP root)\(%UserDomain%)]
PS C:\Users\Administrator> mkdir C:\inetpub\ftproot\LocalUser 

    Directory: C:\inetpub\ftproot


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----         9/5/2019  10:19 PM                LocalUser

# restart FTP site
PS C:\Users\Administrator> Restart-WebItem -PSPath 'IIS:\Sites\FTPRoot' 

# create folders for each local user that each folder name is the same with thier username
# naming rule ⇒ [(FTP root)\LocalUser\(Username)] (example below is for [ariw] user)
PS C:\Users\Administrator> mkdir C:\inetpub\ftproot\LocalUser\ariw 
PS C:\Users\Administrator> icacls "C:\inetpub\ftproot\LocalUser\ariw" /grant "ariw:(OI)(CI)(F)" 
processed file: C:\inetpub\ftproot\LocalUser\ariw
Successfully processed 1 files; Failed processing 0 files

CloudFlare dynamic DNS update

#!/usr/bin/env bash
set -o errexit
set -o nounset
set -o pipefail

# Automatically update your CloudFlare DNS record to your dynamic IP address
# Can retrieve cloudflare Domain id and list zones, because, lazy

# Credits: https://github.com/yulewang/cloudflare-api-v4-ddns
# curl https://raw.githubusercontent.com/yulewang/cloudflare-api-v4-ddns/master/cf-v4-ddns.sh > /usr/local/bin/cf-ddns.sh && chmod +x /usr/local/bin/cf-ddns.sh
# run `crontab -e` and add next line:
# */1 * * * * /usr/local/bin/cf-ddns.sh >/dev/null 2>&1
# or if you need log:
# */1 * * * * /usr/local/bin/cf-ddns.sh >> /var/log/cf-ddns.log 2>&1


# Usage:
# cf-ddns.sh -k cloudflare-api-key \
#            -u [email protected] \
#            -h host.example.com \     # fqdn of the record you want to update
#            -z example.com \          # will show you all zones if forgot, but you need this
#            -t A|AAAA                 # specify ipv4/ipv6, default: ipv4

# Optional flags:
#            -f false|true \           # force dns update, disregard local stored ip

# default config

# API key, see https://www.cloudflare.com/a/account/my-account,
# incorrect api-key results in E_UNAUTH error
CFKEY=7d47d11f9your-API-keyee32f3c59d612

# Username, eg: [email protected]
[email protected]

# Zone name, eg: example.com
CFZONE_NAME=domain.com

# Hostname to update, eg: homeserver.example.com
CFRECORD_NAME=sub.domain.com

# Record type, A(IPv4)|AAAA(IPv6), default IPv4
CFRECORD_TYPE=AAAA

# Cloudflare TTL for record, between 120 and 86400 seconds
CFTTL=120

# Ignore local file, update ip anyway
FORCE=false

WANIPSITE="http://ipv4.icanhazip.com"

# Site to retrieve WAN ip, other examples are: bot.whatismyipaddress.com, https://api.ipify.org/ ...
if [ "$CFRECORD_TYPE" = "A" ]; then
  :
elif [ "$CFRECORD_TYPE" = "AAAA" ]; then
  WANIPSITE="http://ipv6.icanhazip.com"
else
  echo "$CFRECORD_TYPE specified is invalid, CFRECORD_TYPE can only be A(for IPv4)|AAAA(for IPv6)"
  exit 2
fi

# get parameter
while getopts k:u:h:z:t:f: opts; do
  case ${opts} in
    k) CFKEY=${OPTARG} ;;
    u) CFUSER=${OPTARG} ;;
    h) CFRECORD_NAME=${OPTARG} ;;
    z) CFZONE_NAME=${OPTARG} ;;
    t) CFRECORD_TYPE=${OPTARG} ;;
    f) FORCE=${OPTARG} ;;
  esac
done

# If required settings are missing just exit
if [ "$CFKEY" = "" ]; then
  echo "Missing api-key, get at: https://www.cloudflare.com/a/account/my-account"
  echo "and save in ${0} or using the -k flag"
  exit 2
fi
if [ "$CFUSER" = "" ]; then
  echo "Missing username, probably your email-address"
  echo "and save in ${0} or using the -u flag"
  exit 2
fi
if [ "$CFRECORD_NAME" = "" ]; then 
  echo "Missing hostname, what host do you want to update?"
  echo "save in ${0} or using the -h flag"
  exit 2
fi

# If the hostname is not a FQDN
if [ "$CFRECORD_NAME" != "$CFZONE_NAME" ] && ! [ -z "${CFRECORD_NAME##*$CFZONE_NAME}" ]; then
  CFRECORD_NAME="$CFRECORD_NAME.$CFZONE_NAME"
  echo " => Hostname is not a FQDN, assuming $CFRECORD_NAME"
fi

# Get current and old WAN ip
WAN_IP=`curl -s ${WANIPSITE}`
WAN_IP_FILE=$HOME/.cf-wan_ip_$CFRECORD_NAME.txt
if [ -f $WAN_IP_FILE ]; then
  OLD_WAN_IP=`cat $WAN_IP_FILE`
else
  echo "No file, need IP"
  OLD_WAN_IP=""
fi

# If WAN IP is unchanged an not -f flag, exit here
if [ "$WAN_IP" = "$OLD_WAN_IP" ] && [ "$FORCE" = false ]; then
  echo "WAN IP Unchanged, to update anyway use flag -f true"
  exit 0
fi

# Get zone_identifier & record_identifier
ID_FILE=$HOME/.cf-id_$CFRECORD_NAME.txt
if [ -f $ID_FILE ] && [ $(wc -l $ID_FILE | cut -d " " -f 1) == 4 ] \
  && [ "$(sed -n '3,1p' "$ID_FILE")" == "$CFZONE_NAME" ] \
  && [ "$(sed -n '4,1p' "$ID_FILE")" == "$CFRECORD_NAME" ]; then
    CFZONE_ID=$(sed -n '1,1p' "$ID_FILE")
    CFRECORD_ID=$(sed -n '2,1p' "$ID_FILE")
else
    echo "Updating zone_identifier & record_identifier"
    CFZONE_ID=$(curl -s -X GET "https://api.cloudflare.com/client/v4/zones?name=$CFZONE_NAME" -H "X-Auth-Email: $CFUSER" -H "X-Auth-Key: $CFKEY" -H "Content-Type: application/json" | grep -Po '(?<="id":")[^"]*' | head -1 )
    CFRECORD_ID=$(curl -s -X GET "https://api.cloudflare.com/client/v4/zones/$CFZONE_ID/dns_records?name=$CFRECORD_NAME" -H "X-Auth-Email: $CFUSER" -H "X-Auth-Key: $CFKEY" -H "Content-Type: application/json"  | grep -Po '(?<="id":")[^"]*' | head -1 )
    echo "$CFZONE_ID" > $ID_FILE
    echo "$CFRECORD_ID" >> $ID_FILE
    echo "$CFZONE_NAME" >> $ID_FILE
    echo "$CFRECORD_NAME" >> $ID_FILE
fi

# If WAN is changed, update cloudflare
echo "Updating DNS to $WAN_IP"

RESPONSE=$(curl -s -X PUT "https://api.cloudflare.com/client/v4/zones/$CFZONE_ID/dns_records/$CFRECORD_ID" \
  -H "X-Auth-Email: $CFUSER" \
  -H "X-Auth-Key: $CFKEY" \
  -H "Content-Type: application/json" \
  --data "{\"id\":\"$CFZONE_ID\",\"type\":\"$CFRECORD_TYPE\",\"name\":\"$CFRECORD_NAME\",\"content\":\"$WAN_IP\", \"ttl\":$CFTTL}")

if [ "$RESPONSE" != "${RESPONSE%success*}" ] && [ "$(echo $RESPONSE | grep "\"success\":true")" != "" ]; then
  echo "Updated succesfuly!"
  echo $WAN_IP > $WAN_IP_FILE
  exit
else
  echo 'Something went wrong :('
  echo "Response: $RESPONSE"
  exit 1
fi

Host WordPress with Caddy web server

# Add repository and install through APT
apt install -y debian-keyring debian-archive-keyring apt-transport-https
curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/gpg.key' | apt-key add -
curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/debian.deb.txt' | tee /etc/apt/sources.list.d/caddy-stable.list
apt update
apt install caddy php-fpm php-mysql php-curl php-gd php-mbstring php-common php-xml php-xmlrpc -y
# Install MariaDB
apt install mariadb-server -y
mysql_secure_installation 
# Create database
mysql -uroot -p
create database ariw;
grant all on ariw.* to 'ariw'@'localhost' identified by 'LuGueEnd!';
flush privileges;
# Download WordPress
mkdir /ssd/www/ariw.net && cd /ssd/www/ariw.net && wget wordpress.org/latest.zip && unzip latest.zip && mv wordpress/* ./ && rmdir wordpress
# Create Caddyfile (/etc/caddy/Caddyfile)
ariw.net, www.ariw.net {
        tls {
        dns cloudflare yOurCloudFlareTokenHere0D7Fkei-suZR4P7y5kQ0
        }

        root * /ssd/www/ariw.net
        php_fastcgi unix//run/php/php7.4-fpm.sock
        file_server

        @disallowed {
        path /xmlrpc.php
        path *.sql
        path /wp-content/uploads/*.php
        }

        rewrite @disallowed '/index.php'

}