search cancel

MySQL Cookbook for DevTest

book

Article ID: 76812

calendar_today

Updated On:

Products

CA Application Test CA Continuous Application Insight (PathFinder) Service Virtualization

Issue/Introduction

Here is quick steps to setup MySQL

The DevTest specific data is in the Install manual, the MySQL information is not, here is the cheat sheet.

Cause

N/A

Environment

All supported DevTest releases.

All supported MySQL releases and drivers.

Resolution

Steps
1) Download MySQL DB and JDBC client (this example using JDBC Client jar version 5.1.45)
2) Install MySQL supported for the version of DevTest you are on (this example is MySQL 5.7)
3) Configure MySQL for default code page
4) Create database and user for IAM, Enterprise Dashboard and Registry
configure IAM
configure Enterprise Dashboard
configure Registry 
5) check for tables and handy commands

1—
Download and install MySQL for your platform from MySQL Community Downloads
Download and install JDBC client for MySQL referred to as Connector/J from Connector/J , you will need to put the jar file into your /IdentityAccessManager/database/drivers, /lib/shared and /lib/dradis folders.

2—
Install on disk or accept default, the following instructions are based on default
You need put the JDBC client jar into LISA classpath
Copy the mysql-connector-java-5.1.45-bin.jar to the install LISA_HOME\lib\shared, LISA_HOME\lib\dradis and LISA_HOME\IdentityAccessManager\database\drivers folders

3—
The database needs to have the default code page set for UTF8, you will need the following files on Windows or OSX.

File Name => my.ini
location OSX=> /usr/local/mysql
location Windows => C:\Program Files\MySQL\MySQL Server 5.7

[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8

4—
Create 3 databases, one for IAM, one for Enterprise Dashboard and one for the Registry. My iamdatabase is for IAM, edbdatabase is for the Enterprise Dashboard and the regdatabase is for the Registry. One user is added to the system and associated with each database. The default login for MySQL is root with no password. Once you have set a password you will need a -p to state you are specifying one.

mysql -u root

The following command are submitted via the command line utility mysql

create database iamdatabase
default character set utf8
default collate utf8_unicode_ci;

create database edbdatabase
default character set utf8
default collate utf8_unicode_ci;

create database regdatabase
default character set utf8
default collate utf8_unicode_ci;

grant usage on *.* to [email protected] identified by 'itkorocks';

grant all privileges on iamdatabase.* to [email protected] ;
grant all privileges on edbdatabase.* to [email protected] ;
grant all privileges on regdatabase.* to dt[email protected] ;

**Make sure you give access to all the machines that need access to MySQL Database to avoid any host related issues. 

for example:
CREATE USER 'dtuser'@'localhost' IDENTIFIED BY 'password'; ( if you want user to connect from same machine use localhost)
CREATE USER 'dtuser'@'hostnamet' IDENTIFIED BY 'password'; ( 'hostname' tells MySQL what hosts the user can connect from)
CREATE USER 'dtuser'@'%' IDENTIFIED BY 'password'; (If you're allowing network access and want a connection from any host, then use %)

Now login to the mysql command line with this command, the password will be prompted. To switch between the two databases use the “use command”

mysql -u dtuser -p

use regdatabase;
use edbdatabase;
use iamdatabase;

5—
IAM uses iam.properties to set the iamdatabase.

File Name=> iam.properties

iam.db.vendor=mysql
iam.db.jdbc.driver.class=com.mysql.jdbc.Driver
iam.db.url=jdbc:mysql://localhost:3306/iamdatabase
iam.db.user=dtuser
iam.db.password=itkorocks
iam.db.jdbc.driver.path=<Fully qualified path where connector jar is>

 

The Enterprise Dashboard uses dradis.properties to set the edbdatabase.

File Name=> dradis.properties

dradis.db.internal.enabled=false

dradis.db.driverClass=com.mysql.jdbc.Driver
dradis.db.url=jdbc:mysql://localhost:3306/edbdatabase
dradis.db.user=dtuser
dradis.db.password=itkorocks

 

The Registry uses the site.properties to specify the MySQL database regdatabase.

File Name=> site.properties

lisadb.acl.poolName=common
lisadb.broker.poolName=common
lisadb.reporting.poolName=common
lisadb.dradiscache.poolName=common

lisadb.pool.common.driverClass=com.mysql.jdbc.Driver
lisadb.pool.common.url=jdbc:mysql://localhost:3306/regdatabase
lisadb.pool.common.user=dtuser
lisadb.pool.common.password=itkorocks

lisadb.internal.enabled=false

5—
Once you start up the database you can see the default code pages, they should look similar to this:

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)




To see a database and tables use this while logged in as root:

show databases;
show tablesin iamdatabase;
show tablesin edbdatabase;
show tablesin regdatabase;

To export or backup database to send to someone use this:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump --user=dtuser --password=itkorocks regdatabase > c:\regdatabase.sql



 

Additional Information

Recommended to install MySQL Workbench if running on a Windows platform or a Linux platform that accepts GUI, it is much easier to create schemas and users.