hive使用mysql localstore方式连接Access denied for user 'hive'@'localhost' to database

 

mysql> create user 'hive' identified by 'hive';        --创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'hive@localhost' with grant option;  --localhost不可少，否则启动bin/hive 后show databases后有异常，见“附注”
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

 

mysql> flush privileges;            --这个不可少，不然启动bin/hive后不会立即生效（hivedb不会产生）
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hivedb             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)                --可以看到有hivedb生成。

mysql>

 

附注

异常如下：

hive> show databases;
FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access denied for user'hive'@'localhost' to database 'hivedb'
NestedThrowables:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user'hive'@'localhost' to database 'hivedb'
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask