HIVE及MySQL安装

安装HIVE

编辑hive-env.sh

 

在HDFS创建数据存储仓库

[root@master conf]# hadoop fs -mkdir -p /user/hive/warehouse

 

启动hive报错如下：

 

原因：jar包冲突，删除低版本jar包即可

解决方法：删除/usr/local/src/hadoop/soft/hadoop-2.6.0/share/hadoop/yarn/lib中的jline-0.9.94.jar，再次启动hive即可

 

HIVE的使用：

[root@master lib]# hive

 

hive> show databases;  查看数据库

OK

default

Time taken: 0.976 seconds, Fetched: 1 row(s)

 

hive> use default;  使用默认数据库

OK

Time taken: 0.046 seconds

 

hive> show tables;  显示所有表

OK

Time taken: 0.035 seconds

 

hive> create table student(id int, name string);  创建表student

OK

Time taken: 1.065 seconds

hive> show tables;  显示所有表

OK

student

Time taken: 0.006 seconds, Fetched: 1 row(s)

 

hive> select * from student ;  查看表信息

OK

Time taken: 1.297 seconds

 

hive> insert into student values(1, "kp");  在表中插入信息

hive的执行语句依赖mapreduce，具体回显如下：↓

Query ID = root_20190418123535_42b4ee17-5455-4c36-ba9d-ae70cfb9b9ac

Total jobs = 3

Launching Job 1 out of 3

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_1555559259494_0001, Tracking URL = http://slave1:8088/proxy/application_1555559259494_0001/

Kill Command = /usr/local/src/hadoop/soft/hadoop-2.6.0/bin/hadoop job  -kill job_1555559259494_0001

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0

2019-04-18 12:36:33,782 Stage-1 map = 0%,  reduce = 0%

2019-04-18 12:37:34,940 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.55 sec

MapReduce Total cumulative CPU time: 1 seconds 550 msec

Ended Job = job_1555559259494_0001

Stage-4 is selected by condition resolver.

Stage-3 is filtered out by condition resolver.

Stage-5 is filtered out by condition resolver.

Moving data to: hdfs://hahadoop/user/hive/warehouse/student/.hive-staging_hive_2019-04-18_12-35-52_629_2401328796934797931-1/-ext-10000

Loading data to table default.student

Table default.student stats: [numFiles=1, numRows=1, totalSize=5, rawDataSize=4]

MapReduce Jobs Launched:

Stage-Stage-1: Map: 1   Cumulative CPU: 1.71 sec   HDFS Read: 3344 HDFS Write: 76 SUCCESS

Total MapReduce CPU Time Spent: 1 seconds 710 msec

OK

Time taken: 108.661 seconds

 

hive> select *from student;  查看表中插入的内容

OK

1 kp

Time taken: 0.129 seconds, Fetched: 1 row(s)

hive> desc student;  查看表结构

OK

id                   int                                      

name                 string                                   

Time taken: 0.159 seconds, Fetched: 2 row(s)

 

导入数据到HIVE中：↓

[root@master software]# mkdir -p datas  创建存放数据的文件夹

[root@master datas]# vi student.txt  写入txt数据如下 ↓

2       ali          ←←←←←←←←←←←

3       xiaofei

4       qiangqiang

5       dabai ←←字段之间的分隔符是\t

 

hive> load data local inpath "/opt/software/datas/student.txt" into table student;

Loading data to table default.student  导入刚刚写的数据

Table default.student stats: [numFiles=2, numRows=0, totalSize=42, rawDataSize=0]

OK  初步显示成功

Time taken: 0.945 seconds

 

hive> select *from student;  查看student表

OK

1 kp

NULL NULL

NULL NULL  导入数据都为空，数据出错

NULL NULL  表和txt文件之间元数据的对应关系有问题

NULL NULL

Time taken: 0.11 seconds, Fetched: 5 row(s)

 

hive> drop table student;  删除表，重新创建

OK

Time taken: 2.688 seconds

 

hive> create table student(id int, name string) row format delimited fields terminated by "\t";  创建表，行格式分割字段以\t结尾    

