setup pure-ftpd server with mysql authentication

1- install the necessary packages
$ sudo apt-get install pure-ftpd pure-ftpd-mysql mysql-server mysql-client

2- create database and tables for ftp users

/*pureftp.sql */;
SET sql_mode = '';
create database pureftp;
use pureftp;
CREATE TABLE `ftp_user` (
  `ftp_user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sys_userid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_groupid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_perm_user` varchar(5) DEFAULT NULL,
 `sys_perm_group` varchar(5) DEFAULT NULL,
  `sys_perm_other` varchar(5) DEFAULT NULL,
  `server_id` int(11) unsigned NOT NULL DEFAULT '0',
  `parent_domain_id` int(11) unsigned NOT NULL DEFAULT '0',
  `username` varchar(64) DEFAULT NULL,
  `username_prefix` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(64) DEFAULT NULL,
  `quota_size` bigint(20) NOT NULL DEFAULT '-1',
  `active` enum('n','y') NOT NULL DEFAULT 'y',
  `uid` varchar(64) DEFAULT NULL,
  `gid` varchar(64) DEFAULT NULL,
  `dir` varchar(255) DEFAULT NULL,
  `quota_files` bigint(20) NOT NULL DEFAULT '-1',
  `ul_ratio` int(11) NOT NULL DEFAULT '-1',
  `dl_ratio` int(11) NOT NULL DEFAULT '-1',
  `ul_bandwidth` int(11) NOT NULL DEFAULT '-1',
  `dl_bandwidth` int(11) NOT NULL DEFAULT '-1',
  `expires` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ftp_user_id`),
  KEY `active` (`active`),
  KEY `server_id` (`server_id`),
  KEY `username` (`username`),
  KEY `quota_files` (`quota_files`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `ftp_user` VALUES (3,2,2,'riud','riud','',1,1,'ftp2','test',ENCRYPT('test123'),-1,'y','web2','client1','/var/www/clients/client1/web2',-1,-1,-1,-1,-1,'0000-00-00 00:00:00');


$ mysql -u root -p < pureftp.sql

after you import with the sql file above your will have a user ftp2 with password test123

3- update pure-ftpd configuration file

-/etc/pure-ftpd/db/mysql.conf

MYSQLServer     127.0.0.1
MYSQLUser       root
MYSQLPassword   sqlpassword
MYSQLDatabase   pureftp
MYSQLCrypt      crypt
MYSQLGetPW      SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
MYSQLGetUID     SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
MYSQLGetGID     SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
MYSQLGetDir     SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
MySQLGetQTAFS  SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_files != '-1' AND username="\L"
MySQLGetQTASZ  SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_size != '-1' AND username="\L"
MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_ratio != '-1' AND username="\L"
MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_ratio != '-1' AND username="\L"
MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_bandwidth != '-1' AND username="\L"
MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_bandwidth != '-1' AND username="\L"
MySQLTransactions On

- /etc/pure-ftpd/conf/ChrootEveryone
yes

- /etc/pure-ftpd/conf/UnixAuthentication
no

-/etc/pure-ftpd/conf/PAMAuthentication
no

4- restart pure ftp server
$ sudo /etc/init.d/pure-ftpd-mysql restart

5- create group and local user that map to our user in mysql and also the directory for its home directory
$ mkdir -p /var/www/clients/client1/
$ sudo groupadd  -g 5005 client1
$ sudo useradd  -u 5005  -m -d /var/www/clients/client1/web2 -g client1 -s /bin/false web2

screenshot when login already

***note to debug pure-ftpd auth
create file below with content yes
- /etc/pure-ftpd/conf/VerboseLog
yes