您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

FreeBSD 下 ProFTPD + MySQL + Quota 實戰部署：虛擬用戶磁盤配額管理

編輯：MySQL 資料庫相關知識

環境基線

OS: FreeBSD 10-RELEASE
資料庫: MySQL 5.6+ (ports: databases/mysql56-server)
FTP 服務: ProFTPD 1.3.5+ (ports: ftp/proftpd-mysql，勾選 MYSQL QUOTA 模塊)
管理工具: phpMyAdmin (可選)

MySQL 安裝與初始化

# 安裝
cd /usr/ports/databases/mysql56-server && make install clean

# 創建用戶組/用戶
pw groupadd mysql -g 88
pw useradd mysql -u 88 -g 88 -d /nonexistent -s /usr/sbin/nologin

# 初始化數據目錄
mysql_install_db --user=mysql --datadir=/var/db/mysql
chown -R mysql:mysql /var/db/mysql

# 啟動腳本
cp /usr/local/share/mysql/mysql.server /usr/local/etc/rc.d/mysql-server.sh
chmod +x /usr/local/etc/rc.d/mysql-server.sh

# 配置文件
cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
# 編輯 my.cnf：bind-address=127.0.0.1, innodb_buffer_pool_size=128M 等

# 啟動並加密
/usr/local/etc/rc.d/mysql-server.sh start
mysql_secure_installation

ProFTPD 編譯安裝

cd /usr/ports/ftp/proftpd-mysql
make config  # 勾選 MYSQL, QUOTA, (可選) OPENSSL, WRAP
make install clean

數據庫 Schema 設計

CREATE DATABASE ftpdb DEFAULT CHARSET utf8mb4;
USE ftpdb;

-- 用戶表
CREATE TABLE ftp_users (
  userid     VARCHAR(32) NOT NULL PRIMARY KEY,
  passwd     VARCHAR(64) NOT NULL,          -- 存加密雜湊
  uid        INT UNSIGNED NOT NULL DEFAULT 2001,
  gid        INT UNSIGNED NOT NULL DEFAULT 2001,
  homedir    VARCHAR(255) NOT NULL,
  shell      VARCHAR(16) NOT NULL DEFAULT '/sbin/nologin',
  enable     TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_user (userid)
) ENGINE=InnoDB;

-- 組表
CREATE TABLE ftp_groups (
  groupname  VARCHAR(32) NOT NULL PRIMARY KEY,
  gid        INT UNSIGNED NOT NULL,
  members    TEXT NOT NULL
) ENGINE=InnoDB;

-- 配額限制表
CREATE TABLE quota_limits (
  name          VARCHAR(32) NOT NULL,
  quota_type    ENUM('user','group','class','all') NOT NULL DEFAULT 'user',
  per_session   ENUM('true','false') NOT NULL DEFAULT 'false',
  limit_type    ENUM('soft','hard') NOT NULL DEFAULT 'hard',
  bytes_in_avail  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_out_avail BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_xfer_avail BIGINT UNSIGNED NOT NULL DEFAULT 0,
  files_in_avail  INT UNSIGNED NOT NULL DEFAULT 0,
  files_out_avail INT UNSIGNED NOT NULL DEFAULT 0,
  files_xfer_avail INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (name, quota_type)
) ENGINE=InnoDB;

-- 配額使用表（ProFTPD 自動維護）
CREATE TABLE quota_tallies (
  name          VARCHAR(32) NOT NULL,
  quota_type    ENUM('user','group','class','all') NOT NULL DEFAULT 'user',
  bytes_in_used  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_out_used BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_xfer_used BIGINT UNSIGNED NOT NULL DEFAULT 0,
  files_in_used  INT UNSIGNED NOT NULL DEFAULT 0,
  files_out_used INT UNSIGNED NOT NULL DEFAULT 0,
  files_xfer_used INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (name, quota_type)
) ENGINE=InnoDB;

-- 測試數據
INSERT INTO ftp_groups VALUES ('ftpusers', 2001, 'ftpuser');
INSERT INTO ftp_users VALUES ('testuser', MD5('testpass'), 2001, 2001, '/var/ftp/testuser', '/sbin/nologin', 1);
INSERT INTO quota_limits VALUES ('testuser', 'user', 'false', 'hard', 1073741824, 0, 2147483648, 1000, 0, 0); -- 1GB 磁盤，2GB 流量，1000 文件

ProFTPD 核心配置

# /usr/local/etc/proftpd.conf
ServerName          "My FTP Server"
ServerType          standalone
DefaultServer       on
Port                21
Umask               022
MaxInstances        20
MaxClients          50
MaxClientsPerHost   5
TimeoutIdle         300
TimeoutNoTransfer   600
TimeoutStalled      3600
DisplayLogin        /etc/motd
DisplayChdir        .message
User                ftpuser
Group               ftpgroup
DefaultRoot         ~
RequireValidShell   off
UseReverseDNS       off
IdentLookups        off

# 日志
SystemLog           /var/log/proftpd/system.log
TransferLog         /var/log/proftpd/xferlog

# MySQL 認證模塊

  SQLBackend           mysql
  SQLAuthTypes         OpenSSL Crypt
  SQLConnectInfo       ftpdb@localhost ftpuser ftp_password
  SQLUserInfo          ftp_users userid passwd uid gid homedir shell
  SQLGroupInfo         ftp_groups groupname gid members
  SQLUserWhereClause   "enable=1"
  SQLLog               PASS counter_login
  SQLNamedQuery        counter_login UPDATE ftp_users SET login_count=login_count+1, last_login=NOW() WHERE userid='%u'


# 配額模塊

  QuotaEngine          on
  QuotaDirectoryTally  on
  QuotaDisplayUnits    Mb
  QuotaShowQuotas      on
  QuotaOptions         ScanOnLogin

  QuotaLimitTable      sql:/quota_limits
  QuotaTallyTable      sql:/quota_tallies


# 匿名登錄（可選）

  User                ftp
  Group               ftp
  UserAlias           anonymous ftp
  MaxClients          10
  
    DenyAll

系統用戶與目錄準備

pw groupadd ftpgroup -g 2001
pw useradd ftpuser -u 2001 -g 2001 -d /var/ftp -s /usr/sbin/nologin
mkdir -p /var/ftp/testuser
chown -R ftpuser:ftpgroup /var/ftp

啟動驗證

# 測試配置語法
proftpd -t

# 前台調試
proftpd -n -d 5

# 正式啟動
service proftpd start
# 或加入 /etc/rc.conf: proftpd_enable="YES"

# 客戶端測試
ftp localhost
# 登錄後執行
quote SITE QUOTA
# 應顯示配額信息