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