Restore Hive MySQL database for new Hive service on new HDP cluster 2.6.0

[#1]

If the mysqldump is for different version other than Hive 2.1.1000 in HDP 2.6. Then do the following:

1. Stop Hive services from Ambari.

2. Create new database under MySQL as say hive2:

mysql> create database hive2;
Query OK,1 row affected (0.00 sec)
mysql> grant all privileges on hive2.* to 'hive'@'%' identified by'hive';
Query OK,0 rows affected (0.00 sec)

3. Restore database as:

mysql -u hive -phive hive2 < dumpfilename.sql

4. Update database connection string for mysql under Ambari -> Hive configs.

5. Save configurations and try restarting. Since there is different in VERSION, service startup would fail.

6. Run Hive schematool command to upgrade the schema as below:

hive@ssnode260 bin]$ /usr/hdp/2.6.0.3-8/hive2/bin/schematool -upgradeSchema -dbType mysql

7. Restart Hive services from Ambari.

If the Hive metadata version is same as Hive 2.1.1000 in HDP 2.6, then follow steps 1 through 5.