Over a million developers have joined DZone.

Migrating a MySQL Application to NuoDB

DZone's Guide to

Migrating a MySQL Application to NuoDB

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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


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:


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:


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


Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}