Move Traffic DB to a Separate Server

Sometimes it is required to log all browsing records (even non filtered) to MySQL/MariaDB. In this case it is recommended to move MySQL database to a separate server.

Note

In the following instructions we assume you are using a fresh install of Ubuntu 16 LTS 64-bit machine with static IP address 192.168.1.20, netmask 255.255.255.0 and gateway 192.168.1.1. We will call this server database box.

Install MySQL on the database box by running the following commands as root.

# debian, ubuntu
export DEBIAN_FRONTEND=noninteractive

# install it
apt-get -y install mysql-server python-mysqldb

# restart mysql
service mysql restart

Create special websafety user in MySQL with superuser rights; this user will be allowed to login from any host.

mysql -u root mysql < websafety.sql

Where websafety.sql contains the following.

GRANT ALL PRIVILEGES ON *.* To 'websafety'@'%' IDENTIFIED BY 'Passw0rd';
FLUSH PRIVILEGES;

Edit MySQL configuration file at /etc/mysql/mysql.conf.d/mysqld.cnf to allow listening for incoming TCP connections on designated IP address.

bind-address=192.168.1.20

Restart MySQL and see if the mysql port is indeed opened by MySQL daemon by running netstat -ap | grep mysql. The output must look like the following.

root@websafety:/etc/mysql/mysql.conf.d# netstat -ap | grep mysql
tcp        0      0 192.168.1.20:mysql      *:*                     LISTEN      1403/mysqld

Now on the proxy machine edit DILADELE_MONITOR_DB_MYSQL setting in /opt/websafety/var/console/console/settings.py.

DILADELE_MONITOR_DB_MYSQL = {
    'ENGINE'  : 'django.db.backends.mysql',
    'NAME'    : 'websafety_monitor',
    'USER'    : 'websafety',
    'PASSWORD': 'Passw0rd',
    'HOST'    : '192.168.1.20', # the IP is pointing to MySQL server
    'PORT'    : '3306'
}

Run the following commands on the proxy machine as root.

# stop apache server so that Web UI does not have open connections to database
systemctl stop apache2

# stop wsmgrd daemon so that it does not hold open connections to database
systemctl stop wsmgrd

# switch Web UI to sqlite and back to mysql
python /opt/websafety/var/console/switch_db.py --db=sqlite
python /opt/websafety/var/console/switch_db.py --db=mysql

# reinitialize the database recreating all tables and dropping all current records
python /opt/websafety/var/console/sync_db.py

# reset the owner just in case
chown -R websafety:websafety /opt/websafety

# start apache and wsmgrd
systemctl start wsmgrd
systemctl start apache2

Now click Save and Restart from Web UI, reboot your proxy box and verify that the traffic information gets uploaded to the remote MySQL. The log files at /opt/websafety/var/log/database.log and /opt/websafety/var/log/wsmgrd.log may help with troubleshooting.

Note

Please consult your DBA on all issues related to securing access to this MySQL instance.