Using an Existing Database or Installing a Default One

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.

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

RHEL 8/9:

sudo yum install mysql-server 

sudo yum install mysql-connector-java*

systemctl start mysqld

Ubuntu 20/22:

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 install -f mysql-server

sudo systemctl start mysql

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:

1. 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 the 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

2. 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 respective driver jar package.

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

RHEL 8/9:

#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

Note You can also get the Postgres 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/.

Once the prerequisites are met, perform the following steps:

1. Configure and start the database service:

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

listen_addresses = '*'

Note 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.

2. 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

3. 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();

4. 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:

Note Restart the PostgreSQL service every time after editing the 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.

Note Here is a sample installation guide for the MariaDB database.

RHEL 8/9

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

Note All MySQL connectors work unchanged with MariaDB. Hence, refer to MySQL section to install the JDBC connector jar and provide the necessary permissions.

After the necessary permissions are given, create a user for your service and grant 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.

Note 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 performing the above shown action, configure and start the database. Following this, create a user for your service and grant 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.

Note This example is specific for the RHEL 8/9 OS system. For production setups, please consider installing the database instance on a dedicated host and configuring master-slave replication.

Procedure

Once the prerequisites are met, perform the following steps:

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

yum install mariadb-server -y 

​

systemctl start mariadb systemctl enable mariadb

3. Secure the installation. /usr/bin/mysql_secure_installation
4. 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;

5. 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, the Ambari Sever can be set up with externally installed databases.

Note 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 or MariaDB database using the schema script. Run the script in the same location where you find the Ambari-DDL-MySQL-CREATE.sql file. You must find the Ambari-DDL-MySQL-CREATE.sql file in the /var/lib/ambari-server/resources/ directory of the Ambari Server host, after you install the 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.

Hive

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create a hive service user and database, where [USERNAME] is the hiveuser name, [PASSWORD] is the hive user password, and [DATABASE_NAME] is the hive user database.

NiFi Registry

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create a registry service user and database where [USERNAME] is the registry user name, [PASSWORD] is the registry user password, and [DATABASE_NAME] is the registry user database.

Schema Registry

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create a schema registry service user and database, where [USERNAME] is the schema registry user name, [PASSWORD] is the schema registry user password, and [DATABASE_NAME] is the schema registry user database.

Airflow

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create an airflow service user and database where [USERNAME] is the airflow user name, [PASSWORD] is the airflow user password, and [DATABASE_NAME] is the airflow user database.

Ranger

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create a ranger service user and database, where [USERNAME] is the ranger user name, [PASSWORD] is the ranger user password, and [DATABASE_NAME] is the ranger user database.

Ranger KMS

Note Refer to the respective instructions for databases like MySQL, PostgreSQL, MariaDB, and OracleDB to carry out the following steps:

• Validate if the database service is installed and up.
• On the Ambari Server host, stage the appropriate JDBC driver file.
• Create a rangerkms service user and database, where [USERNAME] is the ranger user name, [PASSWORD] is the rangerkms user password, and [DATABASE_NAME] is the rangerkms user database.