Proftp+Mysql5.0+mod_sql+mod_quotatab

 

安装MySQL:

需要下载MySQL5.0(从我的安装的经验来看mod_sql 不支持5.5)

http://dev.mysql.com/

安装Shared components（这是首先要做的，否则在安装Server时，会告诉你缺少依赖）

#rpm –ivh MySQL-shared-5.0.95-1.glibc23.i386.rpm

安装MySQL Server

#rpm -ivh MySQL-server-5.0.95-1.glibc23.i386.rpm

安装Client Utilities

#rpm -ivh MySQL-client-5.0.95-1.glibc23.i386.rpm

如果安装失败可以使用rpm –e <名字>.rpm 来卸载

创建数据库

#/usr/bin/mysql_install_db --user=mysql (MySQL安装完毕，会自动的创建一个数据库。如果自动创建失败，可以使用)

修改密码

# /usr/bin/mysqladmin -u root -p password 123456
回车出现 Enter password: 最开始密码默认为空 继续回车即可

数据库状态

Service mysql status

没有启动 MySQL is not running                                       [失败]

正常启动 MySQL running (29095)                                      [确定]

启动异常 MySQL is not running but lock exists                    [失败]   

        (这个问题，可能是之前安装的MySQL没有完全卸载，

         rm /var/lock/subsys/mysql 就可以解决)

启动失败 Starting MySQL.Manager of pid-file quit without updating fi[失败]

        (杀掉已经启动的进程：

[root@localhost mysql]# ps -A|grep mysql
8016 pts/2 00:00:00 mysqld_safe
8037 pts/2 00:00:00 mysqld 

Kill -9 8016,8037

安装Proftpd：

需要下载最新版本的Proftpd，http://www.proftpd.org/

需要编译，安装

./configure --prefix=/usr/local/proftpd

--with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql --with-includes=/usr/include/mysql --with-libraries=/usr/lib/

make

make install

 

(如果编译过程中，出现编译错误。那么就需要检查下是否是mysql的开发包没有安装或者是Mysql的版本太新，Proftp的的版本太旧导致的。我比较倒霉，这样的问题层出不穷。)

 

配置Proftpd,对/usr/local/proftpd/etc/proftpd.conf进行编辑

可以参考我的配置

########################################################################################################################################################

# This is a basic ProFTPD configuration file (rename it to

# 'proftpd.conf' for actual use.  It establishes a single server

# and a single anonymous login.  It assumes that you have a user/group

# "nobody" and "ftp" for normal operation and anon.

 

ServerName          "shannxiUmsFTPServer"   

ServerType          standalone

DefaultServer           on

 

# Port 21 is the standard FTP port.

Port                21

 

# Don't use IPv6 support by default.

UseIPv6             off

 

# Umask 022 is a good standard umask to prevent new dirs and files

# from being group and world writable.

Umask               022

 

# To prevent DoS attacks, set the maximum number of child processes

# to 30.  If you need to allow more than 30 concurrent connections

# at once, simply increase this value.  Note that this ONLY works

# in standalone mode, in inetd mode you should use an inetd server

# that allows you to limit maximum number of processes per service

# (such as xinetd).

MaxInstances            30

 

# Set the user and group under which the server will run.

User                nobody

#Group              nogroup

Group                  nobody

# To cause every FTP user to be "jailed" (chrooted) into their home

# directory, uncomment this line.

#DefaultRoot ~

 

# Normally, we want files to be overwriteable.

AllowOverwrite      on

 

# Bar use of SITE CHMOD by default

<Limit SITE_CHMOD>

  DenyAll

</Limit>

 

# A basic anonymous configuration, no upload directories.  If you do not

# want anonymous users, simply delete this entire <Anonymous> section.

#<Anonymous ~ftp>

#  User             ftp

#  Group            ftp

 

# We want clients to be able to login with "anonymous" as well as "ftp"

#  UserAlias        anonymous ftp

 

# Limit the maximum number of anonymous logins

#  MaxClients           10

 

# We want 'welcome.msg' displayed at login, and '.message' displayed

# in each newly chdired directory.

#  DisplayLogin         welcome.msg

#  DisplayChdir         .message

 

# Limit WRITE everywhere in the anonymous chroot

#  <Limit WRITE>

#    DenyAll

#  </Limit>

#</Anonymous>

 

 

# The passwords in MySQL are encrypted using CRYPT

SQLAuthTypes            Plaintext Crypt

SQLAuthenticate         users* groups*

 

# used to connect to the database

# databasename@host database_user user_password

SQLConnectInfo  proftpd@127.0.0.1 root 123456

 

#if homedir is not exsit,will create it.

CreateHome on

 

# Here we tell ProFTPd the names of the database columns in the "usertable"

# we want it to interact with. Match the names with those in the db

SQLUserInfo     ftpuser userid passwd uid gid homedir shell

 

 

# Here we tell ProFTPd the names of the database columns in the "grouptable"

# we want it to interact with. Again the names match with those in the db

SQLGroupInfo    ftpgroup groupname gid members

 

 

# set min UID and GID - otherwise these are 999 each

#SQLMinID        500

 

# Update count every time user logs in

SQLLog PASS updatecount

 

SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

 

# Update modified everytime user uploads or deletes a file

SQLLog  STOR,DELE modified

 

SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

 

SQLLogFile /usr/local/proftpd/log.txt

 

# User quotas

# ===========

 

QuotaEngine on

 

QuotaDirectoryTally on

 

QuotaDisplayUnits Mb

 

QuotaShowQuotas on

 

 

 

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"

 

 

 

SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"

 

 

 

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies

 

 

 

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies

 

 

 

QuotaLimitTable sql:/get-quota-limit

 

QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

 

 

RootLogin off

 

RequireValidShell off

########################################################################################################################################################

 

以上配置文件需要注意的是：

SQLConnectInfo  proftpd@127.0.0.1 root 123456

如果你采用host=localhost，那么你很可能会获得

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' 的错误，在SQLLOGFile中。

你可以通过mysqladmin -uroot -p variables |grep socket 来检查mysql.sock位置是否正确。如果你觉得你的配置都正确，但是。。还无法解决。那么你可以将localhost更换成127.0.0.1试试。

 

#Group              nogroup

Group                  nobody

RedHat Enterprise 5.5 没有nogroup 用户，所以使用nobody来代替。

 

 

 

 

 

#配置系统用户

# groupadd -g 5500 ftpgroup
# useradd –u 5500 –d /home/ftpuser –s /sbin/nologin -m -g ftpgroup ftpuser

配置Mysql

#创建数据库

mysql> create database proftpd

 

#添加proftpd数据库的用户权限,proftpd数据库的用户名为：proftpd，密码为：123456，它只有查询、更新、插入的权限。 配置为@localhost，那么就只能通过本地来登录。
mysql> grant select,update,insert on proftpd.* to proftpd@localhost identified by '123456';

 

在proftpd数据库中建立数据表，proftpd使用mysql验证用户时一共使用4个表，分别是ftpuser,ftpgroup,ftpquotalimits,ftpquotatallies。下面是建立这4个表的sql语句

###########################################################################

###########################################################################

# ftpgroup 

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) TYPE=MyISAM COMMENT='ProFTP group table';

