Migrate oozie from derby to mysql

  1. Install and make the mysql service up.

#/etc/init.d/mysqld status

2. Stop oozie

#/etc/init.d/oozie stop

3. Take a backup of the derby database using the tool JdbcTool (http://quuxo.com/products/jdbctool/)

Download the tool from the site and decompress it.

#tar xvf jdbctool-1.0.tar

#cd jdbctool-1.0/bin/

4. Connect to derby database using the default username SA

#./jdbctool -u sa jdbc:derby:/var/lib/gphd/oozie/oozie-db/

To check that it is working, you can count rows we need to migrate:

To check that it is working, you can count rows we need to migrate:

derby:/ext/oozie-db/> select count(*) from WF_ACTIONS
| 1      |
| 225592 |

5. Take the database backup

#./jdbcdump -u sa jdbc:derby:/var/lib/gphd/oozie/oozie-db/ >/data/Backups/oozie.sql

6. Prepare OOZIE and MySQL DB

Oozie uses jdbc, so, you’ll need proper connector for mysql. Grab it and place the jar files in libext folder of oozie and rebuild war file with

#bin/oozie-setup.sh prepare-war

Then modify config file (but make copy of old config file, just in case). There are four options related to DB connectivity:

  1. oozie.service.JPAService.jdbc.driver, set it tocom.mysql.jdbc.Driver
  2. oozie.service.JPAService.jdbc.url set it tojdbc:mysql://MYSQL_HOST/DB_NAME (for example «jdbc:mysql://localhost/oozie»)
  3. oozie.service.JPAService.jdbc.username, set it to mysql user name
  4. oozie.service.JPAService.jdbc.password for password

After that, you need to create oozie schema in mysql (and check that it can connect to DB) by issuing the command:

#bin/oozie-setup.sh db create -run

7. Restore data from derby

#cat oozie.sql | sed -r “/^INSERT INTO/s/VALUES \(([^,]+)/VALUES (‘\1’/g” | mysql –show-warnings -uhadoop  oozie

8 Start oozie again

#/etc/init.d/oozie start

#/etc/init.d/oozie status


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s