Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Migrating a MySQL Application to NuoDB

DZone's Guide to

Migrating a MySQL Application to NuoDB

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

In this TechBlog article, I will show you how to migrate a MySQL application to NuoDB.  I will use the Saiku Server OLAP application to demonstrate how to do it.  Saiku is a fully-featured Java Web application, hosted in Tomcat, that uses the Mondriananalytics engine to perform some useful analytical processing. JDBC is used to connect to the underlying database.

In this example, I will setup the Saiku Server application, first using MySQL.  Next, I will use the NuoDB Migration tool to migrate that MySQL database to NuoDB.  Lastly, I will run Saiku Server application using NuoDB.

Starting from a clean Ubuntu 12 machine:

Install Java and MySQL

In this example, I set the MySQL ‘root’ user password to be ‘root’:

$ sudo apt-get update
$ sudo apt-get install openjdk-7-jre
$ sudo apt-get install mysql-server   
$ sudo apt-get install libmysql-java

Set JAVA_HOME and CLASSPATH

You must set the JAVA_HOME and CLASSPATH environment variables, which are needed for the Saiku Server:

$ export JAVA_HOME=$(readlink -f /usr/bin/java | sed "s:bin/java::")
$ export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java.jar

Download Saiku Server 2.4

Next, download the Saiku Server from the analytical-labs website and extract it:

$ cd ${HOME}
$ wget http://analytical-labs.com/downloads/saiku-server-foodmart-2.4.tar.gz
$ tar -xzf saiku-server-foodmart-2.4.tar.gz

Creating the MySQL foodmart database

Foodmart is an example data set bundled with Saiku that represents a small warehouse of a commerce-oriented business, featuring sales, payroll and other data.  The Saiku Server does not come with a MySQL version of that data, but you can download one from our website:

$ wget www.nuodb.com/latest/foodmart.mysql.sql.gz

Next unzip, and create an empty foodmart MySQL database, and then load it:

$ gunzip foodmart.mysql.sql.gz
$ echo "create database foodmart;" | mysql -uroot -proot
$ mysql -uroot -proot foodmart < foodmart.mysql.sql

Change Saiku Server to use port 8081

By default the NuoDB web console and Saiku Server both use localhost:8080.  To avoid the conflict, I will change Saiku Server to use port 8081 instead.  Edit: ~/saiku-server/tomcat/conf/server.xml and change the Connector port to “8081″ on Line 69:

<Connector port="8081" protocol="HTTP/1.1"

Change Saiku Server to use MySQL Data Source

Edit ~/saiku-server/tomcat/webapps/saiku/WEB-INF/classes/saiku-datasources/foodmart:

type=OLAP
name=foodmart-mysql
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost/foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=res:foodmart/FoodMart.xml;
username=root
password=root

Start Saiku Server

$ cd ~/saiku-server
$ chmod +x start-saiku.sh
$ ./start-saiku.sh

Run Saiku (MySQL)

Open a web browser on http://localhost:8081

Login using admin/admin

In the “Cubes” dropdown, select “FoodMart->Sales”. In Dimensions, select “Gender->Gender” and drag & drop in “Columns”. Then in Dimensions, select “Store->Store State” and drag & drop in “Rows”.

Stop the current Saiku Server instance

Now that we have a Saiku working with MySQL, let’s migrate it to NuoDB.  First stop the current running Saiku Server instance:

$ ./stop-saiku.sh

Download, Install NuoDB, run the quickstart

Download NuoDB http://www.nuodb.com/download, install NuoDB, and run the NuoDB quickstart.

$ sudo dpkg -i ~/Downloads/nuodb-1.1.1.linux.x64.deb
$ cd /opt/nuodb
$ ./run-quickstart
...
SQL> exit;

Migrate MySQL database to NuoDB

The actual migration is just three simple steps.  First migrate the database schema from MySQL to NuoDB:

$ /opt/nuodb/tools/migrator/bin/nuodb-migrator schema --source.driver=com.mysql.jdbc.Driver --source.url=jdbc:mysql://localhost:3306/foodmart --source.username=root --source.password=root --target.url=jdbc:com.nuodb://localhost/test?schema=FOODMART --target.username=dba --target.password=goalie

At this point we have migrated the schema and table information from MySQL to NuoDB.  The second step is to dump the data from the MySQL foodmart database:

$ /opt/nuodb/tools/migrator/bin/nuodb-migrator dump --source.driver=com.mysql.jdbc.Driver --source.url=jdbc:mysql://localhost:3306/foodmart?zeroDateTimeBehavior=convertToNull --source.username=root --source.password=root --output.type=csv --output.path=/tmp/foodmart.cat

At this point, the table data from the MySQL database has been stored using “comma separated value” (csv) format, in the /tmp directory.  The final step is to load that data into the NuoDB database:

$ /opt/nuodb/tools/migrator/bin/nuodb-migrator load --target.url=jdbc:com.nuodb://localhost/test?schema=FOODMART --target.username=dba --target.password=goalie --input.path=/tmp/foodmart.cat

Copy nuodbjdbc.jar into saiku-server

$ cp /opt/nuodb/jar/nuodbjdbc.jar $HOME/saiku-server/tomcat/webapps/saiku/WEB-INF/lib

Change Saiku Server to use NuoDB Data Source

Edit ~/saiku-server/tomcat/webapps/saiku/WEB-INF/classes/saiku-datasources/foodmart:

type=OLAP
name=foodmart-nuodb
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:com.nuodb://localhost/test?schema=foodmart;JdbcDrivers=com.nuodb.jdbc.Driver;Catalog=res:foodmart/FoodMart.xml;
username=dba
password=goalie

Start Saiku Server

$ cd ~/saiku-server
$ ./start-saiku.sh

Run Saiku (NuoDB)

Open a web browser on http://localhost:8081

Login using admin/admin

Just like above, in the “Cubes” dropdown, select “FoodMart->Sales”. In Dimensions, select “Gender->Gender” and drag & drop in “Columns”. Then in Dimensions, select “Store->Store State” and drag & drop in “Rows”.

saiku_nuodb










Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:

Published at DZone with permission of Seth Proctor, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}