ProFTPD is a highly configurable FTP server that supports virtual hosting, allowing multiple domains and users to be hosted on a single server. By integrating with MySQL, you can manage user accounts and their associated quotas efficiently. This guide will show you how to set up virtual hosting with ProFTPD and MySQL, including user quotas, on your Linux server. For optimal performance and control, consider hosting your FTP server on a WindowsVPS, where a VPS server provides the resources needed for scalability.

Step 1: Install ProFTPD and MySQL

First, install ProFTPD and MySQL on your VPS server. You can install both packages using the following commands:

sudo apt update
sudo apt install proftpd proftpd-mod-mysql mariadb-server mariadb-client -y

During the installation of ProFTPD, you may be prompted to choose between standalone or inetd mode. Select standalone mode.

Step 2: Secure MySQL

After installing MySQL, it’s important to secure the installation by running the following command:

sudo mysql_secure_installation

Follow the prompts to set a root password and remove insecure defaults.

Step 3: Create the ProFTPD Database and User Tables

Log in to the MySQL shell to create a database and user for ProFTPD:

sudo mysql -u root -p

Create a new database called proftpd and add the necessary tables:

CREATE DATABASE proftpd;
USE proftpd;

CREATE TABLE ftpusers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userid VARCHAR(30) NOT NULL,
  passwd VARCHAR(128) NOT NULL,
  uid INT NOT NULL,
  gid INT NOT NULL,
  homedir VARCHAR(255) NOT NULL,
  shell VARCHAR(255) NOT NULL,
  quota_size INT NOT NULL DEFAULT 0
);

CREATE TABLE ftpquotatallies (
  name VARCHAR(30) NOT NULL,
  quota_type ENUM('user','group') NOT NULL,
  bytes_in_used BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_out_used BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_in_avail BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_out_avail BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (name, quota_type)
);

CREATE TABLE ftpgroup (
  groupname VARCHAR(30) NOT NULL,
  gid INT NOT NULL,
  members VARCHAR(255) NOT NULL,
  PRIMARY KEY (groupname)
);

GRANT ALL PRIVILEGES ON proftpd.* TO 'ftpuser'@'localhost' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
EXIT;

Make sure to replace your_password with a secure password.

Step 4: Configure ProFTPD with MySQL

Edit the ProFTPD configuration file to integrate MySQL. Open the configuration file:

sudo nano /etc/proftpd/proftpd.conf

Uncomment or add the following lines to enable MySQL authentication:

Include /etc/proftpd/sql.conf
DefaultRoot ~
RequireValidShell off

Now, create the sql.conf file to define the MySQL connection settings:

sudo nano /etc/proftpd/sql.conf

Add the following content, replacing your_password with the password you set earlier:

    SQLBackend              mysql
    SQLEngine               on
    SQLAuthenticate         users* groups*

    SQLConnectInfo          proftpd@localhost ftpuser your_password
    SQLUserInfo             ftpusers userid passwd uid gid homedir shell
    SQLGroupInfo            ftpgroup groupname gid members

Step 5: Enable Quotas

To enforce quotas, you need to enable the mod_quotatab and mod_quotatab_sql modules. Open the ProFTPD modules configuration file:

sudo nano /etc/proftpd/modules.conf

Ensure the following lines are present and uncommented:

LoadModule mod_quotatab.c
LoadModule mod_quotatab_sql.c

Now, configure quota settings in the sql.conf file:

    QuotaEngine on
    QuotaShowQuotas on



    SQLNamedQuery get-quota-limit SELECT "name, quota_type, bytes_in_avail AS quota_limit FROM ftpquotatallies WHERE name='%{0}' AND quota_type='%{1}'"
    SQLNamedQuery update-used-quota UPDATE "bytes_in_used=bytes_in_used+%{1}, bytes_out_used=bytes_out_used+%{2} WHERE name='%{0}' AND quota_type='%{3}'" ftpquotatallies
    QuotaLimitTable sql:/get-quota-limit
    QuotaTallyTable sql:/update-used-quota

Step 6: Restart ProFTPD

Once everything is configured, restart ProFTPD to apply the changes:

sudo systemctl restart proftpd

Step 7: Testing ProFTPD Virtual Hosting

To test the setup, create an FTP user in the MySQL database:

INSERT INTO ftpusers (userid, passwd, uid, gid, homedir, shell) 
VALUES ('testuser', MD5('password'), 1001, 1001, '/home/testuser', '/sbin/nologin');

Then, create the home directory for the user:

sudo mkdir -p /home/testuser
sudo chown -R 1001:1001 /home/testuser

You can now log in as the FTP user to test the virtual hosting setup.

Step 8: Optimize Your VPS Server for ProFTPD

Running ProFTPD with MySQL on a WindowsVPS ensures that your FTP server performs efficiently by utilizing dedicated resources. A VPS server provides flexibility and scalability, allowing you to manage large numbers of users and domains while maintaining high performance and reliability.

Conclusion

By following this guide, you have successfully set up virtual hosting with ProFTPD and MySQL, including quotas. Hosting your ProFTPD server on a WindowsVPS ensures optimal performance and scalability, giving you the flexibility to manage users and their file quotas effectively.

For more information about VPS hosting and optimizing your ProFTPD setup, visit WindowsVPS today.