How to migrate the Hue user database from the embedded SQLite database to an external MySQL database.

Document created by sreedhar Employee on Feb 8, 2016
Version 1Show Document
  • View in full screen mode

Author: Sreedhar Alagonda

 

Original Publication Date: November 28, 2014

 

This article describes the process for migrating the user database in Hue 3.6 from the embedded SQLite database to an external MySQL database.  As a prerequisite two users are created ("mapr" & "sreedhar" ) in the SQLlite DB and after the migration these users are present in the MYSQL database.

Process:

- Take backup of embedded SQLite database

i) Check if the Hue webserver is running using lsof. Ex:

root@svs-sc2 hue-3.6.0]# lsof -i :8888

 

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

 

python2.6 32706 mapr 3u IPv4 213764932 0t0 TCP *:ddi-tcp-1 (LISTEN)

ii) Stop the Hue server using maprcli node services. Ex:

[root@svs-sc2 hue-3.6.0]# maprcli node services -name hue -action stop -nodes `hostname`

ii) Confirm that Hue is stopped by verifying as follows:

[root@svs-sc2 hue-3.6.0]# lsof -i :8888  No output.. 

iii) Take a backup of the Hue SQLite DB as follows:

root@svs-sc2 hue-3.6.0]# /opt/mapr/hue/hue-3.6.0/build/env/bin/hue dumpdata > /tmp/hue-sqlite.json  

iv) Edit the hue-sqlite.json output file and remove all JSON objects with useradmin.userprofile in the model field and save the file. You have to remove the following lines as an example:

{"pk": 1, "model": "useradmin.userprofile", "fields": {"creation_method": "HUE", "user": 1, "home_directory": "/user/mapr"}}, {"pk": 2, "model": "useradmin.userprofile", "fields": {"creation_method": "HUE", "user": 2, "home_directory": "/user/sreedhar"}},

iv) Create a backup of /opt/mapr/hue/hue-3.6.0/desktop/desktop.db. Ex:

[root@svs-sc2 hue-3.6.0]# cp /opt/mapr/hue/hue-3.6.0/desktop/desktop.db /root/desktop.db.bak

v) Check the existing SQLite DB users so those users can be validated in the MySQL DB after the migration.

sqlite3 /opt/mapr/hue/hue-3.6.0/desktop/desktop.db

 

SQLite version 3.6.20

 

Enter ".help" for instructions

 

Enter SQL statements terminated with a ";"

 

sqlite> select username from auth_user;

 

mapr

 

sreedhar

- Configure MySQL DB as an external database for HUE

i) Configure MySQL to use the MyISAM storage engine as it is the simplest option for migration. If the INNODB storage engine is used there are additional steps needed to perform the migration.

Change the /etc/my.cnf file as follows: [mysqld]

  datadir=/var/lib/mysql

  socket=/var/lib/mysql/mysql.sock

  bind-address=<ip-address>

  default-storage-engine=MyISAM

ii) Restart the mysql daemon  

[root@svs-yrc1 ~]# /etc/init.d/mysqld restart

iii) Create a new database for Hue and grant privileges to the hue user to manage the database.            

mysql> create database sc1hue;  
mysql> grant all on sc1hue.* to 'sc1hue'@'localhost' identified by '<secretpassword>';

iv) Open "/opt/mapr/hue/hue-3.6.0/desktop/conf/hue.ini" file and modify the "[[database]]" section with the MySQL host and user information. The changes will be similar to the below using the above example.  

[[database]]

 

  engine=mysql

 

  host=10.10.70.56

 

  port=3306

 

  user=sc1hue

 

  password=sc1hue123

 

  name=sc1hue

v)  Once the MySQL DB has been properly setup and Hue is configured run the following command to start populating tables in the new MySQL DB. 

[root@svs-sc2 hue-3.6.0]#/opt/mapr/hue/hue-3.6.0/build/env/bin/hue syncdb --noinput 

vi) Check the Hue DB in MySQL for the new tables. Ex:

mysql> use sc1hue;

 

