MySQL configuration

Introduction

It is presumed that mysql is up and running, but that the opennac DB does not exist yet.

/etc/my.cnf settings

Copy /etc/my.cnf from your MASTER machine and adapt. The key settings for a server called "vmps2" would be as follows.

datadir=/var/lib/mysql
log-error=/var/lib/mysql/mysqld.log
log-bin=vmps2-bin
log-warnings
server-id       = 20                     [adapt if more than one slave]
report-host=vmps2                        [adapt if more than one slave]
replicate-do-db= opennac
replicate-wild-ignore-table= opennac.vmpsauth%
relay-log=vmps2-relay-bin 

It is important that server-id and report-host are unique with the group of NAC servers. So set it to "vmps3" and "30" for a third server (2nd slave) etc.

On Ubuntu (7.10), my.cnf contains a log_bin, not log-bin, and expects an absolute patch to the logfile name.

Mysqld startup

Ensure that /etc/init.d/mysql exists, and automatic start is enabled ('chkconfig mysql on').

Initial FreeNAC empty dataset

Install an initial set of empty FreeNAC tables for the 'opennac' database, backing up the existing tables first:

cd /mysqldata
cp opennac opennac.$$  
cp /opt/nac/contrib/opennac_db.tar.gz .
tar xvzf opennac_db.tar.gz tables.sql
mysql -se "create database opennac;"
mysql opennac < tables.sql
 

Configuring the empty opennac DB

Empty the vmpsauth (local):

  DELETE FROM vmpsauth;

Configuring the DB permissions

The DB in only accessed by local scripts, it is not used by the GUis, so permissions can be mucht tighter than on the master.

-- Set passwords for script access
-- Local PHP server script user, PASSWORD2 is set in 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 ALL     ON opennac.vmpsauth to inventwrite@localhost; 

Troubleshooting

Try to start mysql:

    /etc/init.d/mysql start

If you have errors, check the log (on Ubuntu, see syslog, e.g. /var/log/syslog) :

    more /mysqldata/mysqld.log

Check that mysql client works, i.e. can connect to the DB:

mysql mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>  

Make sure that file permissions are right. e.g.

  chown -R mysql:mysql /mysqldata/* /var/lib/mysql
  chgrp -R mysql /usr/local/mysql/*  

Notes

• On some Suse systems it might be necessary to copy the mysql client library (for PHP) from your master

  scp /usr/lib/libmysqlclient.so.14 vmps2:/usr/lib