0. MySQL configuration
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
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
report-host = vmps1
server-id = 10 [10 for master, 20 for slave1, 20 for slave 2 etc..]
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_offset = 1 [1 for vmps1, 2 for vmps2, 3 for vmps3 ...]
Ensure the mysql user can write to the database files (this is usually the case).
chown -R mysql /mysqldata /var/lib/mysql
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:
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
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 :-) ):
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.
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:
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');
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'