Hive metastore on mysql database

######################################
## Hive on mysql
#####################################
Step1:
To install MySQL on a Red Hat system:
$ sudo yum install mysql-server

Starting Mysql service
$ sudo service mysqld start

Step2:
To install the MySQL connector on a Red Hat 6 system:
Install mysql-connector-java and symbolically link the file into the /usr/lib/hive/lib/ directory.

$ sudo yum install mysql-connector-java
$ ln -s /usr/share/java/mysql-connector-java.jar /opt/hive/lib/mysql-connector-java.jar

Step3:
$ sudo /usr/bin/mysql_secure_installation
[...]
Enter current password for root (enter for none):
OK, successfully used password, moving on...
[...]
Set root password? [Y/n] y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
[...]
Disallow root login remotely? [Y/n] N
[...]
Remove test database and access to it [Y/n] Y
[...]
Reload privilege tables now? [Y/n] Y
All done!

Step4:
To make sure the MySQL server starts at boot:

On Red Hat systems:
$ sudo /sbin/chkconfig mysqld on
$ sudo /sbin/chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

Step5:
$ mysql -u root -p
Enter password:
mysql> CREATE DATABASE metastore;
mysql> CREATE USER 'hive'@'ec2-54-210-74-58.compute-1.amazonaws.com' IDENTIFIED BY 'hive';
mysql> GRANT all on *.* to 'hive'@'ec2-54-210-74-58.compute-1.amazonaws.com' identified by 'hive';
mysql> flush privileges;
------------------------------------------------------
Note: Procedure to export and import dump for other cluster setup
backup:
$mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:
$mysql -u root -p[root_password] [database_name] < dumpfilename.sql
-------------------------------------------------------

Step6:
Adding/Modifying the properties in hive-site.xml

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/metastore</value>
  <description>the URL of the MySQL database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hive</value>
</property>

<property>
  <name>datanucleus.autoCreateSchema</name>
  <value>false</value>
</property>

<property>
  <name>datanucleus.fixedDatastore</name>
  <value>true</value>
</property>

<property>
  <name>hive.metastore.uris</name>
  <value>thrift://localhost:9083</value>
  <description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>

---------------------------------------------------
Step8:

Start thrift service
hive --service metastore