Category Archives: Information Technology

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