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

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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










It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}