Hive 1.1.2 - MySQL配置

0、配置好Hive环境

参考上一篇 https://www.jianshu.com/p/dfec0cfdf375

1、查看本地是否存在Mysql ,并删除

完全删除Mysql可参考 https://blog.csdn.net/taking_wang/article/details/52464381

[root@hadoop01 ~]#rpm -qa | grep mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@hadoop01 ~]#rpm -ev --nodeps mysql-libs-5.1.73-8.el6_8.x86_64

2、安装Mysql

[root@hadoop01 ~]#yum install mysql-server

3、查看mysql状态，并启动

a、查看状态

[root@hadoop01 ~]#service mysqld status
mysqld is stopped

b、启动

[root@hadoop01 ~]#service mysqld start
Initializing MySQL database:  ............
......
Starting mysqld:     [  OK  ]

c、查看状态

[root@hadoop01 ~]#service mysqld status
mysqld (pid  7170) is running...

4、设置Mysql开机启动

a、查看自启动状态 （保证2到5是启用，则为开机启动）

[root@hadoop01 ~]#chkconfig --list | grep mysql
mysqld      0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

b、设置开机启动

[root@hadoop01 ~]#chkconfig mysqld on

5、设置Mysql 用户名和密码

[root@hadoop01 ~]#mysqladmin -u root password '123456'

6、登录Mysql

[root@hadoop01 ~]#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73 Source distribution

7、设置其他主机连接权限（默认不允许其他主机连接）

a、查看所有数据库

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

b、

mysql>use mysql
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
  Database changed

c、

mysql>show tables;
  +---------------------------+
  | Tables_in_mysql           |
  +---------------------------+
  | ...                       |
  | user                      |
  +---------------------------+
  23 rows in set (0.00 sec)

d、

mysql>select user,host from user;
  +------+-------------+
  | user |   host      |
  +------+-----------+
  | root |  127.0.0.1  |
  |        | hadoop01  |
  | root |   hadoop01  |
  |     | localhost    |
  | root | localhost   |
  +------+-------------+
  5 rows in set (0.00 sec)

e、设置远程登录权限

mysql>grant all privileges on *.* to 'root'@'%' identified by '123456';
    Query OK, 0 rows affected (0.00 sec)

f、查看 root % 是否存在

mysql> select user,host from user;
    +------+-----------+
    | user | host      |
    +------+-----------+
    | root | %         |
    | root | 127.0.0.1 |
    |      | hadoop01  |
    | root | hadoop01  |
    |      | localhost |
    | root | localhost |
    +------+-----------+
    6 rows in set (0.00 sec)

g、删除其他的连接方式，只留下 root %

mysql>delete from user where host='localhost';
mysql>delete from user where host='127.0.0.1';
mysql>delete from user where host='hadoop01';
    ...
mysql>select user,host from user;
    +------+------+
    | user | host |
    +------+------+
    | root | %    |
    +------+------+
    1 row in set (0.00 sec)

h、刷新信息，并退出

mysql>flush privileges;
   Query OK, 0 rows affected (0.00 sec)

i、重启mysql服务

[root@hadoop01 ~]#service mysqld restart;
    Stopping mysqld:                       [  OK  ]
    Starting mysqld:                       [  OK  ]

8、配置 Hive和Mysql

[root@hadoop01 conf]#cp hive-default.xml.template hive-site.xml
[root@hadoop01 conf]#vim hive-site.xml

把<configuration> 下的内容全删掉，替换成以下内容:

    <configuration>
        <!-- 指定mysql链接驱动 -->
        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>

        <!-- 指定mysql主机名端口号和新建数据库名称 -->
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://hadoop01:3306/metastore?createDatabaseIfNotExist=true</value>
        </property>

        <!-- 指定用户名与密码 -->
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>123456</value>
        </property>

        <!-- 显示列名 -->
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
        </property>

        <!-- 显示数据库名或者表名 -->
        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
        </property>
    </configuration>

9、配置hive的log

[root@hadoop01 conf]#cp hive-log4j.properties.template hive-log4j.properties
[root@hadoop01 conf]#vim hive-log4j.properties

修改 hive.log.dir=/opt/app/hive-1.2.2-bin/logs