This article will guide you through installing pure-ftpd configured for virtual hosts using mysql as the authentication mechanism. It assumes you have a functioning Linux server running mysql already.

If you’re lucky enough to use Ubuntu, you can simply run

apt-get install pure-ftpd-mysql

First of we’ll create a table in mysql

CREATE TABLE IF NOT EXISTS `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 '1002',
`gid` smallint(6) NOT NULL default '1002',
`homedir` varchar(255) NOT NULL,
`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`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='ProFTP user table' AUTO_INCREMENT=0 ;

Put some data in the table to, you’ll need to put in a userid, passwd and a homedir.
Take note of the uid & gid fields, these will need to match the file system owner of the homedir if your FTP is to succesfully upload files.

I have a user called virtual, with a uid and gid of 1002 which owns all the files under /home/virtualhosts. It does the job fine.

Next create a mysql user that can read that table, remember the password, you’ll need it next.

Once pure-ftpd is installed, edit /etc/pure-ftpd/db/mysql.conf

MYSQLServer     127.0.0.1
MYSQLPort       3306
MYSQLSocket      /var/run/mysqld/mysqld.sock
MYSQLUser      XXXX
MYSQLPassword   XXXX
MYSQLDatabase   XXXX

MYSQLCrypt      cleartext

MYSQLGetPW      SELECT passwd FROM ftpuser WHERE userid="L"
MYSQLGetUID     SELECT uid FROM ftpuser WHERE userid="L"
#MYSQLDefaultUID 1000
MYSQLGetGID     SELECT gid FROM ftpuser WHERE userid="L"
#MYSQLDefaultGID 1000
MYSQLGetDir     SELECT homedir FROM ftpuser WHERE userid="L"

# MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="L"

# MySQLGetRatioUL SELECT ULRatio FROM users WHERE User="L"
# MySQLGetRatioDL SELECT DLRatio FROM users WHERE User="L"

# MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="L"
# MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="L"

# MySQLTransactions On

Some information on that default config file, at a minimum you will need to change the following values, MYSQLUser, MYSQLPassword and MYSQLDatabase so that the server can read your virtual user database in mysql.

I’ve left some options commented out incase you’re interested in using them, such as ratios,bandwidth throttling and quotaing, all of which are outside the scope of this article.

You can change MYSQLCrypt from “ClearText” if you don’t wish your passwords to be stored in clear text.
Valid values are : “cleartext”, “crypt”, “md5″ and “password”. See the docs for more information.

Finally, you can turn on Transactions if you wish. Again, see the docs for more information.

One last configuration file, we need to make sure the virtual users are chroot’ed to their virtual home directory.

echo Yes > /etc/pure-ftpd/conf/ChrootEveryone

Now start the server,

/etc/init.d/pure-ftpd-mysql start

You have my guarantee* it will work.
Well, probably!