大数据实践中Mysql与Sqoop的安装与连接(含安装包分享)

安装包：

链接：https://pan.baidu.com/s/1do53rnLDu9H9zXt6b22Ing
提取码：hvy2

1.Mysql安装

1.1安装包准备

1. 上传Mysql相关安装包

   
   #1.首先在/opt/software/目录下创建mysql文件
   [lili@hadoop102 software]$ mkdir mysql
   #2.上传安装包到/opt/software/mysql目录下
   #Mysql客户端
   MySQL-client-5.6.24-1.el6.x86_64.rpm
   #Mysql驱动
   mysql-connector-java-5.1.27.tar.gz
   #Mysql服务器
   MySQL-server-5.6.24-1.el6.x86_64.rpm
   

2. 查看Mysql是否安装，如果安装进行卸载（在此时记得先进入root用户）

   #1.先进入root用户
   [lili@hadoop102 ~]$ su root
   #2.查看
   [root@hadoop102 ~]# rpm -qa | grep mysql
   mysql-libs-5.1.73-7.el6.x86_64
   #3.卸载
   [root@hadoop102 ~]# rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64
   

1.2安装Mysql服务器

1. 安装Mysql服务端

   [root@hadoop102 mysql]# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm
   

2. 查看产生的随机密码

   [root@hadoop102 mysql]# cat /root/.mysql_secret
   POUNaHY70WkG19Xs
   

3. 查看Mysql状态

   [root@hadoop102 mysql]# service mysql status
   

4. 启动Mysql

   [root@hadoop102 mysql]# service mysql start
   

1.3安装Mysql客户端

1. 安装mysql客户端

   [root@hadoop102 mysql]# rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm
   

2. 链接mysql

   [root@hadoop102 mysql-libs]# mysql -uroot -pPOUNaHY70WkG19Xs
   

3. 修改密码

   mysql>SET PASSWORD=PASSWORD('123456');
   

4. 退出mysql

   mysql>exit;
   

1.4Mysql中user表配置

配置只要是root用户+密码，在任何主机上都能登录MySQL数据库。

1. 进入mysql

   [root@hadoop102 mysql]# mysql -uroot -p123456
   

2. 显示数据库

   mysql>show databases;
   

3. 使用mysql数据库

   mysql>use mysql;
   

4. 展示mysql数据库中的所有表

   mysql>show tables;
   

5. 展示user表的结构

   mysql>desc user;
   

6. 查询user表

   mysql>select User, Host, Password from user;
   

7. 修改user表，把Host表内容修改为%

   mysql>update user set host='%' where host='localhost';
   

8. 删除root用户的其他host

   mysql>delete from user where Host='hadoop102';
   mysql>delete from user where Host='127.0.0.1';
   mysql>delete from user where Host='::1';
   

9. 查询user表

   mysql>select User, Host, Password from user;
   +------+------+-------------------------------------------+
   | User | Host | Password                                  |
   +------+------+-------------------------------------------+
   | root | %    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
   +------+------+-------------------------------------------+
   1 row in set (0.00 sec)
   

10. 刷新

    mysql>flush privileges;
    

11. 退出

    mysql>exit;
    

12. 切换回普通用户

    [root@hadoop102 mysql]# exit
    exit
    [lili@hadoop102 mysql]$
    

2.Sqoop安装

2.1下载并解压

1. 下载地址：http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/

2. 上传安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz到hadoop102的/opt/software路径中

3. 解压sqoop安装包到指定目录

   [lili@hadoop102 software]$ tar -zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/
   

4. 将sqoop-1.4.6.bin__hadoop-2.0.4-alpha文件改名为sqoop

   [lili@hadoop102 module]$ mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop
   

2.2修改配置文件

1. 进入到/opt/module/sqoop/conf目录，重命名配置文件

   [liliu@hadoop102 conf]$ mv sqoop-env-template.sh sqoop-env.sh
   

2. 修改配置文件

   [lili@hadoop102 conf]$ vim sqoop-env.sh 
   export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2
   export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2
   

2.3添加JDBC驱动

1. 进入到/opt/software/mysql路径，解压mysql-connector-java-5.1.27.tar.gz到当前路径

   [lili@hadoop102 mysql]$ tar -zxvf mysql-connector-java-5.1.27.tar.gz
   

2. 进入到/opt/software/mysql/mysql-connector-java-5.1.27路径，拷贝jdbc驱动到sqoop的lib目录下

   [lili@hadoop102 mysql-connector-java-5.1.27]$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop/lib/
   

2.4验证Sqoop

我们通过Sqoop的一个简单命令进行验证

[lili@hadoop102 sqoop]$ bin/sqoop help
#中间部分警告信息忽略
Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

3.测试Sqoop连接Mysql

[lili@hadoop102 sqoop]$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 123456
#输出数据库列表
information_schema
mysql
performance_schema
test