Configuring Hue 3.5 to use MySQL for the Hue user database

Document created by mufeed on Feb 13, 2016
Version 1Show Document
  • View in full screen mode

Author: Mufeed Usman

 

Original Publication Date: August 25, 2014

 

This will setup Hue 3.5 with MySQL as it's backend database.

After successful login to Hue UI using IP:8888 query the default Hue database as shown below to ensure it is functional.

[root@mu-node-69 server]# sqlite3 /opt/mapr/hue/hue-3.5.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

Now let us install MySQL and have the required configurations in place.

 

Installing MySQL

[root@mu-node-69 server]# yum install mysql-server

 

[root@mu-node-69 server]# /etc/init.d/mysqld start

 

[root@mu-node-69 server]# mysqladmin -u root password root

Preparing the new Hue database in MySQL and migrating data from sqlite. In the 'grant all ...' command below 'mu-node-69' to be replaced by the hostname of the Hue node.

[root@mu-node-69 server]# mysql -uroot -proot

 

root@localhost:[(none)]> create database hue;

Query OK, 1 row affected (0.00 sec)

 

root@localhost:[(none)]> show databases;

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

| Database |

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

| information_schema |

| hue |

| mysql |

| test |

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

4 rows in set (0.00 sec)

 

root@localhost:[(none)]> grant all on hue.* to 'hue'@'mu-node-69' identified by 'hue';

Query OK, 0 rows affected (0.00 sec)

 

root@localhost:[(none)]> select host,user,password from mysql.user;

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

| host | user | password |

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

| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

| mu-node-69 | root | |

| 127.0.0.1 | root | |

| localhost | | |

| mu-node-69 | | |

| mu-node-69 | hue | *15221DE9A04689C4D312DEAC3B87DDF542AF439E |

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

6 rows in set (0.00 sec)

 

root@localhost:[(none)]> grant all privileges on hue.* to 'hue'@'mu-node-69';

 

root@localhost:[(none)]> update mysql.user set password = PASSWORD('hue') where password = '';

 

[root@mu-node-69 server]# /etc/init.d/mysqld restart

Stop the Hue service to make changes to $HUE_INSTALL_PATH/desktop/conf/hue.ini to point to MySQL

[root@mu-node-69 ~]# maprcli node services -name hue -action stop -nodes `hostname`

 

[root@mu-node-69 ~]# maprcli service list -node `hostname`

memallocated name state logpath displayname 

1777.0 fileserver 2 /opt/mapr/logs/mfs.log FileServer 

  hue 3 /opt/mapr/hue/hue-3.5.0/logs/ HueWebServer 

512.0 webserver 2 /opt/mapr/logs/adminuiapp.log Webserver 

711.0 cldb 2 /opt/mapr/logs/cldb.log CLDB 

178.0 tasktracker 2 /opt/mapr/hadoop/hadoop-0.20.2/logs TaskTracker 

888.0 jobtracker 2 /opt/mapr/hadoop/hadoop-0.20.2/logs JobTracker 

Auto hoststats 2 /opt/mapr/logs/hoststats.log HostStats

Add the following in $HUE_INSTALL_PATH/desktop/conf/hue.ini under [[database]] section

engine=mysql

host=mu-node-69

port=3306

user=hue

password=hue

name=hue

Sync and Migrate the DB from SQlite to MySQL

 

[root@mu-node-69 ~]# cd /opt/mapr/hue/hue-3.5.0/build/env/bin

[root@mu-n

ode-69 bin]# ./hue syncdb

Syncing...

...

...

 

[root@mu-node-69 bin]# ./hue migrate

Running migrations for desktop:

- Migrating forwards to 0007_auto__add_documentpermission__add_documenttag__add_document.

> desktop:0001_initial

...

...

 

The migrated database needs to be prepared to be usable by Hue

 

[root@mu-node-69 server]# mysql -h`hostname` -uhue -phue

 

hue@mu-node-69:[(none)]> use hue;

 

hue@mu-node-69:[hue]> select username from auth_user;

Empty set (0.00 sec)

As seen from the above the auth_user is currently empty. It'll get populated upon initial login.

 

hue@mu-node-69:[hue]> show create table auth_permission;

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

| Table | Create Table |

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

| auth_permission | CREATE TABLE `auth_permission` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) NOT NULL,

  `content_type_id` int(11) NOT NULL,

  `codename` varchar(100) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `content_type_id` (`content_type_id`,`codename`),

  KEY `auth_permission_e4470c6e` (`content_type_id`)

) ENGINE=MyISAM AUTO_INCREMENT=199 DEFAULT CHARSET=latin1 |

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

1 row in set (0.00 sec)

 

hue@mu-node-69:[hue]> ALTER TABLE auth_permission DROP FOREIGN KEY content_type_id_refs_id_e4470c6e;

Query OK, 198 rows affected (0.05 sec)

Records: 198 Duplicates: 0 Warnings: 0

 

hue@mu-node-69:[hue]> DELETE FROM hue.django_content_type;

Query OK, 66 rows affected (0.00 sec)

In the Hue UI I created a new user hueadmin upon initial login which reflects in the MySQL Hue database now

hue@mu-node-69:[hue]> select id,username from auth_user; 
+----+----------+
| id | username |
+----+----------+
|  1 | hueadmin |
+----+----------+
1 row in set (0.00 sec)

 

There you go :-)!   Hue on MySQL.

Attachments

    Outcomes