Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
quick guide to connect HS2 to MySQL DB metastore over SSL

Setting up MySQL SSL

# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

update my.cnf as follow

[mysqld]
ssl-ca=/home/hive/ca-cert.pem
ssl-cert=/home/hive/server-cert.pem
ssl-key=/home/hive/server-key.pem

Restart MySQL

grant priv to hive user

mysql> GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' IDENTIFIED BY 'hive' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

import client cert and key into keystore

as there is no direct way to do it I have taken a help from this guide http://www.agentbob.info/agentbob/79-AB.html convert cert and pem key into DER format and import it using the java program provided at the link.

Edit hive-env.sh

# specified truststore location and password with hive client opts
if [ "$SERVICE" = "hiveserver2" ]; then
 export HADOOP_CLIENT_OPTS="$HADOOP_CLIENT_OPTS -Djavax.net.ssl.trustStore=/home/hive/keystore.ImportKey -Djavax.net.ssl.trustStorePassword=importkey"
fi

updated hive-site.xml

javax.jdo.option.ConnectionURL
jdbc:mysql://sandbox.hortonworks.com/hive?createDatabaseIfNotExist=true&useSSL=true&verifyServerCertificate=false

Restarted HS2 which is now able to connect to MySQL over SSL


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment