Store Monitoring Data in MySQL

Default installation of Diladele Web Safety stores monitoring data in SQLite database located in /var/opt/quintolabs/qlproxy/console/monitoring.sqlite. This database is good enough to store browsing statistics for blocked items of a typical household for a month or so without consuming many system resources. On the other hand, if it is required to store all browsing statistics or handle amount of data in a typical business office it is recommended to switch Diladele Web Safety monitoring module to a more powerful database such as MySQL.

To switch Diladele Web Safety monitoring module to MySQL (on Ubuntu Server 13.10 for example) we would need to.

  1. Install some utilities to provide default mysql installation responses prior to installing MySQL itself

    $ sudo apt-get install debconf-utils
  2. Install MySQL database to be used for Diladele Web Safety monitoring module

    $ echo "mysql-server-5.5 mysql-server/root_password_again password P@ssw0rd" | debconf-set-selections
    $ echo "mysql-server-5.5 mysql-server/root_password password P@ssw0rd" | debconf-set-selections
    $ sudo apt-get install mysql-server
  3. Secure your MySQL installation if desired

    $ sudo /usr/bin/mysql_secure_installation
  4. Create database that would store monitoring data for Diladele Web Safety

    $ echo "CREATE DATABASE qlproxy_monitor CHARACTER SET utf8;" | mysql -u root --password=P@ssw0rd
  5. Patch the qlproxy settings file download the file here.

    $ sudo patch /var/opt/quintolabs/qlproxy/console/qlproxy/ <
  6. Stop the apache server

    $ sudo service apache2 stop
  7. Install MySQL Python module

    $ sudo apt-get install python-mysqldb
  8. Sync the database

    $ sudo python /var/opt/quintolabs/qlproxy/console/ syncdb --database=monitor
  9. Start the apache server

    $ sudo service apache2 start

From now on next monitoring data upload will be done into this MySQL database. Reporting will use this database too. If you require your own reports on collected data, be free to use any reporting tool available for MySQL databases.

About sichent

This entry was posted in MySQL and tagged , . Bookmark the permalink.