# ftpquotalimits 

CREATE TABLE ftpquotalimits (
name varchar(30) default NULL,
quota_type enum('user','group','class','all') NOT NULL default 'user',
per_session enum('false','true') NOT NULL default 'false',
limit_type enum('soft','hard') NOT NULL default 'soft',
bytes_in_avail int(10) unsigned NOT NULL default '0',
bytes_out_avail int(10) unsigned NOT NULL default '0',
bytes_xfer_avail int(10) unsigned NOT NULL default '0',
files_in_avail int(10) unsigned NOT NULL default '0',
files_out_avail int(10) unsigned NOT NULL default '0',
files_xfer_avail int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

# ftpquotatallies 

CREATE TABLE ftpquotatallies (
name varchar(30) NOT NULL default '',
quota_type enum('user','group','class','all') NOT NULL default 'user',
bytes_in_used int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) unsigned NOT NULL default '0',
files_in_used int(10) unsigned NOT NULL default '0',
files_out_used int(10) unsigned NOT NULL default '0',
files_xfer_used int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

# ftpuser 

CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '5500',
gid smallint(6) NOT NULL default '5500',
homedir varchar(255) NOT NULL default '',
shell varchar(16) NOT NULL default '/sbin/nologin',
count int(11) NOT NULL default '0',
accessed datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id),
UNIQUE KEY userid (userid)
) TYPE=MyISAM COMMENT='ProFTP user table';

 

 

# 数据表建立成功后加入测试帐号test，安装成功后你可以用这个帐号测试
 INSERT INTO `ftpgroup` VALUES ('ftpgroup', 5500, 'ftpuser');
 INSERT INTO `ftpuser` VALUES (1, 'test', 'test', 5500, 5500, '/home/ftpuser/test', '/sbin/nologin',0,'','');
 INSERT INTO `ftpquotalimits` VALUES ('test','user','false','hard','15728640','0','0','0','0','0');
这时数据库中已经有一个用户，用户名为:test，密码为:test，用户根目录为：/home/ftp/test，他的空间大小限制为15M，超过 15M后用户将不能上传文件。

 

配置用户权限,proftpd的用户权限控制是很完备的。

配置可以参考如下：

<Limit RMD RNTO DELE STOR MKD SITE_CHMOD RNTO>

        DenyUser test

</Limit>

删除目录(RMD),删除文件(DEL),和执行chmod命令(SITE_CHMOD),上传(STOR),建目录(MKD),下载(RETR),重命名(RNTO)

 

日志：

Proftpd记录的日志一般在

/var/log/xferlog
/var/log/messages

评论

1 楼 have23 2012-03-12  

你可以创建与授权用户。
GRANT ALL PRIVILEGES ON *.* TO 'userName'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

来解决你不能远程登录mysql的问题。


该语句可以删除来自所有授权表的帐户权限记录。

drop user username@'%'

drop user username@localhost