Configure a database on Red Hat Enterprise Linux

Our given task is:

Configure a database.

Create a MariaDB database named employees on system1 such that the following conditions exist:

– The database should contain the contents of the database dump from http://rhgh.domain9.example.com/materials/employees.mdb
– The database should be accessible from localhost only.
– Other than the root user this database only allows queries from the user luigi. This user should have the password wakennym.
– The root user should have the password wakennym and must not be allowed to log in without using a password.

First we will install mariadb and mariadb-client group of packages:
yum groupinstall mariadb mariadb-client

Enable MariaDB to start automatically after the reboot:
systemctl enable mariadb

Verify if MariaDB is configured to start automatically after the reboot:
systemctl is-enabled mariadb

Start MariaDB:
systemctl start mariadb

Check status of MariaDB:
systemctl status mariadb

Verify that MariaDB is listening on all interfaces:
ss -tulpn | grep mysql

Part of this RHCE task is that the database should be accessible from localhost only.
So we enable the skip networking directive:
vi /etc/my.cnf

Add line “skip-networking=1” to [mysqld] section:
skip-networking=1

Restart MariaDB:
systemctl restart mariadb

Verify status of MariaDB:
systemctl status mariadb

Verify that MariaDB is not listening on any interface:
ss -tulpn | grep mysql

Above command shouldn’t return anything.

Secure MariaDB service using the mysql_secure_installation tool. Set the root password to wakennym, and answer yes to all other questions.
mysql_secure_installation

Enter current password for root (enter for none):

We haven’t set root password yet – so we press enter here.

Set root password? [Y/n]

It is part of this RHCE task that the root user should have the password wakennym and must not be allowed to log in without using a password. So press lower or upper case Y for yes.

Type a new password which should be wakennym here.
Re-enter new password.

Remove anonymous users? [Y/n]

Our RHCE task says that other than the root user this database only allows queries from the user luigi. So we press Y for yes now.

Disallow root login remotely? [Y/n]

Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess at the root password from the network. So we press Y for yes here as well as this one is important security hardening.
And also our RHCE task says that the database should be accessible from localhost only anyway.

We will answer yes also to our last two questions.

Remove test database and access to it? [Y/n] Y

Reload privilege tables now? [Y/n] Y

Connect to the MariaDB database as user root using mysql command:

mysql -u root -p

mysql : is a client to connect to the MariaDB database server.
-u : is option to specify the username for this connection.
root : username for this connection.
-p : is option to prompt for password.

Our RHCE task is to create MariaDB database named employees on system1.
So we will create it using command:
CREATE DATABASE employees;

We will verify if the database employees has been created successfully with command:
SHOW DATABASES;

Our next task is to create user luigi and set password wakennym for him:
CREATE USER luigi@localhost IDENTIFIED BY 'wakennym';

Database should allow queries from the user luigi:
GRANT SELECT ON employees.* TO luigi@localhost;

Now we need to reload privileges using ‘flush privileges’ command:
FLUSH PRIVILEGES;

Display privileges that are assigned to the user luigi:
SHOW GRANTS FOR luigi@localhost;

Disconnect from the MariaDB database and exit mysql client:
EXIT;

Connect to the MariaDB database as user luigi:
mysql -u luigi -p

Connect to the database employees to confirm that user luigi can use it:
USE employees;

Disconnect from the MariaDB database and exit mysql client:
EXIT;

The database should contain the contents of the database dump from http://rhgh.domain9.example.com/materials/employees.mdb

First install wget utility to download employees.mdb file:
yum install wget

Download employees.mdb file:
wget http://rhgh.domain9.example.com/materials/employees.mdb

Reload employees.mdb dump file which was written by mysqldump:
mysql -u root -p employees < employees.mdb

Verify if employees database was restored successfully and if user luigi can see and query all tables.
Connect as user luigi:
mysql -u luigi -p

Use the employees database:
USE employees;

List tables:
SHOW TABLES;

Query data from the employees table:
SELECT * FROM employees;

Disconnect from the MariaDB database:
EXIT;

Please remember that on your RHCE exam SELinux must be set to enforcing mode and firewall must be running.

Please also remember that all your RHCE tasks will be graded after reboot so it is recommended for you to reboot system1 server and double-check your MariaDB database after the reboot.

Employees Sample Database:

https://dev.mysql.com/doc/employee/en/
https://github.com/datacharmer/test_db

Related Video Tutorials:
RHCE EX300 Exam:
Configure an iSCSI target on Red Hat Enterprise Linux

Configure an iSCSI initiator (client) on Red Hat Enterprise Linux

Query a database on Red Hat Enterprise Linux