Set up MariaDB or MySQL database

You install a MariaDB or MySQL database to serve as the backend database for the Hive metastore. You also install the MySQL driver on your cluster, and then configure the database.

  1. Install MySQL from the command line of a node in your cluster.
    • RHEL:
      sudo yum install mysql-server
    • SLES:
      sudo zypper install mysql
      sudo zypper install libmysqlclient_r17
    • Ubuntu:
      sudo apt-get install mysql-server
  2. Start the mysql daemon.
    • RHEL:
      sudo service mysqld start
    • SLES or Ubuntu:
      sudo service mysql start 
  3. Install the latest MySQL JDBC driver on the Hive metastore server node in your cluster.
  4. Set the MySQL root password.
    sudo /usr/bin/mysql_secure_installation
    [...]
    Enter current password for root (enter for none):
    OK, successfully used password, moving on...
    [...]
    Set root password? [Y/n] y
    New password:
    Re-enter new password:
    Remove anonymous users? [Y/n] Y
    [...]
    Disallow root login remotely? [Y/n] N
    [...]
    Remove test database and access to it [Y/n] Y
    [...]
    Reload privilege tables now? [Y/n] Y
    All done!
  5. Configure the MySQL server to start when the cluster starts up.
    • RHEL
      sudo /sbin/chkconfig mysqld on
      sudo /sbin/chkconfig --list mysqld
    • SLES:sudo chkconfig --add mysql
    • Ubuntu: sudo chkconfig mysql on
  6. Create the initial database schema.
    mysql -u root -p
    Enter password:
    mysql> CREATE DATABASE metastore;
    mysql> USE metastore;
    -- For Package install method:
    mysql> SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-n.n.n.mysql.sql;
    -- For Parcel install method:
    mysql> SOURCE /opt/cloudera/parcels/CDH/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-n.n.n.mysql.sql;
    If the metastore service runs on the host where the database is installed, replace 'metastorehost' in the CREATE USER example with 'localhost'.
  7. Create a MySQL user account to access the metastore.
    mysql> CREATE USER 'hive'@'metastorehost' IDENTIFIED BY 'mypassword';
    ...
    mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'metastorehost';
    mysql> GRANT ALL PRIVILEGES ON metastore.* TO 'hive'@'metastorehost';
    mysql> FLUSH PRIVILEGES;
    mysql> quit;