0. MySQL configuration

MySQL settings

General

Ensure that mysql starts automatically (e.g. 'chkconfig mysql on' on RedHat/Suse systems or 'update-rc.d mysql defaults' on Debian based systems).

Add the path to 'mysql' to your PATH for ease of use.

Set a softlink "/mysqldata" to point to the mysql database directory, for example '/var/lib/mysql'. In most of the documentation we refer to /mysqldata for brevity.

ln -s /var/lib/mysql /mysqldata 

my.cnf

Compare your /etc/my.cnf (or /etc/mysql/my.cnf) with /opt/nac/contrib/etc/my.cnf, for parameters that may need to be set in the [mysqld] section.

The most important parameters to check are:

log-bin and report-host to include hostname. On the master this might be vmps1, on secondaries vmps2/3 etc.:

log-bin = vmps1-bin
log-warnings
report-host = vmps1
server-id       = 10                     [10 for master, 20 for slave1, 20 for slave 2 etc..] 
relay-log=vmps1-relay-bin
replicate-do-db= opennac
replicate-wild-ignore-table= opennac.vmpsauth%

On Ubuntu 7.10, log-bin is configured with the full path, and should include the hostname. It may also be called log_bin, not log-bin:

log-bin = /var/log/mysql/vmps1-bin.log

Consider increasing the connection timeouts to avoid spurious deconnection on low traffic networks, add the following:

interactive_timeout = 604800
wait_timeout = 604800 

MySQL needs to be listening to the network on port 3306, but it might be bound only to localhost (e.g. Ubuntu default). Check the parameter bind-address and comment it out:

#bind-address = 127.0.0.1

Each server can insert data locally, changes are replicated to other servers and the changes do not conflict. Datasets must be configured with autoincrement keys, and the autoincrement value set differently on each server - thus avoiding replication conflicts. An auto_increment_increment value of 5 allows a maximun of 5 servers. Each server must have a different auto_increment_offset (1 for the first, or main server, 2 for the second, etc.)

auto_increment_increment= 5 
auto_increment_offset   = 1    [1 for vmps1, 2 for vmps2, 3 for vmps3 ...] 

Permissions

Ensure the mysql user can write to the database files (this is usually the case).

chown -R mysql /mysqldata /var/lib/mysql

Restart

Ensure that /etc/init.d/mysql exists, and automatic start is enabled. Finally, restart mysql in order to take into account the modifications you made to my.cnf:

/etc/init.d/mysql restart 

You can check that mysql is running by looking at netstat, and verify that mysqld is now bound on 0.0.0.0 and not 127.0.0.1 only:

$ netstat -anp|grep mysql
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     5666/mysqld

Initial FreeNAC data-set

Extract the SQL scripts

cd /mysqldata
cp /opt/nac/contrib/opennac_db.tar.gz .
tar xvzf opennac_db.tar.gz

Create an empty dataset (new masters only)

For a new master server: Install an initial set of empty FreeNAC tables for the 'opennac' database, backing up the existing tables first (Note: You may need to prefix each command with sudo, depending on the permissions of the directory. And during the first install, you do not have an opennac db to backup :-) ):

cd /mysqldata
cp -R opennac opennac.$$  

mysql -u root -p -e "create database opennac;"
mysql -u root -p opennac < tables.sql
mysql -u root -p opennac < values.sql

Configuring database permissions

As of v2.2 RC3, we provide a permissions.sql file, so you don't have to worry about setting permissions by hand.

cd /mysqldata
mysql -u root opennac < permissions.sql 

check /mysqldata/localhost.err for errors. (or whereever your log file resides, i.e. /var/log/mysql.err or syslog - 'grep mysqld /var/log/syslog')

Login to sql to check connectivity:

mysql opennac
          show tables;
          select * from port;

Configure mysql users for local PHP scripts (IMPORTANT)

By default the permissions script above, and the default config.inc use the password 'PASSWORD2' to connect to the database and thus be able to run the daemons.

It is important for security to change the passwords from the default values.

Connect first as root to the mysql database:

mysql -u root -p mysql

