Moving Oozie to MySQL with Ambari

[#1]

The following provide directions to swap out the default Derby DB in Ambari for Ooize to Mysql.

1. followed *some* steps from this for preparing to create the MySQL db creation - http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.1-latest/bk_installing_manually_book/content/rpm-chap8-3.html
2. Setup databse (dropped the existing DB first)
   1. $ mysqldump -u oozie -p -h <host> oozie > oozie.dump.sql
   2. $ mysql -u oozie -p -h <host> -e "drop database oozie"
   3. $ sudo mysql
   4. mysql> create database oozie;
   1. Query OK, 1 row affected (0.03 sec)
3. mysql> grant all privileges on oozie.* to 'oozie'@'localhost' identified by 'oozie'
   1. Query OK, 0 rows affected (0.03 sec)
4. mysql> grant all privileges on oozie.* to 'oozie'@'%' identified by 'oozie';
   1. Query OK, 0 rows affected (0.03 sec)
5. mysql> exit
6. backup oozie-site.xml
7. modify the following in oozie-site.xml
   1. change db info to mysql (note: use the proper password for the grants: pass=oozie). This is used for creating the database. We need to manually change it because Ambari disables this options once it's been set.

      <property>
      <name>oozie.service.JPAService.jdbc.driver</name>
      <value>com.mysql.jdbc.Driver</value>
      </property>
      <property>
      <name>oozie.service.JPAService.jdbc.url</name>
      <value>jdbc:mysql://<hostname>:3306/oozie</value>
      </property>
      <property>
      <name>oozie.service.JPAService.jdbc.username</name>
      <value>oozie</value>
      </property>
      <property>
      <name>oozie.service.JPAService.jdbc.password</name>
      <value>oozie</value>
      </property>												
      

8. copy mysql-connector-java.jar to oozie/libext (/var/lib/oozie/)
9. install the Oozie DB
   1. sudo -u oozie /usr/lib/oozie/bin/ooziedb.sh create -run
10. Check out the Ambari Oozie settings via the CLI API

    /var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> get 127.0.0.1 <clustername> oozie-site | python -m json.tool
    cat <(echo {) <(/var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> get 127.0.0.1 <clustername> oozie-site|tail -n +4) <(echo })|python -m json.tool
    

11. Update the Oozie properties in Ambari
    1. create a tmp dir, e.g. /tmp/oozie and cd to it
    2. Get the global config

    /var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> get 127.0.0.1 <clustername> global global.json

12. Update the following:
    1. "oozie_database" : "Existing MySQL Database"
    2. "oozie_database_type" : "MySQL"
13. Update the global config
    1. /var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> set 127.0.0.1 <clustername> global global.json
14. Get the oozie-site config
    1. /var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> get 127.0.0.1 <clustername> oozie-site oozie-site.json
15. Update the following:
    1. "oozie.db.schema.name" : "oozie"
    2. "oozie.service.JPAService.jdbc.driver" : "com.mysql.jdbc.Driver"
    3. "oozie.service.JPAService.jdbc.url" : "jdbc:mysql://<hostname>:3306/oozie"
16. Update the oozie-site config
    1. /var/lib/ambari-server/resources/scripts/configs.sh -u admin -p <password> set 127.0.0.1 <clustername> oozie-site oozie-site.json

[#2]

This came in quite handy last week at a customer poc