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

Note Here is a sample installation guide for 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:

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

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

After ensuring that the prerequisites have been met, proceed by following the provided steps below:

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

Note: Here is a sample installation guide for 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

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

Note Here is a sample installation and setup guide for 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 shown action, configure and start the database. Following this, create a user for your service and grant it 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 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:

1. On a dedicated host, Download the MySQL Connector/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 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 driver on 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 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 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 a 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.

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.

Oozie

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 oozie service user and database, where [USERNAME] is the oozie user name, [PASSWORD] is the oozie user password and [DATABASE_NAME] is the oozie 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.