Hive+Mysql搭建

Mysql搭建(Centos7+Mysql5.7)

搭建

1.下载rpm包

mysql57-community-release-el7-11.noarch.rpm

链接：https://pan.baidu.com/s/1PRJFBziydlM4Zz-aR7M-tA
提取码：dotl

[hdlearn@master back]$ wget 'https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm'
--2021-01-24 16:12:55--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2021-01-24 16:13:05--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 104.75.165.42
Connecting to repo.mysql.com (repo.mysql.com)|104.75.165.42|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

100%[=================================================================================================================================>] 25,680      --.-K/s   in 0.02s   

2021-01-24 16:13:05 (1.14 MB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

2.加入yum仓库

[hdlearn@master back]$ sudo rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
[sudo] password for hdlearn: 
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]

3.安装mysql

[hdlearn@master back]$ sudo yum install -y mysql-community-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: ftp.sjtu.edu.cn
 * extras: ftp.sjtu.edu.cn
 * updates: ftp.sjtu.edu.cn
mysql-connectors-community                                                                                                                          | 2.6 kB  00:00:00     
mysql-tools-community                                                                                                                               | 2.6 kB  00:00:00     
mysql57-community

4.启动mysql

[hdlearn@master back]$ systemctl start mysqld
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: root
Password: 
==== AUTHENTICATION COMPLETE ===

5.mysql状态查看

[hdlearn@master back]$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-01-24 16:22:23 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3700 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3646 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3704 (mysqld)
    Tasks: 27
   CGroup: /system.slice/mysqld.service
           └─3704 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

修改Mysql密码

1.查看密码(看第一种方法即可)

# 第一种方法：
[hdlearn@master ~]$ sudo grep 'temporary password' /var/log/mysqld.log
[sudo] password for hdlearn: 
2021-01-24T08:22:20.879396Z 1 [Note] A temporary password is generated for root@localhost: :*u7:XkYUg/2
[hdlearn@master ~]$ mysql -uroot -p:*u7:XkYUg/2
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.33
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> set global validate_password_policy=0; 	# 修改密码复杂度，设置为0
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;	# 修改密码长度限制为1
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

# 第二种方法（经测试有问题，会出现修改完密码再次输入密码报密码错误）
[hdlearn@master back]$ sudo vim /etc/my.cnf # 看下图【设置免密登陆】
[hdlearn@master back]$ systemctl restart mysqld
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: root
Password: 
==== AUTHENTICATION COMPLETE ===
[hdlearn@master back]$ mysql -root # 看下图【】
mysql> set password for 'root'@'localhost' = password('密码')
mysql> flush privileges;

设置免密登陆

mysql修改初始密码

注！第二种方法，还需原样去掉免密登陆设置(作者见过其他人操作可以，但作者这里只能用第一种方法)

添加Mysql远程授权

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
mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "密码";	# 添加远程访问权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |	# 权限已添加
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

设置开机启动Mysql

[hdlearn@master ~]$ systemctl enable mysqld
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-unit-files ===
Authentication is required to manage system service or unit files.
Authenticating as: root
Password: 
==== AUTHENTICATION COMPLETE ===
==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon ===
Authentication is required to reload the systemd state.
Authenticating as: root
Password: 
==== AUTHENTICATION COMPLETE ===
[hdlearn@master ~]$ systemctl daemon-reload
==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon ===
Authentication is required to reload the systemd state.
Authenticating as: root
Password: 
==== AUTHENTICATION COMPLETE ===

Hive框架搭建

apache-hive-1.2.2-bin.tar.gz

链接：https://pan.baidu.com/s/1BJqilurVrLD4XaNFqdHzoA
提取码：85gc

Hive安装配置

1.Hive解压

1 2	# 解压hive到某个目录 [hdlearn@master software]$ tar -zxvf apache-hive-1.2.2-bin.tar.gz -C /opt/module/

2.修改配置文件hive-env.sh和hive-site.xml

进入Hive解压目录里的conf目录下，修改hive-env.sh文件，创建新的hive基本配置文件。

1
2
3

[hdlearn@master apache-hive-1.2.2-bin]$ cd conf/
[hdlearn@master conf]$ mv hive-env.sh.template hive-env.sh
[hdlearn@master conf]$  vim hive-site.xml	# 创建hive基本配置文件

下面是编辑好的hive基本配置xml文件内容：

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
            <!-- 链接至mysql去查找数据源 -->
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
        </property>
        <property>
            <!-- 指定mysql驱动 -->
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>
        <property>
            <!-- mysql用户名 -->
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
            <!-- mysql用户密码 -->
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>852508</value>
        </property>
        <property>
            <!-- 会在hdfs上创建数据库路径 -->
               <name>hive.metastore.warehouse.dir</name>
               <value>/user/hive/warehouse</value>
       </property>
       <property>
           
             <name>yarn.app.mapreduce.am.command-opts</name>
             <value>-Djava.net.preferIPv4Stack=true -Xmx500m</value>
       </property>
       <property>
           
             <name>mapreduce.map.java.opts</name>
             <value>-Djava.net.preferIPv4Stack=true -Xmx500m</value>
       </property>
    	   
             <name>mapreduce.map.java.opts</name>
             <value>-Djava.net.preferIPv4Stack=true -Xmx500m</value>
       </property>
       <property>
           
            <name>mapreduce.reduce.java.opts</name>
            <value>-Djava.net.preferIPv4Stack=true -Xmx500m</value>
       </property>
