CentOS7下Hive连接MySQL失败及创建Hive用户报错求助

Hey there, let's work through your two problems one by one—first the MySQL login failure, then the Hive socket connection error.

1. Fixing the MySQL Login Command Error #

Your current login command mysql -u root -p welcome123 is incorrect because the -p flag doesn't support directly appending the password without a space. Here's how to fix it:

Correct Ways to Log into MySQL #

• Interactive Password Prompt (Recommended for Security):
  Run this command, then enter your password welcome123 when prompted:

  mysql -u root -p
  

• One-Line Command (For Scripts):
  Use the --password long option instead (note the equals sign):

  mysql -u root --password=welcome123
  

Creating the Hive User Properly #

Once logged into MySQL, run these commands to create the Hive user and grant necessary permissions (don't forget to create the Hive metadata database first):

-- Create the hive database (required for Hive metadata storage)
CREATE DATABASE IF NOT EXISTS hive;
-- Create the hive user with password 'hivepwd'
CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hivepwd';
-- Grant full permissions on the hive database to the hive user
GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost';
-- Flush privileges to apply changes immediately
FLUSH PRIVILEGES;

Alternatively, you can run this in a single bash command without logging into MySQL interactively:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS hive; CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hivepwd'; GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost'; FLUSH PRIVILEGES;"

Then enter your root password when prompted.

2. Resolving Hive's "Can't connect to local MySQL server through socket" Error #

This error usually comes from MySQL service issues, incorrect socket paths, or permission problems. Let's go through the troubleshooting steps:

Step 1: Verify MySQL Service is Running #

First, check if MySQL is active:

systemctl status mysqld

If it's not running, start it and enable auto-start on boot:

systemctl start mysqld
systemctl enable mysqld

Step 2: Check MySQL Socket File Location #

Find where MySQL's socket file is located by running:

mysql -u root -p -e "SHOW VARIABLES LIKE 'socket';"

You'll get an output like:

+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/lib/mysql/mysql.sock   |
+---------------+-----------------------------+

Step 3: Update Hive Configuration #

Open your Hive configuration file hive-site.xml (usually at /etc/hive/conf or your Hive installation's conf directory) and adjust the database connection settings:

Option A: Use Socket Connection

Update the URL to include the correct socket path:

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true&useSSL=false&socket=/var/lib/mysql/mysql.sock&useUnicode=true&characterEncoding=UTF-8</value>
</property>

Option B: Use TCP Connection (Avoids Socket Issues)

Switch to TCP by specifying the default MySQL port (3306) — this is often more reliable for cross-service connections:

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>

Also, double-check the user and password properties are set correctly:

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hivepwd</value>
</property>

Step 4: Fix Socket File Permissions (If Needed) #

If the socket file has restrictive permissions that block Hive access, check the permissions:

ls -l /var/lib/mysql/mysql.sock

For testing, you can temporarily loosen permissions:

chmod 777 /var/lib/mysql/mysql.sock

For a permanent fix, edit MySQL's my.cnf (located at /etc/my.cnf or /etc/mysql/my.cnf) to set socket permissions on startup:

[mysqld]
socket=/var/lib/mysql/mysql.sock
chmod=0777

Then restart MySQL:

systemctl restart mysqld

After making these changes, restart Hive services and test the connection again.

内容的提问来源于stack exchange，提问作者ScalaBoy