> For the complete documentation index, see [llms.txt](https://book.bsdcn.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.bsdcn.org/ask/flat/chapter-36-file-transfer-protocol-ftp/di-36.3-jie-proftpd-ji-yu-mysql.md).
# 36.3 ProFTPD (with MySQL)
> **Tip**
>
> Connecting to a ProFTPD server using the built-in Windows FTP client will not produce garbled text.
## Installing ProFTPD
> **Note**
>
> This section is based on MySQL 8.0. For MySQL installation and basic setup, please refer to other chapters.
Please complete the installation and configuration of MySQL 8.x on your own, and ensure that its version is consistent with the `databases/mysql8X-client` version installed by `proftpd-mod_sql_mysql`.
Install ProFTPD and its MySQL module:
```sh
# pkg install proftpd proftpd-mod_sql_mysql
```
Or install using Ports:
```sh
# cd /usr/ports/ftp/proftpd/ && make install clean
# cd /usr/ports/databases/proftpd-mod_sql_mysql/ && make install clean
```
## ProFTPD Configuration File
Edit the ProFTPD configuration file **/usr/local/etc/proftpd.conf**:
> **Tip**
>
> See the sample file at **/usr/local/etc/proftpd.conf.sample**.
```ini
ServerName "Test Ftp Server" # FTP server name
ServerType standalone # Standalone mode
DefaultServer on # Set as default server
ServerIdent on "FTP Server ready" # Server identification information
Port 21 # Listening port
Umask 022 # File creation mask
# Timeout settings
TimeoutLogin 300 # Login timeout (seconds)
TimeoutIdle 36000 # Idle timeout (seconds)
TimeoutNoTransfer 36000 # No-transfer timeout (seconds)
# Resource limits
User proftpd # Service running user
Group proftpd # Service running group
RLimitMemory 256M 256M # Memory limit
RLimitOpenFiles 1024 1024 # Open file count limit
PassivePorts 50000 60000 # Passive mode port range
# Log configuration
LogFormat default "%h %l %u %t \"%r\" %s %b" # Default log format
LogFormat auth "%v [%P] %h %t \"%r\" %s" # Authentication log format
SystemLog /var/log/proftpd/proftpd.log # System log path
TransferLog /var/log/proftpd/xfer.log # File transfer log path
ExtendedLog /var/log/proftpd/auth.log AUTH auth # Extended authentication log
# MySQL module loading
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_sql_passwd.c
# Only allow access to respective directories
DefaultRoot ~ # Restrict users to their home directory
# Allow file overwriting
AllowOverwrite on # Allow file overwrite
# Database connection
SQLConnectInfo proftpd@localhost proftpd # Database connection info (user, host, password)
# Password authentication settings
SQLAuthTypes SHA256 # Use SHA256 encrypted authentication
SQLPasswordEngine on # Enable SQL password engine
# User table mapping
SQLUserInfo users username password uid gid homedir shell # Map users table fields
SQLDefaultGID 2000 # Default GID
SQLDefaultUID 2000 # Default UID
RequireValidShell off # Do not require a valid shell
# Authentication order
AuthOrder mod_sql.c
SQLAuthenticate users # Use SQL user table for authentication
# Login statistics
SQLNamedQuery getcount SELECT "CONCAT('Total logins: ', count) FROM users WHERE username='%u'" # Query login count
SQLNamedQuery updatecount UPDATE "count=count+1 WHERE username='%u'" users # Update login count
SQLShowInfo PASS "230" "%{getcount}" # Display login statistics
SQLLog PASS updatecount # Record login statistics
# File operation logging
SQLNamedQuery log_work FREEFORM "INSERT INTO worklog (user_name, file_and_path, bytes, send_time, client_ip, client_name, client_command) VALUES ('%u', '%f', %b, NULLIF('%T', ''), '%a', '%h', '%m')" # File operation log record
SQLLog RETR,STOR,DELE log_work # Log RETR, STOR, DELE operations to the worklog table
```
Create the log directory:
```sh
# mkdir /var/log/proftpd
```
> **Warning**
>
> If the directory is not manually created, the system will display `proftpd[3435]: warning: handling possibly truncated configuration data at line 65 of '/usr/local/etc/proftpd.conf'`.
> **Tip**
>
> The ProFTPD configuration file **/usr/local/etc/proftpd.conf** has a syntax check command: `proftpd -t -d5`.
In this configuration, the server uses port 21 for active mode and ports 50000-60000 for passive mode. Ensure that the firewall allows these ports.
For the PF firewall, use the following rule:
Allow inbound TCP traffic from the external interface `$ext_if` (replace with the actual network interface) to local ports 21 and 50000-60000:
```ini
pass in quick on $ext_if proto tcp from any to $ext_if port { 21, 50000:60000 }
```
## Related File Structure
```sh
/usr/local/etc/
├── proftpd.conf # ProFTPD main configuration file
└── proftpd.conf.sample # ProFTPD sample configuration file
```
## Creating Users
For security reasons, ProFTPD should be run as a non-root user. Add a new user:
```sh
# adduser
Username: proftpd # Username
Full name: FTP User # User full name
Uid (Leave empty for default):
Login group [proftpd]:
Login group is proftpd. Invite proftpd into other groups? []:
Login class [default]:
Shell (sh csh tcsh bash nologin) [sh]: nologin # Do not allow system login
Home directory [/home/proftpd]:
Home directory permissions (Leave empty for default):
Enable ZFS encryption? (yes/no) [no]:
Use password-based authentication? [yes]: no
Lock out the account after creation? [no]:
Username : proftpd
Password :
Full Name : FTP User
Uid : 1002
ZFS dataset : zroot/home/proftpd
Class :
Groups : proftpd
Home : /home/proftpd
Home Mode :
Shell : /usr/sbin/nologin
Locked : no
adduser: INFO: Successfully created ZFS dataset (zroot/home/proftpd).
adduser: INFO: Successfully added (proftpd) to the user database.
Add another user? (yes/no) [no]:
Goodbye!
```
## MySQL Database Setup
Create the MySQL database and user:
```sql
CREATE DATABASE `proftpd` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- Create proftpd database using utf8mb4 character set and general collation
```
Create the database user and password:
```sql
CREATE USER 'proftpd'@'localhost' IDENTIFIED BY ''; -- Create MySQL user proftpd, only allowed to connect from localhost, replace with actual password
GRANT SELECT, INSERT, UPDATE, DELETE ON proftpd.* TO 'proftpd'@'localhost'; -- Grant proftpd user CRUD permissions on the proftpd database
FLUSH PRIVILEGES; -- Flush privileges to make the configuration take effect immediately
```
Create data tables:
```sql
USE proftpd; -- Select the proftpd database
-- Create users table
DROP TABLE IF EXISTS users;
CREATE TABLE `users` (
`username` VARCHAR(30) NOT NULL, -- Username
`descr` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, -- User description
`password` VARCHAR(64) NOT NULL, -- User password (SHA-256 encrypted)
`uid` INT(11) DEFAULT NULL, -- User UID
`gid` INT(11) DEFAULT NULL, -- User GID
`homedir` VARCHAR(255) DEFAULT NULL, -- User home directory
`shell` VARCHAR(255) DEFAULT NULL, -- User shell
`count` INT(11) NOT NULL DEFAULT 0, -- Login count statistics
PRIMARY KEY (`username`) -- Primary key is username
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Create worklog table
DROP TABLE IF EXISTS worklog;
CREATE TABLE worklog (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- Log ID, auto-increment primary key
date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0), -- Operation time
user_name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- Username
file_and_path VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- File and path
bytes BIGINT UNSIGNED DEFAULT NULL, -- File size
send_time VARCHAR(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- File transfer time
client_ip VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- Client IP
client_name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- Client name
client_command VARCHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, -- Client command
PRIMARY KEY (id),
UNIQUE INDEX id (id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
-- Insert test user
INSERT INTO `proftpd`.`users` (`username`, `descr`, `password`, `uid`, `gid`, `homedir`, `shell`, `count`)
VALUES ('test', 'Test user', SHA2('FTPpassword_here', 256), '1002', '1002', '/home/www/ftp', NULL, '0'); -- Add test user record
```
The following has been created:
| Item | Value |
| ------------------ | ------------------ |
| FTP username | `test` |
| FTP login password | `FTPpassword_here` |
| UID | `1002` |
| GID | `1002` |
> **Warning**
>
> The UID and GID above must match those of the proftpd user, otherwise there will only be read permission without write permission.
>
> You can obtain the UID, GID, and group information for the proftpd user using:
>
> ```sh
> # id proftpd
> uid=1002(proftpd) gid=1002(proftpd) groups=1002(proftpd)
> ```
Test the database connection:
```sh
# mysql -u proftpd -p -h localhost proftpd # Connect to the proftpd database on localhost using the proftpd user, the system prompts for a password
proftpd@localhost [proftpd]> show databases; -- Display the list of all databases on the MySQL server
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| proftpd |
+--------------------+
3 rows in set (0.00 sec)
proftpd@localhost [proftpd]> use proftpd; -- Select the proftpd database as the current operating database
Database changed
proftpd@localhost [proftpd]> select * from users; -- Query all records in the users table
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
| username | descr | password | uid | gid | homedir | shell | count |
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
| test | Test user | d1d6930fda5f964acba51ec4c35d0ddb3b36d25bfef59f1120abd2e4f9f140d9 | 1002 | 1002 | /home/www/ftp | NULL | 0 |
+----------+-----------+------------------------------------------------------------------+------+------+---------------+-------+-------+
1 row in set (0.00 sec)
```
Create the directory and test the FTP user:
```sh
# mkdir -p /home/www/ftp # Create the FTP user's home directory and subdirectories
# chown -R proftpd:proftpd /home/www/ftp # Set directory owner and group to proftpd
# chmod -R 775 /home/www/ftp # Set directory permissions to 775, allowing group users to read and write
```
## Service Operations
After configuration is complete, start and manage the ProFTPD service:
```sh
# service proftpd enable # Set ProFTPD service to start on boot
# service proftpd start # Start the ProFTPD service
# service proftpd stop # Stop the ProFTPD service
# service proftpd restart # Restart the ProFTPD service
```
Log in to FTP with the username `test` and the password `FTPpassword_here`.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://book.bsdcn.org/ask/flat/chapter-36-file-transfer-protocol-ftp/di-36.3-jie-proftpd-ji-yu-mysql.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.