</configuration>

接着，修改hive-env.sh里的内容：

# 在文件里取消注释以下三个配置，并配置对应路径

# Set HADOOP_HOME to point to a specific hadoop install directory
# hadoop根目录路径
HADOOP_HOME=/opt/module/hadoop-2.6.1

# Hive Configuration Directory can be controlled by:
# hive的conf的目录
export HIVE_CONF_DIR=/opt/module/apache-hive-1.2.2-bin/conf

# Folder containing extra ibraries required for hive compilation/execution can be controlled by:
# hive的lib包目录，里面有jar包
export HIVE_AUX_JARS_PATH=/opt/module/apache-hive-1.2.2-bin/lib

3.添加文件mysql-connector-java-5.1.46.jar和替换文件 jline-0.9.94.jar

1.配置mysql-connector-java-5.1.46.jar文件，将其放到hive根目录下的lib包里。

链接：https://pan.baidu.com/s/1Wm9qKwOAQLg7tpIJIGeQ_g
提取码：clcs

2.进入hadoop的根目录的“hadoop-2.6.1/share/hadoop/yarn/lib”路径下，可以看里面存在 jline-0.9.94.jar包，因为其版本较老，需要替换。

需要替换的jar包

将hive根目录下的lib目录下的jline-2.12.jar包复制到hadoop的“/share/hadoop/yarn/lib”路径下，替换掉 jline-0.9.94.jar包。

hive下的较新版本的jar包

注意，hadoop集群中，只需要替换hive所在机器的jline-0.9.94.jar文件就好

4.配置环境变量

[hdlearn@master /]$ sudo vim /etc/profile
[sudo] password for hdlearn: 
[hdlearn@master /]$ source /etc/profile

在/etc/profile文件添加以下环境变量
#HIVE
export HIVE_HOME=/opt/module/apache-hive-1.2.2-bin
export PATH=$PATH:$HIVE_HOME/bin

启动Hive

1.首先要启动hadoop集群，用jps查看集群启动情况

[hdlearn@master /]$ start-all.sh 
This script is Deprecated. Instead use start-dfs.sh and start-yarn.sh
Starting namenodes on [master]
master: starting namenode, logging to /opt/module/hadoop-2.6.1/logs/hadoop-hdlearn-namenode-master.out
master: starting datanode, logging to /opt/module/hadoop-2.6.1/logs/hadoop-hdlearn-datanode-master.out
slave2: starting datanode, logging to /opt/module/hadoop-2.6.1/logs/hadoop-hdlearn-datanode-slave2.out
slave1: starting datanode, logging to /opt/module/hadoop-2.6.1/logs/hadoop-hdlearn-datanode-slave1.out
Starting secondary namenodes [slave2]
slave2: starting secondarynamenode, logging to /opt/module/hadoop-2.6.1/logs/hadoop-hdlearn-secondarynamenode-slave2.out
starting yarn daemons
starting resourcemanager, logging to /opt/module/hadoop-2.6.1/logs/yarn-hdlearn-resourcemanager-master.out
master: starting nodemanager, logging to /opt/module/hadoop-2.6.1/logs/yarn-hdlearn-nodemanager-master.out
slave2: starting nodemanager, logging to /opt/module/hadoop-2.6.1/logs/yarn-hdlearn-nodemanager-slave2.out
slave1: starting nodemanager, logging to /opt/module/hadoop-2.6.1/logs/yarn-hdlearn-nodemanager-slave1.out
[hdlearn@master /]$ jps
9425 Jps
8708 NameNode
8873 DataNode
9279 NodeManager

2.启动hive

[hdlearn@master /]$ hive
ls: cannot access /opt/module/spark-2.4.4-bin-hadoop2.6/lib/spark-assembly-*.jar: No such file or directory

Logging initialized using configuration in jar:file:/opt/module/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.properties
hive> show databases;	# 查看hive数据库，有一个默认数据库
OK
default
Time taken: 0.515 seconds, Fetched: 1 row(s)
hive>

查看hadoop的hdfs

[hdlearn@master apache-hive-1.2.2-bin]$ hadoop fs -ls /tmp/hive
Found 1 items
drwx------   - hdlearn supergroup          0 2021-01-24 23:24 /tmp/hive/hdlearn
[hdlearn@master apache-hive-1.2.2-bin]$

以上便是Hive+Mysql搭建的所有步骤。

参考资料

1.https://blog.csdn.net/lovequanquqn/article/details/82343920