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:

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:

Now, add the following to your proftpd.conf:

That’s it. Restart ProFTPd and test it. As soon as you got some rows, try to run a query like e.g.:

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.

3 Responses

  1. Oberdan
    Apr 01, 2008 - 09: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.

  2. dragnovich
    Sep 28, 2009 - 05: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!

  3. iezzip
    Sep 28, 2009 - 05: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

Leave a Comment

css.php