Chris_Kraus

MySQL cookbook for DevTest 8

Blog Post created by Chris_Kraus Employee on Jan 5, 2015

You may have noticed that when you run the default Derby DB with DevTest Solution 8.0 you get a warning and request to use a better performing DB.  Here is quick steps to setup MySQL.  The DevTest specific data is in the Install manual, the MySQL information is not, her is the cheat sheet.

 

Steps

1) Download MySQL DB and JDBC client

2) Install MySQL 5.6.5 or newer

3) Configure MySQL for default code page

4) Create database and user for the enterprise dashboard and server (registry)

configure enterprise dashboard

configure registry settings

5) check for tables and handy commands

 

1—

Download and install MySQL for your platform, current version is 5.6.5 (http://dev.mysql.com/downloads/mysql/)

Download and install JDBC client for MySQL referred to as Connector/J (http://dev.mysql.com/downloads/connector/j/), you will need to put the .jars into your /lib folder

 

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.25-bin.jar to the install LISA_HOME\lib

For the Portal copy into the LISA_HOME\webserver\phoenix\phoenix-1.0.0\WEB-INF\lib

 

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.6

 

 

[mysqld]

collation_server=utf8_unicode_ci

character_set_server=utf8

 

 

4—

Create 2 databases, one for Enterprise dashboard and one for the registry.  My emdatabase is for the enterprise dashboard and the pfdatabase is for registry and pathfinder.  One user is added to the system and assoicated 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 emdatabase

default character set utf8

default collate utf8_unicode_ci;

 

create database pfdatabase

default character set utf8

default collate utf8_unicode_ci;

 

grant usage on *.* to pfuser@localhost identified by 'itkorocks';

grant all privileges on emdatabase.* to pfuser@localhost ;

grant all privileges on pfdatabase.* to pfuser@localhost ;

 

You can 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 pfuser -p

 

use pfdatabase;

use emdatabase;

 

5—

The enterprise dashboard uses local.properties to set the emdatabase.

 

File Name=> local.properties

 

lisadb.pool.dradis.driverClass=com.mysql.jdbc.Driver

lisadb.pool.dradis.url=jdbc:mysql://localhost:3306/emdatabase

lisadb.pool.dradis.user=pfuser

lisadb.pool.dradis.password=itkorocks

 

dradisdb.internal.enabled=false

 

For the rest of DevTest use the site.properties to specify the MySQL database pfdatabase.

 

File Name=> site.properties

 

lisadb.acl.poolName=common

lisadb.broker.poolName=common

lisadb.reporting.poolName=common

lisadb.vse.poolName=common

lisadb.dradiscache.poolName=common

 

 

 

lisadb.pool.common.driverClass=com.mysql.jdbc.Driver

lisadb.pool.common.url=jdbc:mysql://localhost:3306/pfdatabase

lisadb.pool.common.user=pfuser

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       | /usr/local/mysql-5.6.22-osx10.8-x86_64/share/charsets/ |

+--------------------------+--------------------------------------------------------+

8 rows in set (0.00 sec)

 

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

 

mysql show databases;

mysql  show tables in pfdatabase;

 

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

 

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --user=pfuser --password=itkorocks pfdatabase > c:\pfdatabase.sql

Outcomes