Integrating with a MySQL Server database

Step 1: Installing the MySQL JDBC Driver

Refer to the MySQL website for more information on the currently supported driver versions.

Copy the MySQL JDBC Driver for example, mysql-connector-java-8.0.29.jar to the $OMNIHOME/gates/java directory.

Step 2: Preparing tables for Netcool integration

Preparing AUDIT mode:

Use the scripts bundled in the $OMNIHOME/gates/jdbc2/scripts directory under the directory audit/mysql.

In the command prompt, navigate to the mysql directory and run the following command to create the AUDIT tables in your database:

> run ./mysqlinstall

Run mysqlinstall to install the database tables.

You can check that the tables have been created using the following command:

Login to mysql -u <user_name> -p <password>
 mysql> show databases;
 mysql> use alerts; 
 mysql> show tables;

Step 3: Preparing REPORTING mode:

Use the scripts bundled in the scripts directory under the directory reporting/mysql.

In the command prompt, navigate to the mysql directory and run the `setup_MySql_Reporting.sh` script to create the REPORTING tables in your database:

> ./setup_MySql_Reporting.sh --user <username> --pass <password> --database <database> --details <details table name> --journal <journal table name> --status <status table name> --dropdb <true or false>

Where username is the name of the user that is authorized to connect and run commands on MySQL from the current machine (default is root).

password is the password of that user.

database is the name of the Reporter database (default is reporter)

details is the name of the Details table (default is reporter_details)

journal is the name of the Journal table (default is reporter_journal)

status is the name of the Status table (default is reporter_status)

dropdb specifies that if a Reporter database already exists, drop the database before provisioning a new one. If you wish for the script to create the Reporter database for you, please specify `true` for dropdb. Otherwise if you have already created the Reporter database, please specify false for `dropdb`. Default is false.

You can check that the tables have been created using the following command:

mysql> show databases;  mysql> use reporter;  mysql> show tables;

Create the user account on MySQL that will only have privileges to access and alter the data in the Audit/Reporting database. In the example below, login as the root user on the MySQL database and enter the following commands:

CREATE USER 'netcool'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON reporter.* TO 'netcool'@'%';
FLUSH PRIVILEGES;

This example shows the netcool user is configured with privileges to access the reporter database.

Step 4: Configuring the gateway

The following example shows how to configure for Audit mode:

Gate.Jdbc.Mode: 'AUDIT' # STRING (JDBC gateway mode (AUDIT|REPORTING))

Specify the JDBC connection properties:

Gate.Jdbc.Driver: 'com.mysql.cj.jdbc.Driver' # STRING (JDBC Driver) 
Gate.Jdbc.Url: 'jdbc:mysql://<hostname or IP address of the MySQL Server>:3306/alerts' # STRING (JDBC connection URL) 
Gate.Jdbc.Username: 'netcool' # STRING (JDBC username) 
Gate.Jdbc.Password: 'password' # STRING (JDBC password)

In the above example, the gateway is configured to connect to the ALERTS database using the user netcool.

The following example shows how to configure for Reporting mode:

Gate.Jdbc.Mode: REPORTING # STRING (JDBC gateway mode (AUDIT|REPORTING))

Specify the JDBC connection properties:

Gate.Jdbc.Driver: 'com.mysql.cj.jdbc.Driver' # STRING (JDBC Driver) 
Gate.Jdbc.Url: 'jdbc:mysql://<hostname or IP address of the MySQL Server>:3306/reporter' # STRING 
(JDBC connection URL) 
Gate.Jdbc.Username: 'netcool' # STRING (JDBC username) 
Gate.Jdbc.Password: 'password' # STRING (JDBC password)

In the above example, the gateway is configured to connect to the REPORTER database using the user netcool.