CloudTran Guide - MySQL set up


Contents > 2. Getting Started

2.5 MySQL set up

MySQL is an open source database. It is used in a number of the CloudTran Tutorials. To run the examples as they are currently configured will require the installation of a MySQL envronment.

The following information is designed to get you started with MySQL. It is by no means exhaustive and should you need it the full MySQL documentation can be found at the following location MySQL Documentation

The CloudTran examples were tested with MySQL 5.1.44 but there should be no reason why the examples shouldn't run on other versions of MySQL.

MySQL itself can be downloaded from MySQL Download. Follow the instructions to download and install MySQL.

For the Windows environment the simplest way to install MySQL is to download the 'noinstall' zip (e.g. mysql-noinstall-5.1.44-win32.zip) and simply unzip the file to a convenient location (e.g. C:\MySQL)

2.5.1	MySQL Environment
2.5.2	Using an Existing Installation
2.5.3	Creating the CloudTran User
2.5.4	Creating the CloudTran Databases
2.5.5	To Load the Schema
2.5.6	To Load the Data Files

2.5.1 MySQL Environment

First things first. All the examples use the following common MySQL environment settings:

Host = localhost
Port = 3306
Url = jdbc:mysql://localhost:3306/<database_name>
User = cloudtran
Password = cloudtran

In addition each tutorial example has its own database ( the <database_name> part of the Url above). For example, the database names for the existing examples are shown below.

Example name Database Name

Congestion charge congestioncharge

Demo demo

Entity entityExample

Entity entityExample

Entity entityExample

Warehouse warehouseExample

2.5.2 Using an Existing Installation

If you want to use an existing installation of MySQL you have two options:

Alter your installation by:
- Ensuring MySQL is running on port 3306
- Adding the appropriate database
- Adding the 'cloudtran' user
Change the data source definition details in each of the examples to match your environment (see the tutorials for details).

In either case you should use a separate database for each example/tutorial simply to ensure there is no conflict on table names.

2.5.3 Creating the CloudTran User

To run the examples as they are currently configured will require the creation of a new user 'cloudtran'. The password for this user will also be 'cloudtran'. To create the user you will need to be logged in to MySQL as either 'root' or as a user with grant privileges. To login open a command box or a shell and login using the following command

mysql -u root -p

You will be prompted to enter the root password. Once in MySQL you should see something like:

Enter the following commands within the MySQL shell This will create a user 'cloudtran' with a password 'cloudtran'. This user will only be able to be used when connecting from localhost - i.e. MySQL will be used

CREATE USER 'cloudtran'@'localhost' IDENTIFIED BY 'cloudtran';
GRANT ALL PRIVILEGES ON *.* TO 'cloudtran'@'localhost' WITH GRANT OPTION;

If you plan to run CloudTran on more than one machine or if you plan to put MySQL on a separate machine you will need to enter the following, which will allow the user 'cloudtran' to connect from machine remote to the one MySQL is running on.

CREATE USER 'cloudtran'@'%' IDENTIFIED BY 'cloudtran';
GRANT ALL PRIVILEGES ON *.* TO 'cloudtran'@'%' WITH GRANT OPTION;

NOTE: In both cases the user 'cloudtran' will be set up will all privileges. If you wish to reduce the 'cloudtran' privileges see the MySQL documentation.

To test the user 'cloudtran' use the following command. Note: there is no space between the '-p' and 'cloudtran'

mysql -u cloudtran -pcloudtran

2.5.4 Creating the CloudTran Databases

Having got MySQL up and running you will need to set up the databases for the examples. There are two ways you can do this, but both will require you to create the database in MySQL. As mentioned previously there are two existing tutorials. Check for additional in the next Chapter - Tutorials.

Entity Tutorial : entityExample
Warehouse Tutorial : warehouseExample

To create the database in MySQL logon using the user 'cloudtran' set up in the previous section

mysql -u cloudtran -pcloudtran

To create the database in MySQL logon using the user 'cloudtran' set up in the previous section and then type in the following command changing <database_name> for the correct database name. The response should something along the lines of 'Query OK, 1 row affected (0.03 sec)'.

create database <database_name>;

At this point you can now load the schema and optionally load a data set.

Loading the schema is essential if you intend to install and run the tutorials - see Tutorials for more information. Loading data into the database before running the tutorials is not essential. Those tutorials that have entities will write data into the Spaces and hence the data source automatically.

However by default the data from the data source is loaded at start of into the Spaces used in the tutorials. By loading some data into the database you will be able to see this feature in action.

The schema files and data files can be found at (.sql) can be found in the main project of the releveant tutorial. For example the Entity Tutorial can be found at:

eclipse/plugins/com.cloudtran.builder_x.y.z/jeewiz/examples/CloudTran/Entities/workspace/EntityTutorial

where X, Y and Z represent the product major, minor and release numbers.

The relevant files are

Schema file    : entDS_entityExample.sql
Data file      : entityTutorialData.sql

The Warehouse sql files can be found at

eclipse/plugins/com.cloudtran.builder_x.y.z/jeewiz/examples/CloudTran/Warehouse/workspace/Warehouse

and relevant files ares

Schema files   : ds1_warehouseexample.sql and ds2_warehouseexample.sql
Data file      : warehouseTutorialData.sql

Note: The schema files are generated automatically based on the entity structure in the tutorial's model. The data files are not generated automatically. If the model is changed such that the entities are altered then the schema files will change but the data files will not.

2.5.5 To Load the Schema

To load the schema run the following command

mysql -u cloudtran -pcloudtran -D <database_name> < <schema_file>

Note: To do it this way you will need to edit the schema file and remove all the 'DROP TABLE' commands

Alternatively login into MySQL using the user 'cloudtran'. Open the schema file in a text editor and select all of the schema and paste it into the MySQL shell.

2.5.6 To Load the Data Files

Loading the data can be done in a similar fashion

mysql -u cloudtran -pcloudtran -D <database_name> < <data_file>

Alternatively login into MySQL using the user 'cloudtran'. Open the data file in a text editor and select all of the schema and paste it into the MySQL shell.