mysql> show tables ;

 

+--------------------------------+

 

| Tables_in_sc1hue |

 

+--------------------------------+

 

| auth_group |

 

| auth_group_permissions |

 

| auth_permission |

 

| auth_user |

 

| auth_user_groups |

 

| auth_user_user_permissions |

 

| django_admin_log |

 

| django_content_type |

 

| django_openid_auth_association |

 

| django_openid_auth_nonce |

 

| django_openid_auth_useropenid |

 

| django_session |

 

| django_site |

 

| south_migrationhistory |

 

+--------------------------------+

vi) Once the tables are created it is time to migrate all remaining tables from Hue such as the desktop and useradmin tables. Ex:

[root@svs-sc2 hue-3.6.0]# /opt/mapr/hue/hue-3.6.0/build/env/bin/hue migrate  

vii) Check the Hue DB in MySQL again to confirm new tables are created. Ex:

mysql> show tables ;

+-----------------------------------+

 

| Tables_in_sc1hue |

 

+-----------------------------------+

 

| auth_group |

 

| auth_group_permissions |

 

| auth_permission |

 

| auth_user |

 

| auth_user_groups |

 

| auth_user_user_permissions |

 

| beeswax_metainstall |

 

| beeswax_queryhistory |

 

| beeswax_savedquery |

 

| beeswax_session |

 

| desktop_document |

 

………………….

 

………………………………

 

oozie_workflow |

 

| pig_document |

 

| pig_pigscript |

 

| south_migrationhistory |

 

| useradmin_grouppermission |

 

| useradmin_huepermission |

 

| useradmin_ldapgroup |

 

| useradmin_userprofile

- Load backup data from the SQLite DB to the Hue MySQL DB.

i) Delete the rows in the django_content_type table.      

[root@svs-yrc1] #  mysql > DELETE FROM sc1hue.django_content_type

Note: As mentioned above if you are running MySQL with the INNODB storage engine one additional step is required. Drop the foreign key on the auth_permission table in the Hue DB.    

mysql > ALTER TABLE auth_permission DROP FOREIGN KEY content_type_id_refs_id_XXXXXX   

ii) Now load data from the SQLite output file to MySQL.             

[root@svs-sc2 hue-3.6.0]# /opt/mapr/hue/hue-3.6.0/build/env/bin/hue loaddata /tmp/hue-sqlite.json.

Note: If you are running MySQL with the INNODB storage engine on additional step is required. Add a foreign key on the 'auth_permission' table in the Hue DB using the following command:

mysql > ALTER TABLE auth_permission ADD FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);

4) User migration is now complete. Verify that the users have been properly migrated to MySQL by checking the contents of the 'auth_user' table. Also verify the original users can login to Hue now that it is using MySQL.  The login should be successful at this point.   

mysql> select * from auth_user ;

 

+----+----------+------------+-----------+-------+-------------------------------------------------------------------------------+----------+-----------+--------------+---------------------+---------------------+

 

| id | username | first_name | last_name | email | password | is_staff | is_active | is_superuser | last_login | date_joined |

 

+----+----------+------------+-----------+-------+-------------------------------------------------------------------------------+----------+-----------+--------------+---------------------+---------------------+

 

| 1 | mapr | | | | pbkdf2_sha256$10000$UzK70t6tq41M$A/8fynhnkF9QN5/pY4RtCPS0z1eWShj3y/19IjvoWnA= | 0 | 1 | 1 | 2014-11-27 07:08:01 | 2014-11-27 04:57:48 |

 

| 2 | sreedhar | | | | pbkdf2_sha256$10000$a3Ziz0OhLey3$S27qW6CdUhCbo0mQ8WI5nw9mhgSGnwbpwdprQDVAAjo= | 0 | 1 | 1 | 2014-11-27 07:16:49 | 2014-11-27 07:15:52 |

 

+----+----------+------------+-----------+-------+-------------------------------------------------------------------------------+----------+-----------+--------------+---------------------+---------------------+

 

2 rows in set (0.00 sec)

Attachments

    Outcomes