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

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.