You are here

Migrating a MySQL Application to NuoDB

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 Mondrian analytics 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
$ 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

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:


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
$ ./


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:

$ ./



Download, Install NuoDB, run the quickstart

Download NuoDB, 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/

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/


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
$ ./


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



Add new comment