Over a million developers have joined DZone.

Migrating a MySQL Application to NuoDB

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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










Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

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

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}