{"id":166,"date":"2009-11-18T23:20:05","date_gmt":"2009-11-18T15:20:05","guid":{"rendered":"http:\/\/twycf.com\/wordpress\/?p=166"},"modified":"2009-11-18T23:20:44","modified_gmt":"2009-11-18T15:20:44","slug":"proftpd-%e6%94%af%e6%8f%b4-utf-8-mysql-%e8%99%9b%e6%93%ac%e5%b8%b3%e8%99%9f-quota-%e9%99%90%e5%88%b6","status":"publish","type":"post","link":"https:\/\/twycf.com\/?p=166","title":{"rendered":"Proftpd \u652f\u63f4 UTF-8 + MYSQL \u865b\u64ec\u5e33\u865f + Quota \u9650\u5236"},"content":{"rendered":"

\u539f\u6587\u53c3\u8003:[FreeBSD & Linux Ubuntu] Proftpd \u652f\u63f4 UTF-8 + MYSQL \u865b\u64ec\u5e33\u865f + Quota \u9650\u5236<\/a>
\n\u539f\u59cb\u6587\u7ae0\u4e2d\u6709\u4e00\u4e9b\u932f\u8aa4\uff0c\u6211\u6703\u5728\u5167\u6587\u4e2d\u66f4\u6b63\u9019\u4e9b\u932f\u8aa4<\/p>\n

\u5b89\u88dd\u4e4b\u524d\u6e96\u5099\u5de5\u4f5c
\n[cc lang=”c”]portsnap fetch && portsnap update
\nMysql \u8981\u5148\u5b89\u88dd\u597d[\/cc]
\n
\n\u5b89\u88dd\u6642\u624d\u4e0d\u6703\u5b89\u88dd\u5230\u4e0d\u652f\u63f4 UTF8 \u7684\u820a\u7248\u672c\uff0c\u672c\u6b21\u5b89\u88dd\u70ba proftpd-1.3.2b
\n\u5be6\u9a57\u74b0\u5883\uff1a
\nFreeBSD 7.2-Release
\nproftpd 1.3.2b<\/coolcode>
\n\u5b89\u88dd\u65b9\u5f0f
\ncd \/usr\/ports\/ftp\/proftpd-mysql
\nmake install clean<\/coolcode>
\n\u9078\u64c7
\nNLS
\nQuota
\nMysql
\nRatio<\/coolcode>
\n\u5efa\u7acb\u8cc7\u6599\u5eab
\n
\nCREATE DATABASE `ftp` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
\n<\/coolcode>
\n\u5efa\u7acb\u4f7f\u7528\u8005\u8cc7\u6599\u8868\uff0c\u9019\u500b\u8cc7\u6599\u8868\u662f\u7d00\u9304\u4f7f\u7528\u8005\u7684\u6240\u6709\u8cc7\u6599
\n
\n# username \u4f7f\u7528\u8005\u5e33\u865f
\n# uid \u7cfb\u7d71\u7684 uid \u9019\u500b\u53ef\u4ee5\u81ea\u5df1\u96a8\u4fbf\u7d66\u500b\u6578\u5b57\uff0c\u4e0d\u8981\u91cd\u8907\u5230\u7cfb\u7d71\u5e33\u865f
\n# gid \u7cfb\u7d71\u7684 gid \u9019\u500b\u770b\u4f60\u7528\u9014\u662f\u4ec0\u9ebc\u4f86\u53bb\u5c0d\u61c9
\n# password \u4f7f\u7528\u8005\u5bc6\u78bc
\n# homedir \u4f7f\u7528\u8005\u5bb6\u76ee\u9304
\n# shell \u4f7f\u7528\u8005\u7684 shell \u6700\u597d\u662f\u586b \/sbin\/nologin
\n# accessed \u4f7f\u7528\u8005\u6700\u5f8c\u767b\u5165\u7684\u6642\u9593
\n# count \u767b\u5165\u6b21\u6578
\n#\u9019\u500b\u90e8\u4efd\u589e\u52a0\u4e86\u5169\u500b\u6b04\u4f4d accessed, count \uff0c\u56e0\u70ba\u4f7f\u7528\u8005\u767b\u5165\u6642\uff0c\u6703\u57f7\u884c updatecount \u7684SQL\u8a9e\u6cd5\uff0c\u4f46\u6c92\u6709\u9019\u5169\u500b\u6b04\u4f4d\uff0c\u6703\u5beb\u4e0d\u9032\u53bb
\n#\u5176\u5be6\u9084\u5c11\u4e86 modified \u6b04\u4f4d\uff0c\u4f46\u662f\u6211\u60f3\u4e0d\u51fa\u4f86\u5132\u5b58\u9019\u500b\u6b04\u4f4d\u6709\u4ec0\u9ebc\u7528\u9014\uff0c\u6240\u4ee5\u4e0d\u4fee\u6b63<\/p>\n

DROP TABLE IF EXISTS `ftp`;
\nCREATE TABLE IF NOT EXISTS `ftp` (
\n `username` varchar(60) default NULL,
\n `uid` int(11) NOT NULL,
\n `gid` int(11) default NULL,
\n `password` varchar(30) default NULL,
\n `homedir` varchar(60) default NULL,
\n `shell` varchar(32) default ‘\/usr\/bin\/nologin’,
\n `accessed` datetime NOT NULL,
\n `count` int(11) NOT NULL,
\n PRIMARY KEY (`uid`),
\n UNIQUE KEY `username` (`username`)
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;
\n<\/coolcode>
\n\u63a5\u4e0b\u4f86\u5efa\u7acb group \u8cc7\u6599\u8868
\n
\n# groupname \u7fa4\u7d44\u5e33\u865f
\n# gid \u7cfb\u7d71\u7684 gid \u9019\u500b\u5c0d\u61c9\u5230\u7cfb\u7d71 \/etc\/group \u7684 gid \u503c\uff0c\u4e5f\u53ef\u4ee5\u4e0d\u7528\uff0c\u770b\u4f60\u600e\u9ebc\u7528
\n# members \u9019\u500b\u7fa4\u7d44\u7684\u6210\u54e1<\/p>\n

CREATE TABLE IF NOT EXISTS `groups` (
\n `groupname` varchar(30) NOT NULL DEFAULT ”,
\n `gid` int(11) NOT NULL DEFAULT ‘0’,
\n `members` varchar(255) DEFAULT NULL
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;
\n<\/coolcode>
\nmembers \u6b04\u4f4d\uff0c\u4ee5\u9017\u9ede\u9694\u958b\u6bcf\u4e00\u500b\u6210\u54e1\uff0c\u6240\u4ee5\u4e00\u500b\u7fa4\u7d44\u53ef\u4ee5\u8a31\u591a\u500b\u6210\u54e1\uff0c\u4f8b\u5982
\n
\ntest1,test2,test3
\n<\/coolcode>
\n\u63a5\u4e0b\u4f86\u4fee\u6539 proftpd.conf
\n
\nUseReverseDNS off
\n#\u5efa\u8b70\u958b\u555f Log\uff0c\u767b\u5165\u767c\u751f\u932f\u8aa4\u6642\uff0c\u4e5f\u6bd4\u8f03\u597d\u6293\u554f\u984c
\nSystemLog \/var\/log\/proftpd\/ftp.log
\nDefaultAddress ::
\n#
\n# \u4f7f\u7528 mysql \u8cc7\u6599\u5eab\u70ba\u4e3b
\n#
\nSQLBackend mysql<\/p>\n

# SQLAuthTypes Backend
\n# \u5bc6\u78bc\u8a8d\u8b49 \u5df2 PASSWORD() \u7522\u751f\u70ba\u4e3b
\n# \u5bc6\u78bc\u4f7f\u7528\u660e\u78bc Plaintext
\nSQLAuthTypes Plaintext<\/p>\n

#
\n# \u958b\u555f\u8a8d\u8b49
\n#
\nSQLAuthenticate on
\n#
\n# MySQL \u9023\u7dda\u8cc7\u8a0a\uff0c\u8cc7\u6599\u5eab\u540d\u7a31@\u4e3b\u6a5f \u5e33\u865f \u5bc6\u78bc
\n#
\nSQLConnectInfo ftp@localhost root wwwadmin080225
\n#
\n# \u4f7f\u7528\u8005\u8cc7\u6599\u5eab\u6b04\u4f4d
\n#
\nSQLUserInfo ftp username password uid gid homedir shell
\n#
\n# \u7fa4\u7d44\u8cc7\u6599\u5eab\u6b04\u4f4d
\n#
\nSQLGroupInfo groups groupname gid members
\n#
\n# SQL Log \u6a94
\n#
\nSQLLogFile \/var\/log\/proftpd\/sql.log
\n#
\n# \u7576 Home \u76ee\u9304\u4e0d\u5b58\u5728\uff0c\u6703\u81ea\u5df1\u7522\u751f\u3002
\n#
\nSQLHomedirOnDemand on
\n#
\n# SQL Log \u683c\u5f0f\uff0c\u7576\u6b63\u78ba\u767b\u5165\u6642\uff0c\u8981\u57f7\u884c\u7684 SQL \u8a9e\u6cd5
\n#
\nSQLLog PASS updatecount
\n#
\n# updatecount \u589e\u52a0\u767b\u5165\u8ddf\u6642\u9593
\n#
\nSQLNamedQuery updatecount UPDATE “count=count+1, accessed=now() where userid=’%u'” ftp
\n#
\n# SQL Log \u683c\u5f0f\uff0c\u7576\u5132\u5b58\u6216\u522a\u9664\u6a94\u6848\u6642\uff0c\u8981\u57f7\u884c\u7684 SQL \u8a9e\u6cd5
\n#
\nSQLLog STOR,DELE modified
\n#
\n# modified \u8a9e\u6cd5
\n#
\nSQLNamedQuery modified UPDATE “modified=now() where userid=’%u'” ftp
\n<\/coolcode>
\n\u4ee5\u4e0bQuota \u7684\u90e8\u4efd\u5c1a\u672a\u6e2c\u8a66\uff0c\u5148\u6574\u500b\u8cbc\u904e\u4f86
\n
\n\u63a5\u4e0b\u4f86\u662f\u8981\u4ecb\u7d39\u5982\u4f55\u8a2d\u5b9a quota \uff0c\u7136\u5f8c\u5efa\u7acb mysql \u76f8\u95dc\u8cc7\u6599\u8868
\n#
\n#quota_type \u786c\u789f\u9650\u984d\u7684\u9452\u5225,\u53ef\u4ee5\u8a2d\u7f6e\u55ae\u5404\u4f7f\u7528\u8005\uff0c\u4e5f\u53ef\u4ee5\u8a2d\u7f6e\u4e00\u5404\u7d44\u4e2d\u7684\u5168\u90e8\u4f7f\u7528\u8005\uff0c\u9084\u53ef\u4ee5\u8a2d\u7f6e\u5168\u90e8\u4f7f\u7528\u8005
\n#bytes_in_avail \u4e0a\u50b3\u6700\u5927\u4f4d\u5143\u7d44\u6578\uff0c\u5c31\u662fFTP\u4f7f\u7528\u8005\u7a7a\u9593\u5bb9\u91cf (\u8a2d\u7f6e\u500b\u5b57\u6bb5\u7684\u6642\u5019\u662f\u4ee5byte(\u4f4d\u5143\u7d44)\u70ba\u55ae\u4f4d\uff0c\u5982\u679c\u8981\u9650\u984d\u572810M\uff0c\u90a3\u5c31\u662f10240000,\u4e0b\u9762\u4e5f\u4e00\u6a23)
\n#bytes_out_avail \u4e0b\u8f09\u6700\u5927\u4f4d\u5143\u7d44\u6578\uff0c\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u9019\u500b\u5b57\u6bb5\u4e2d\u8a18\u9304\u7684\u662f\u4f7f\u7528\u8005\u7e3d\u5171\u80fd\u5f9e\u4f3a\u670d\u5668\u4e0a\u4e0b\u8f09\u591a\u5c11\u8cc7\u6599\uff0c\u8cc7\u6599\u662f\u7d2f\u8a08\u7684\u3002
\n#bytes_xfer_avail \u7e3d\u5171\u53ef\u50b3\u8f38\u7684\u6587\u4ef6\u7684\u6700\u5927\u4f4d\u5143\u7d44\u6578(\u4e0a\u50b3\u548c\u4e0b\u8f09\u6d41\u91cf)\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u9019\u500b\u5b57\u6bb5\u4e2d\u8a18\u9304\u7684\u662f\u4f7f\u7528\u8005\u7e3d\u5171\u80fd\u50b3\u8f38\u6587\u4ef6\u7684\u6700\u5927\u4f4d\u5143\u7d44\u6578\uff0c\u8cc7\u6599\u662f\u7d2f\u8a08\u7684\u3002
\n#files_in_avail INT \u7e3d\u5171\u80fd\u4e0a\u50b3\u6587\u4ef6\u7684\u6578\u76ee
\n#files_out_avail INT \u80fd\u5f9e\u4f3a\u670d\u5668\u4e0a\u4e0b\u8f09\u6587\u4ef6\u7684\u7e3d\u6578\u76ee
\n#files_xfer_avail INT \u7e3d\u5171\u53ef\u50b3\u8f38\u6587\u4ef6\u7684\u6578\u76ee(\u4e0a\u50b3\u548c\u4e0b\u8f09)
\n#
\nCREATE TABLE quotalimits (
\nname VARCHAR(30),
\nquota_type ENUM(“user”, “group”, “class”, “all”) NOT NULL,
\nper_session ENUM(“false”, “true”) NOT NULL,
\nlimit_type ENUM(“soft”, “hard”) NOT NULL,
\nbytes_in_avail FLOAT NOT NULL,
\nbytes_out_avail FLOAT NOT NULL,
\nbytes_xfer_avail FLOAT NOT NULL,
\nfiles_in_avail INT UNSIGNED NOT NULL,
\nfiles_out_avail INT UNSIGNED NOT NULL,
\nfiles_xfer_avail INT UNSIGNED NOT NULL
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;<\/p>\n

CREATE TABLE quotatallies (
\nname VARCHAR(30) NOT NULL,
\nquota_type ENUM(“user”, “group”, “class”, “all”) NOT NULL,
\nbytes_in_used FLOAT NOT NULL,
\nbytes_out_used FLOAT NOT NULL,
\nbytes_xfer_used FLOAT NOT NULL,
\nfiles_in_used INT UNSIGNED NOT NULL,
\nfiles_out_used INT UNSIGNED NOT NULL,
\nfiles_xfer_used INT UNSIGNED NOT NULL
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;
\n\u518d\u4f86\u662f\u4fee\u6539 proftpd.conf \u7684\u6a94\u6848<\/p>\n

#
\n# \u786c\u789f\u9650\u984d\u90e8\u5206
\n#
\nQuotaDirectoryTally on
\n#
\n#\u786c\u789f\u9650\u984d\u55ae\u4f4d b | Kb | Mb| Gb
\n#
\nQuotaDisplayUnits “Kb”
\n#
\n# \u555f\u52d5 Quota
\n#
\nQuotaEngine on
\n#
\n# \u786c\u789f\u9650\u984d\u65e5\u8a8c\u8a18\u9304
\n#
\nQuotaLog “\u4f60\u7684LOG\u8def\u5f91”
\n#
\n# \u6253\u958b\u786c\u789f\u9650\u984d\u8cc7\u8a0a\uff0c\u7576\u767b\u9678FTP\u5e33\u6236\u5f8c\uff0c\u4f7f\u7528\u547d\u4ee4 “quote SITE QUOTA” \u5f8c\u53ef\u986f\u793a\u7576\u524d\u4f7f\u7528\u8005\u7684\u786c\u789f\u9650\u984d
\n#
\nQuotaShowQuotas on
\n#
\n#\u4ee5\u4e0b\u662fSQL\u8abf\u7528\u8a9e\u53e5\uff0c\u4e0d\u7528\u4fee\u6539\u76f4\u63a5\u62f7\u8c9d\u904e\u53bb
\n#
\nSQLNamedQuery 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 quotalimits WHERE name = ‘%{0}’ AND quota_type = ‘%{1}'”<\/p>\n

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 quotatallies WHERE name = ‘%{0}’ AND quota_type = ‘%{1}'”<\/p>\n

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}'” quotatallies<\/p>\n

SQLNamedQuery insert-quota-tally INSERT “%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}” quotatallies
\n<\/coolcode>
\n\u63a5\u4e0b\u4f86\uff0c\u5148\u5efa\u7acb\u4e00\u500b group \u7d66 ftp
\n
\n# FreeBSD \u5efa\u7acb FTP group
\npw group add ftp
\nmkdir \/home\/ftp
\nchown appleboy:ftp \/home\/ftp
\n#
\n# ftp \u9019\u500b\u7fa4\u7d44\u7684\u90fd\u53ef\u4ee5\u5728\u8cc7\u6599\u593e\u5e95\u4e0b\u65b0\u589e\u522a\u9664
\n#
\nchmod 775 \/home\/ftp
\n<\/coolcode>
\n
\n#
\n# \u65b0\u589e sql \u8a9e\u6cd5
\n#
\nINSERT INTO `ftp` (`username`, `uid`, `gid`, `password`, `homedir`, `shell`) VALUES
\n(‘test1’, 1500, 1004, ‘1234’, ‘\/home\/ftp’, ‘\/usr\/sbin\/nologin’);
\nINSERT INTO `groups` (`groupname`, `gid`, `members`) VALUES
\n(‘ftp’, 1004, ‘appleboy,test1,test2’);
\nINSERT INTO `quotalimits` (`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`) VALUES
\n(‘test1’, ‘user’, ‘false’, ‘soft’, 1024000, 0, 204800, 500, 0, 10);
\n<\/coolcode><\/p>\n

\u932f\u8aa4\u8a0a\u606f\u4ee5\u53ca\u8aaa\u660e\uff1a
\n
\nUSER test 1 (Login failed): Invalid shell: ‘\/sbin\/nologin’
\n\u7cfb\u7d71\u4e0d\u5141\u8a31\u4f7f\u7528\u9019\u500b shell \u8acb\u5728 \/etc\/shells \u52a0\u5165\u4e00\u884c
\n\/usr\/sbin\/nologin
\n<\/coolcode>
\n\u5982\u679c\u6a94\u6848\u7121\u6cd5\u4e0a\u3001\u4e0b\u50b3\u3001\u522a\u9664\uff0c\u8acb\u6aa2\u67e5\u6b0a\u9650
\nftp \u8cc7\u6599\u8868\u4e2d\u7684 uid, gid \u5c31\u662f\u6a94\u6848\u7cfb\u7d71\u4e2d\u7684\u6b0a\u9650\uff0c\u5fc5\u9808\u8a2d\u5b9a\u76f8\u5c0d\u61c9\u7684\u6b0a\u9650\u624d\u80fd\u6b63\u5e38\u904b\u4f5c<\/p>\n","protected":false},"excerpt":{"rendered":"

\u539f\u6587\u53c3\u8003:[FreeBSD & Linux Ubuntu] Proftpd \u652f\u63f4 UTF-8 + M […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,28,16],"tags":[34,31,29,30,32],"class_list":["post-166","post","type-post","status-publish","format-standard","hentry","category-freebsd","category-ftp","category-services-2","tag-freebsd","tag-mysql","tag-proftpd","tag-utf8","tag-32"],"_links":{"self":[{"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/posts\/166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/twycf.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=166"}],"version-history":[{"count":6,"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/posts\/166\/revisions"}],"predecessor-version":[{"id":172,"href":"https:\/\/twycf.com\/index.php?rest_route=\/wp\/v2\/posts\/166\/revisions\/172"}],"wp:attachment":[{"href":"https:\/\/twycf.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/twycf.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/twycf.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}