Sun N1 Grid Engine 6.1 Installation Guide

Setting Up the Database Software

Database software must be installed and configured properly before you can install and use the Accounting and Reporting Console. This section describes how to set up the database software using either a PostgreSQL database, a MySQL database, or an Oracle database.

Using PostgreSQL Database Software with N1 Grid Engine

Detailed information on the PostgreSQL database software can be found in the Postgres documentation.

How to Start the Database Server

Once the PostgreSQL software is installed, you can start the database server.

Before You Begin

Download, compile, and install the PostgreSQL database software. Create a user account to own the database processes. Usually, this user is postgres. Add the PostgreSQL bin directory and necessary LD_LIBRARY_PATH settings to your environment.

Create a home directory for the postgres user.

In this example, the home directory is /space/postgres/data.

% mkdir -p /space/postgres/data
% useradd -d /space/postgres  postgres 
% chown postgres /space/postgres/data
% su - postgres

Continue as described in the PostgreSQL documentation to set up a database.

> initdb -D /space/postgres/data

creating directory /space/postgres/data... ok
creating directory /space/postgres/data/base... ok
creating directory /space/postgres/data/global... ok
creating directory /space/postgres/data/pg_xlog... ok
creating directory /space/postgres/data/pg_clog... ok
creating template1 database in /space/postgres/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:
   postmaster -D /space/postgres/data
   or
   pg_ctl -D /space/postgres/data -l logfile start

Make the following changes to the pg_hba.conf file.

This change permits unrestricted and password free access to the database superuser postgres but requires md5 encrypted passwords for all other database users. Replace nnn.nnn.nnn with your subnet address without the trailing 0. You also can add access rules on a per-host basis by adding similar lines with host IP addresses.
```
# TYPE  DATABASE  USER  IP-ADDRESS     IP-MASK         METHOD
local   all       postgres                             trust
local   all       all                                  md5
# IPv4-style local connections:
#host    all       all   nnn.nnn.nnn.0  255.255.255.0  md5
```

Make the following changes to the postgresql.conf file, to enable TCP/IP access from other hosts.

Note –
Ensure that the value of shared_buffers is at least twice the value of max_connections.
```
tcpip_socket = true
max_connections = 40   (increase if necessary)
```

Start the database.

In this example, -i enables TCP/IP communication, and -S is for silent mode.
> postmaster -S -i

Verify the installation.

As the postgres user, try the following commands:

% su - postgres

> createuser -P test_user
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

> createdb -O test_user -E UNICODE test
CREATE DATABASE

Execute commands as the database super user.

 > psql test
Welcome to psql 7.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# create table test (x int, y text);
CREATE TABLE
test=# insert into test values (1, 'one');
INSERT 16982 1
test=# insert into test values (2, 'two');
INSERT 16983 1
test=# select * from test;
x |  y
---+------
1 | one
2 | two
(2 rows)

test=# \q

 > psql -U test_user test
Password: 
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
test=>

After you have successfully tested your database, you should proceed to the next task, How to Set Up a PostgreSQL Database.

How to Set Up a PostgreSQL Database

Create the owner of the database.

> createuser -P arco_write
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

Create the database for accounting and reporting.
> createdb -O arco_write arco CREATE DATABASE

Create a database user for reading the database.

If you do not use the arco_read user, you need to modify the privileges script that is used in the next step.

> createuser -P arco_read
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

Using Oracle Database Software With N1 Grid Engine

How to Set Up an Oracle Database

Ask your database administrator for an instance of an Oracle database.

You need two database users for this instance, arco_write and arco_read. The arco_write user must be able to create or alter tables, views, and indexes. Privileges for the arco_read user are set later by an SQL script.

Ask your database administrator for the connection string to the database.

Using MySQL Database Software with N1 Grid Engine

The Accounting and Reporting Console uses views, As a result, the console supports MySQL database version 5.0.x and higher. For more information on the MySQL database software, see the MySQL documentation .

Case Sensitivity in MySQL Database

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. Therefore, database and table names are case sensitive in most varieties of UNIX, and not case sensitive in Windows.

How to Install MySQL Database on UNIX Systems

Download the appropriate MySQL software for your system from http://www.mysql.com.

The standard installation directory for UNIX systems is /usr/local/mysql. If you install the software into a different directory, you have to change the settings for the scripts provided in the package.

Note –
ARCo is a Java web-based application and needs the Java^TM DataBase connectivity (JDBC^TM) driver for converting JDBC calls into the network protocol used by the MySQL database. You can download the JDBC driver from http://www.mysql.com/products/connector.

Create a symbolic link from the installation directory to mysql.
# ln -s $installation_directory/mysql-standard-5.0.26-solaris10-i386 mysql
The mysql directory contains several files and subdirectories:
- The bin subdirectory contains client programs and the server.
  - The MySQL server and server startup scripts:
    - mysqld is the MySQL server.
    - mysqld_safe, mysql.server, and mysqld_multi are server startup scripts.
    - mysql_install_db initializes the data directory and the initial databases.
    - mysqlmanager is the MySQL instance manager.
  - Client programs that access the server:
    - mysql is a command-line client for executing SQL statements interactively or in batch mode.
    - mysqladmin is an administrative client.
    - mysqlcheck performs table maintenance operations.
    - mysqldump and mysqlhotcopy make database backups.
    - mysqlimport imports data files.
    - mysqlshow displays information about databases and tables.
  - Utility programs that operate independently of the server:
    - myisamchk performs table maintenance operations.
    - myisampack produces compressed, read-only tables.
    - mysqlbinlog is a tool for processing binary log files.
    - perror displays the meaning of error codes.
- The scripts subdirectory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.

Add a login user and group for mysqld.

# groupadd mysql
# useradd -g mysql mysql

Change the ownership of program binaries to root and ownership of the data directory to the user that you use to run mysqld.
# chown -R root . # chown -R mysql data # chgrp -R mysql .

Create the MySQL grant tables.
# scripts/mysql_install_db --user=mysql

Start the MySQL server.
# bin/mysqld_safe --user=mysql &

Assign the root password.

# ./bin/mysqladmin -u root password 'new-password'
# ./bin/mysqladmin -u root -h ${hostname} password 'new-password'

MySQL Installation Tips

To start the MySQL server at boot time, copy support-files/mysql.server to /etc/init.d and link it to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. If MySQL is not installed in /usr/local/mysql., edit the file to change the basedir and datadir variables.
Add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.

How to Set Up Accounting and Reporting Database in MySQL

Create user and grant privileges for arco_read and arco_write.

mysql> GRANT ALL on *.* to 'arco_write'@'<hostname>' identified by '<password>' with GRANT OPTION;
mysql> GRANT ALL on *.* to 'arco_write'@'localhost' identified by '<password>' with GRANT OPTION;
mysql> GRANT ALL on *.* to 'arco_write'@'%' identified by '<password>' with GRANT OPTION;

mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'<hostname>' identified by '<password>';
mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'localhost' identified by '<password>';
mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'%' identified by '<password>';

Create the accounting and reporting database.
```
mysql> CREATE DATABASE ARCO;
```

Install the Accounting and Reporting Software.