ProFTPd xferlog via MySQL
Logging your FTP transfers to xferlog with ProFTPd is a nice thing. This can easily be done by a one-liner in /etc/proftpd/proftpd.conf:
TransferLog /var/log/proftpd/xferlog
This generates a nice transfer log which we could then parse for transfer statistics. But there is a much better way to accomplish this: MySQL. Let’s use MySQL for everything!
It’s pretty straightforwarded to get ProFTPd to log into a MySQL table.
First, create a table ‘ftpxferlog’ in your database and give the required user INSERT access to it:
CREATE TABLE `ftpxferlog` ( `id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(30) NOT NULL DEFAULT '', `filename` text, `size` BIGINT(20) DEFAULT NULL, `host` tinytext, `ip` tinytext, `action` tinytext, `duration` tinytext, `localtime` TIMESTAMP NULL DEFAULT NULL, `success` BOOL NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_usersucc` (`username`, `success`) ) TYPE=InnoDB; GRANT INSERT ON mydatabase.ftpxferlog TO myuser@localhost; FLUSH PRIVILEGES;
Now, add the following to your proftpd.conf:
SQLConnectInfo mydatabase@localhost myuser mypassword # xfer log in mysql SQLLog RETR,STOR transfer1 SQLNamedQuery transfer1 INSERT "NULL, '%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), '1'" ftpxferlog SQLLOG ERR_RETR,ERR_STOR transfer2 SQLNamedQuery transfer2 INSERT "NULL, '%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), '0'" ftpxferlog
That’s it. Restart ProFTPd and test it. As soon as you got some rows, try to run a query like e.g.:
SELECT sum(size) FROM ftpxferlog WHERE username = 'web2' AND success = '1' AND localtime LIKE '2007-06-07%';
For traffic statistics calculation this is way easier than parsing a logfile. Also I have defined the key ‘idx_usersucc’ on the table to speed things up.
Check the ProFTPd SQL Tutorial (in German) for advanced configuration.
Oberdan said
am April 1 2008 @ 9:06 am
Hello,
thank you for your article, but the word “localtime” is a reserved word of mysql so I would suggest changing the name.
By Obe.
dragnovich said
am September 28 2009 @ 5:44 pm
I can’t make it work, the ftp service does not initialize, when I add the lines.
It seems that the server need something more installed.
I got this error:
Fatal: unknown configuration directive ‘SQLConnectInfo’ on line 68 of ‘/etc/proftpd.conf’
Regards!
iezzip said
am September 28 2009 @ 5:52 pm
@dragnovich: Make sure you have the mod_sql_mysql Module enabled.
On Debian Lenny, you’ll find this configuration in /etc/proftpd/modules.conf:
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
Make sure that you have this module installed and restart Proftpd:
# apt-get install proftpd-mod-mysql
# /etc/init.d/proftpd restart