OK

Time taken: 0.149 seconds

 

hive> load data local inpath "/opt/software/datas/student.txt" into table student;

Loading data to table default.student  导入数据到hive中

Table default.student stats: [numFiles=1, totalSize=37]

OK

Time taken: 0.366 seconds

 

hive> select * from student;  查看表数据

OK

2 ali

3 xiaofei

4 qiangqiang  导入成功

5 dabai

Time taken: 0.085 seconds, Fetched: 4 row(s)

注意：在创建新表时需注意元数据和表之间的映射关系，如：指定表分隔符

安装MySQL

上传并解压mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar

离线安装mysql，顺序如下：↓

[root@master software]# rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm

warning: mysql-community-common-5.7.24-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-common-5.7.24-1.e################################# [100%]

 

[root@master software]# rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm

warning: mysql-community-libs-5.7.24-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-libs-5.7.24-1.el7################################# [100%]

 

[root@master software]# rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm

warning: mysql-community-client-5.7.24-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-client-5.7.24-1.e################################# [100%]

 

[root@master software]# rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm

warning: mysql-community-server-5.7.24-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-server-5.7.24-1.e################################# [100%]

 

[root@master software]# service mysqld restart  重启mysql服务器

Redirecting to /bin/systemctl restart mysqld.service

 

[root@master software]# service mysqld status  查看mysql服务器状态

Redirecting to /bin/systemctl status mysqld.service

• mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2019-04-18 15:04:11 CST; 37s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 50441 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

  Process: 50324 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 50443 (mysqld)

   CGroup: /system.slice/mysqld.service

           └─50443 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

 

Apr 18 15:03:33 master systemd[1]: Starting MySQL Server...

Apr 18 15:04:11 master systemd[1]: Started MySQL Server.

 

查看mysql临时密码一：

[root@master etc]# vim /var/log/mysqld.log

 

查看mysql临时密码二：

[root@master ~]# cat /var/log/mysqld.log | grep 'password'

2019-04-18T07:04:06.757490Z 1 [Note] A temporary password is generated for root@localhost: ydTk4wV=&e&I

2019-04-18T07:08:23.948507Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

2019-04-18T07:14:27.630172Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

2019-04-18T07:24:10.962191Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)

2019-04-18T07:28:51.171125Z 0 [Note] Shutting down plugin 'validate_password'

2019-04-18T07:28:52.770304Z 0 [Note] Shutting down plugin 'sha256_password'

2019-04-18T07:28:52.770305Z 0 [Note] Shutting down plugin 'mysql_native_password'

2019-04-18T07:36:16.301841Z 0 [Note] Shutting down plugin 'sha256_password'

2019-04-18T07:36:16.301842Z 0 [Note] Shutting down plugin 'mysql_native_password'

2019-04-18T07:36:40.947367Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

2019-04-18T07:36:54.244082Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

 

[root@master log]# mysql -uroot -p  登录mysql

Enter password:   输入临时密码

 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.24

 

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>   登录成功

 

 

mysql> alter user 'root'@'localhost' identified by "123456";  修改mysql密码

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements  由于密码过于简单触发validate_password_policy策略

mysql> status  此时不修改密码不能进行任何操作

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

 

mysql> alter user 'root'@'localhost' identified by "P@ssw0rd";  使用强度密码，修改成功

Query OK, 0 rows affected (0.19 sec)

 

mysql> status  查看mysql状态

--------------

mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

 

Connection id: 4

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.24

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db     characterset: latin1

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 29 min 45 sec

 

Threads: 1  Questions: 11  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.006

--------------

 

mysql> show variables like '%password';  查看数据库变量

+--------------------------------------------------------------+-------+

| Variable_name                         | Value |

+-------------------------------------------------------------+---------+

| disconnect_on_expired_password          | ON  |

| log_builtin_as_identified_by_password      | OFF |

| report_password                        |     |

+--------------------------------------------------------------+-------+

3 rows in set (0.04 sec)

 

 

 

mysql> show databases;  查看数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.06 sec)