Then execute the following commands to change the passwords:

SET PASSWORD FOR inventwrite@localhost=PASSWORD('NEW_PASSWORD2');
SET PASSWORD FOR inventwrite@'%'=PASSWORD('NEW_PASSWORD1');

NEW_PASSWORD2 is the password you'll use in your config.inc file and NEW_PASSWORD1 will be used by the Windows GUI.

Regular housekeeping with cron

The cron tool is where all regular tasks are done to keep the system healthy. The following are recommended regular tasks.

The following crontab entries are for FreeNAC v3.0. For versions prior to this one, you don't need to include the .php extension at the end of the script name.

Master server: Remove 'unknowns' from the DB, that were never authorised and are very old:

0 1 * * 1              /opt/nac/bin/purge_unknowns.php

Clean mysql logs on the 1st per month. In this example, the absolute path of the mysql binary file is the one defined below. Please adjust the path according to your system.

0 6 30  * 1     /usr/bin/mysql -uroot -e "PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 30 DAY);"

Optional: The following are scripts to backup the system in different ways to the second internal disk. These are highly system specifc, make sure you understand, tune and test them (e.g. you will need a '/disk2' partition). Remember to adjust any path according to your system.

0   3    * * 1-5 /opt/nac/bin/dump_ports.php
0   3    * * 1   /usr/bin/mysqlhotcopy --allowold --keepold --regexp=".+" /disk2/backups/mysql 2>&1 | logger

Adapt the MySQL path to your distribution

Database rights [Old: for versions prior to 2.2 RC3]

The following has to be done in the event that you don't have a permissions file (releases prior to 2.2RC3)

There are 3 mysql users needed for accessing the database.

A. Local daemon user for PHP scripts: inventwrite@localhost
B. A user for the remote Delphi Windows GUI: inventwrite@'%'
C. Root is used by the sysadmin for local configuration. By default root
is only allowed from localhost, and has no password. Its is
recommended that you set a root password for mysql root, if the NAC
server login is accessible to several users.

Local daemon user for PHP scripts (set the user/pw in /opt/nac/config.inc):

grant SELECT,INSERT,UPDATE        ON opennac.*       to inventwrite@localhost IDENTIFIED by 'PASSWORD2';
SET PASSWORD FOR inventwrite@localhost = OLD_PASSWORD('PASSWORD2');
grant SELECT,INSERT,UPDATE,DELETE ON opennac.systems to inventwrite@localhost;
grant CREATE TEMPORARY TABLES     ON opennac.*        to inventwrite@localhost;
grant ALL                         ON opennac.vmpsauth to inventwrite@localhost;

Remote delphi Windows GUI user. See also the vmps.ini file on the Windows client.

grant SELECT,INSERT               ON opennac.*       to inventwrite@'%' IDENTIFIED by 'PASSWORD1';
  SET PASSWORD FOR inventwrite@'%' = OLD_PASSWORD('PASSWORD1');

  grant SELECT,UPDATE               ON opennac.oper    to inventwrite@'%' ;
  grant SELECT,UPDATE               ON opennac.config  to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.building to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.location to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.port    to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.switch  to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.vlan    to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.systems to inventwrite@'%';
  grant SELECT,INSERT,UPDATE,DELETE  ON opennac.users   to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.patchcable to inventwrite@'%';
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.vlanswitch to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.cabletype  to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_class to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_class2 to inventwrite@'%' ;
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_os        to inventwrite@'%';
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_os1       to inventwrite@'%';
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_os2       to inventwrite@'%';
  grant SELECT,INSERT,UPDATE,DELETE ON opennac.sys_os3       to inventwrite@'%';

Changing the mysql root password

We normally leave a blank password and expect a dedicated server to be used for FreeNAC. Scripts also expect a balnk password.

Optional: If the NAC server is not exclusively used by one administrator, you may want to set a local root password for mysql. This make administratig more difficult though, and some cron scripts will need to be adapted to provide a password.

mysqladmin -u root password 'new-password'
mysqladmin -u root -h MYHOST password 'new-password'