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