How To Set Up ProFTPD with a MySQL Backend on Ubuntu

Welcome to the Greenhost.cloud blog! Today, we’ll guide you through the process of setting up ProFTPD (an FTP server) with a MySQL backend on Ubuntu. ProFTPD is a versatile FTP server that supports a variety of features, and integrating it with MySQL allows for dynamic user management, making it ideal for hosting environments.

Prerequisites

Before we begin, ensure that you have the following:

• An Ubuntu server (the steps are applicable for Ubuntu 18.04 and later).
• Root or sudo access to the server.
• Basic knowledge of terminal commands.

Step 1: Update Your System

First, log in to your server and ensure your package lists are up-to-date.

sudo apt update
sudo apt upgrade -y

Step 2: Install Required Packages

Next, you need to install ProFTPD along with the MySQL backend module.

sudo apt install proftpd proftpd-mysql -y

During installation, you may be prompted to choose a standalone or inetd mode. Select “standalone” as it is more commonly used for FTP servers.

Step 3: Install MySQL Server

If MySQL is not already installed on your server, install it using the following command:

sudo apt install mysql-server -y

Securing your MySQL installation is highly recommended:

sudo mysql_secure_installation

This command will help you set a root password and secure your MySQL server.

Step 4: Create the MySQL Database and User Table

Log in to the MySQL command line interface:

sudo mysql -u root -p

Create a new database for ProFTPD:

CREATE DATABASE proftpd;

Next, create a user for managing the FTP accounts:

CREATE USER 'ftpuser'@'localhost' IDENTIFIED BY 'your_password_here';
GRANT ALL PRIVILEGES ON proftpd.* TO 'ftpuser'@'localhost';
FLUSH PRIVILEGES;

Replace 'your_password_here' with a strong password.

Now, create the user table to store FTP users:

USE proftpd;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(32) NOT NULL UNIQUE,
    password VARCHAR(64) NOT NULL,
    homedir VARCHAR(255) NOT NULL,
    shell VARCHAR(32) NOT NULL
);

Exit the MySQL prompt:

EXIT;

Step 5: Configure ProFTPD to Use MySQL

Open the ProFTPD configuration file:

sudo nano /etc/proftpd/proftpd.conf

Add or modify the following sections to enable MySQL support:

<IfModule mod_sql.c>
    SQLBackend mysql
    SQLDriver mysql
    SQLLogFile /var/log/proftpd/sql.log
    SQLConnectInfo proftpd@localhost ftpuser your_password_here

    SQLUserTable users
    SQLGroupTable users
</IfModule>

Replace your_password_here with the password you used for the ftpuser.

Step 6: Setting Up User Accounts

To add users, you need to create records in the MySQL users table. Here’s an example command to create a new FTP user:

INSERT INTO users (username, password, homedir, shell) VALUES ('ftpuser1', PASSWORD('ftp_password1'), '/home/ftpuser1', '/usr/sbin/nologin');

The PASSWORD() function is used to hash the password appropriately.

Repeat this command for additional users, changing the values as necessary.

Step 7: Restart ProFTPD

Once your configuration changes are complete, restart the ProFTPD service:

sudo systemctl restart proftpd

You can also enable it to start on boot:

sudo systemctl enable proftpd

Step 8: Configure Firewall (if applicable)

If you have a firewall running, ensure that it allows FTP traffic:

sudo ufw allow 21/tcp
sudo ufw allow 20/tcp

Step 9: Testing the FTP Server

You can test your new FTP server with an FTP client like FileZilla or the command-line FTP client.

Example using command line:

ftp your_server_ip

Log in with one of the FTP user accounts you created. You should be able to upload and download files based on the permissions set.

Conclusion

Congratulations! You’ve successfully set up ProFTPD with a MySQL backend on Ubuntu. This configuration allows for dynamic user management, making it an excellent choice for those requiring robust FTP services.

For more tutorials and tech tips, stay tuned to the Greenhost.cloud blog!