Using an Existing or Installing a Default Database

Installing a Default Database

MySQL

Before you begin

Determine the appropriate database version and obtain the release drivers and .jar file.
On the Ambari Server host, Download the MySQL Connector/JDBC driver from MySQL
Install the respective driver jar package.

Here is a sample installation guide for the MySQL 8.x database and the respective connector jar.

CentOS :

sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
sudo yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
sudo yum install mysql-community-server
sudo yum install mysql-connector-java*
systemctl start mysqld
Ubuntu:

wget http://repo.mysql.com/mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb
sudo dpkg-reconfigure mysql-apt-config
sudo apt update
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C
sudo apt update
sudo apt-cache policy mysql-server
sudo apt install -f mysql-client=5.7*
sudo apt install -f mysql-community-server=5.7*
sudo apt install -f mysql-server=5.7*
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-j_8.0.32-1ubuntu20.04_all.deb
sudo dpkg -i mysql-connector-j_8.0.32-1ubuntu20.04_all.deb
ls /usr/share/java/mysql- connector-java.jar
chmod 644 /usr/share/java/mysql-connector-j-8.0.32.jar
After ensuring that the prerequisites have been met, proceed by following the provided steps below:

Configure and start the database service

Edit binding-address from 127.0.0.1 to the host of database service, in /etc/mysql/mysql.conf.d/mysqld.cnf.
Start mysql using service mysql start.
Confirm that .jar is in the Java share directory. For example: ls /usr/share/java/mysql- connector-java.jar
Make sure the .jar file has the appropriate permissions - 644. For example : chmod 644 /usr/share/java/mysql-connector-j-8.0.32.jar

Create a user for your service and grant it permissions, using the MySQL database admin utility

mysql -u root -p
CREATE USER '[USERNAME]'@'%' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'%';
CREATE USER '[USERNAME]'@'localhost' IDENTIFIED BY '[PASSWORD]'; GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'localhost';
CREATE USER '[USERNAME]'@'[SERVICE_SERVER_FQDN]' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'[SERVICE_HOST_FQDN]';
FLUSH PRIVILEGES;
CREATE DATABASE [DATABASE_NAME];

Postgres SQL

Before you begin

Determine the appropriate database version and obtain the release drivers and .jar file.
On the Ambari server host, Download the PostgreSQL JDBC Driver from PostgreSQL.
Install the respective driver jar package.

Here is a sample installation for the Postgres 12.17 database, Postgres 15.5 database, and the respective connector jar.

RHEL 8

#Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
# Install PostgreSQL 12.17
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql12-server postgresql12
-------------------------------------------------------------------------
# Install Postgres 15.5
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql15-server postgresql15
# Install the postgresql jdbc jar
sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
sudo dnf install postgresql-jdbc
You can also get the PostgreSQL JDBC jar from https://jdbc.postgresql.org/download/postgresql-42.7.1.jar using the command wgethttps://jdbc.postgresql.org/download/postgresql-42.7.1.jar and then by moving the jar to /usr/share/java/.
After ensuring that the prerequisites have been met, proceed by following the provided steps below:

Configure and start the database service

Edit/var/lib/pgsql/12/data/postgresql.conf to include

listen_addresses = '*'
Additionally, in version 12.17, it is necessary to modify the authentication method from "indent" to "md5" in the configuration file located at /var/lib/pgsql/12/data/pg_hba.conf. This change switches the authentication method to use a password.

Start the service

# After installation, database initialization is required before service can be started.
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
-----------------------------------------------
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
#Start and enable the database server service
sudo systemctl enable --now postgresql-12
---------------------------------------
sudo systemctl enable --now postgresql-15
systemctl status postgresql-12
-----------------------------
systemctl status postgresql-15

Create a user for your service and grant it the necessary permissions

sudo -i su postgres
psql
CREATE DATABASE 'USERNAME';
CREATE USER 'USERNAME' WITH ENCRYPTED PASSWORD 'PASSWORD'';
GRANT ALL PRIVILEGES ON DATABASE 'USERNAME' TO 'USERNAME';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 'USERNAME';
GRANT CONNECT ON DATABASE 'USERNAME' TO 'USERNAME';
ALTER USER 'USERNAME' CREATEDB;
ALTER USER 'USERNAME' CREATEROLE;
# Grant schema command required only in Postgres 15
\c 'USERNAME'
GRANT ALL ON SCHEMA public TO 'USERNAME';
SELECT pg_reload_conf();

Edit /var/lib/pgsql/12/data/pg_hba.conf to include new service users

local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
local all ambari trust
host all ambari 0.0.0.0/0 trust
host all ambari ::/0 trust
Here is an example:

Restart the PostgreSQL service every time after editing file /var/lib/pgsql/12/data/pg_hba.conf .

Maria DB

Before you begin

Determine the appropriate database version and obtain the release drivers and .jar file.
Install the respective driver jar package.

Here is a sample installation guide for the MariaDB database.

Cent OS

yum install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb
RHEL 8

curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo bash mariadb_repo_setup --mariadb-server-version=10.11
sudo dnf install boost-program-options -y
sudo dnf module reset mariadb -y
sudo yum install MariaDB-server MariaDB-client MariaDB-backup -y
sudo systemctl enable --now mariadb
All MySQL connectors work unchanged with MariaDB. Hence refer to the MySQL section to install jdbc connector jar and provide the necessary permissions.
After the necessary permissions are given, create a user for your service and grant it the necessary permissions as shown below:

mysql -u root -p
CREATE USER '[USERNAME]'@'%' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'%';
CREATE USER '[USERNAME]'@'localhost' IDENTIFIED BY '[PASSWORD]'; GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'localhost';
CREATE USER '[USERNAME]'@'[SERVICE_SERVER_FQDN]' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'[SERVICE_HOST_FQDN]';
FLUSH PRIVILEGES;
CREATE DATABASE [DATABASE_NAME];

OracleDB

Before you begin

Determine the appropriate database version and obtain the release drivers and .jar file.
On the Ambari server host, download the Oracle JDBC (OJDBC) Driver from Oracle.

Here is a sample installation and setup guide for the OracleDB database and the respective connector jar.

curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
yum -y install oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/jdbc/2110/ojdbc8.jar
cp ojdbc8.jar /usr/share/java/
chmod 644 /usr/share/java/ojdbc8.jar
After you perform the above action, configure and start the database. Following this, create a user for your service and grant it the respective permissions as shown below:

./schematool -initSchema -dbType oracle
sqlplus sys/root as sysdba
CREATE USER [USERNAME] IDENTIFIED BY [PASSWORD] default tablespace "USERS" temporary tablespace "TEMP";
GRANT unlimited tablespace to [USERNAME];
GRANT create session to [USERNAME];
GRANT create TABLE to [USERNAME];
GRANT create SEQUENCE to [USERNAME];
QUIT;

Example: Install MariaDB for Use with Multiple Components

Before deploying an Ambari-managed cluster, set up a secure MariaDB database and db users for each component with sufficient permissions.

Before you begin

Determine the appropriate database version and obtain the release drivers and .jar file.

This example is specific to CentOS/RHEL 7 OS systems. For production setups, please consider installing the database instance on a dedicated host and configuring master-slave replication.

Procedure

After you meet the prerequisites, perform the following steps:

On a dedicated host, download the MySQL Connector/JDBC driver from MySQL.
Install MySQL packages and configure them to start on boot, as shown below:

yum install mariadb-server -y
systemctl start mariadb systemctl enable mariadb

Secure the installation. /usr/bin/mysql_secure_installation
Create a database and users as shown below. Here, "%" signifies any host on your domain. Add localhost permissions explicitly.

mysql -uroot -p
create database hive;
grant all privileges on hive.* to 'hive'@'localhost' identified by '[YOUR_PASSWORD]';
grant all privileges on hive.* to 'hive'@'%. [YOUR_DOMAIN_NAME]' identified by '[YOUR_PASSWORD]';
create database ranger;
grant all privileges on ranger.* to 'ranger'@'localhost' identified by '[YOUR_PASSWORD]';
grant all privileges on ranger.* to 'ranger'@'%. [YOUR_DOMAIN_NAME]' identified by '[YOUR_PASSWORD]';
create database rangerkms;
grant all privileges on rangerkms.* to rangerkms@'localhost' identified by '[YOUR_PASSWORD]';
grant all privileges on rangerkms.* to rangerkms@'%. [YOUR_DOMAIN_NAME]' identified by '[YOUR_PASSWORD]';
create database oozie;
grant all privileges on oozie.* to 'oozie'@'localhost' identified by '[YOUR_PASSWORD]';
grant all privileges on oozie.* to 'oozie'@'%. [YOUR_DOMAIN_NAME]' identified by '[YOUR_PASSWORD]';
exit;

Install the driver on the Ambari host.

yum install mysql-connector-java -y
ambari-server setup --jdbc-db=mysql --jdbc-driver=</path/to/mysql-connector-java.jar>

Using an Existing Database with Services

Ambari

Other than the embedded PostgreSQL database instance that Ambari Server uses by default, Ambari Server can be set up with externally installed databases.

Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to perform the following steps.

Validate if the database service is installed and up.
On the Ambari Server host, stage the appropriate JDBC driver file.
Run ambari-server setup --jdbc-db=<database> --jdbc-driver=/path/to/jdbc-driver.jar where <database> is ‘oracle’, ‘mysql’, or ‘postgres’.
Create an Ambari service user and database, where [USERNAME] is the Ambari user name, [PASSWORD] is the Ambari user password, and [DATABASE_NAME] is the Ambari user database.
Load the Ambari Server database schema.

You must pre-load the Ambari database schema into your MySQL/MariaDB database using the schema script. Run the script in the same location where you find the Ambari-DDL-MySQL-CREATE.sql file. You should find the Ambari-DDL-MySQL-CREATE.sql file in the /var/lib/ambari-server/resources/ directory of the Ambari Server host, after you install Ambari Server.

MySQL/MariaDB

mysql -u [USERNAME] -p
USE [DATABASE_NAME];
SOURCE /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql;
SHOW TABLES;
PostgreSQL

[root@localhost ~]# psql -U [USERNAME] -d [DATABASE_NAME]
Password:
psql (15.5)
Type "help" for help.
ambari=> \c ambari
Password:
You are now connected to database "ambari" as user "ambari".
ambari=> \i /var/lib/ambari-server/resources/Ambari-DDL-Postgres-CREATE.sql
Oracle

sqlplus [USERNAME]/[PASSWORD] < /var/lib/ambari-server/resources/Ambari-DDL-Oracle-CREATE.sql
Now, start the Ambari server using ambari-server start.