• Created by Unknown User (cal), last modified by Unknown User (jaredg) on Jul 30, 2007

IEPM-BW Database Setup

A MySQL system has its own set of users which are distinct from the unix users, although the names can be the same. The recommended users are:

  1. iepm - this is the account the monitoring system runs with
  2. readonly - this is a readonly account for general access to the data
  3. apache (or nobody) - the account used by httpd to access the database
  4. cal (or other user) - the administrator account
  5. root - used for MySQL system administration

I remind that these accounts are Mysql user accounts and should not be confused with system user account.

The accounts are defined by issuing grants to the account for accessing the data base.
Create a file named pws in /home/iepm/mysql i.e.

touch /home/iepm/mysql/pws

edit the file and put the password of your iepm mysql account in it.
Create the IEPM data base. Log into MySQL with the root account and password and issue the following command:

create database iepm;

The following grant commands can be set up in a text file for 'batch' entry into MySQL, or they can be entered one by one. The root account and password must be known to set up the grants. Make sure, you put the passwords in and don't use the string 'password'. Note the host name component of the grant. All these forms may not be necessary, however I have found that they are for various means of access.

#For mysql administrator ('cyrus' in this case) grant all on *.* to cyrus@localhost identified by 'password' with grant option;
grant all on *.* to cyrus@202.83.167.108 identified by 'password' with grant option;
grant all on *.* to cyrus@iepm-ncp identified by 'password' with grant option;
grant all on *.* to cyrus@'iepm-ncp.ncp.edu.pk identified by 'password' with grant option;
grant file on *.* to cyrus;

# For the iepm account used by iepm to load data into the data base:
grant all on iepm.* to iepm@localhost identified by 'password';
grant all on iepm.* to iepm@202.83.167.108 identified by 'password';
grant all on iepm.* to iepm@iepm-ncp identified by 'password';
grant all on iepm.* to iepm@'iepm-ncp.ncp.edu.pk' identified by 'password';
grant file on *.* to iepm;

# For the readonly account (report generation) - no password
grant select on iepm.* to readonly@localhost;
grant select on iepm.* to readonly@'202.83.167.108';
grant select on iepm.* to readonly@iepm-ncp;
grant select on iepm.* to readonly@'iepm-ncp.ncp.edu.pk';
grant file on *.* to readonly;

# For the web server account (may be nobody or apache or other) - no password
grant select on iepm.* to apache@localhost;
grant select on iepm.* to apache@'202.83.167.108';
grant select on iepm.* to apache@iepm-ncp;
grant select on iepm.* to apache@'iepm-ncp.ncp.edu.pk';
grant file on *.* to apache;

Creating Tables

Create the tables in iepm database. The scripts of these tables can be found here. However you can use the SQL queries in the files in create tables directory. Create tables by giving these files as input to mysql. Please check if you might need to change nodes.txt in case you have different paths for ping, grep, traceroute etc. Use the command

mysql -u root -p > file_name Database name

For example, for creating monhost table in iepm database

mysql -u root -p > monhost.txt iepm

It will prompt you for password; enter your mysql root password.

Loading the IEPM Database

Next comes the loading of data in to the database tables. Look at the following links of tables at SLAC IEPM node to have some idea of the data to be inserted in the database tables.

http://www.slac.stanford.edu/comp/net/iepm-bw.slac.stanford.edu/monitoring-hosts.html
http://www.slac.stanford.edu/comp/net/iepm-bw.slac.stanford.edu/NODESPECS.html
http://www.slac.stanford.edu/comp/net/iepm-bw.slac.stanford.edu/targets-iepm-bw.slac.stanford.edu.html
http://www.slac.stanford.edu/comp/net/iepm-bw.slac.stanford.edu/toolspecs.html
http://www.slac.stanford.edu/comp/net/iepm-bw.slac.stanford.edu/plotspecs/pltspecs-entries.html

The scripts for populating the database tables are located in v3src/mysql. You can load tables with data using CSV files. Other way you can also create your own queries, put them in a file and give as input to mysql using the command

mysql -u root -p  > file_name  databasename

At the moment, I recommend creating table by using queries.
For reference, see the following links

Populating the NODES Table
Populating the GROUP Tables
Populating the MONHOST Table
Populating the TOOLSPECS Table

For more information on the iepm-bw mysql table setup see IEPM Database Table Summary.

  • No labels