How to Install and Configure MariaDB on CentOS/RHEL 7

Relational Databases

A relational database is a mechanism that allows the persistence of data in an organized way. Databases store data items organized as a set of tables, with each table representing an entity. In a given table, each row corresponds to a record, while each column corresponds to an attribute of that record.

MariaDB [inventory]> SELECT * FROM product;
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
| 1  | ThinkServer TS140 | 539.88  | 20    | 2           | 4               |
| 2  | ThinkServer TS440 | 1736.00 | 10    | 2           | 4               |
| 3  | RT-AC68U          | 219.99  | 10    | 1           | 3               |
| 4  | X110 64GB         | 73.84   | 100   | 3           | 1               |
+----+-------------------+---------+-------+-------------+-----------------+
4 rows in set (0.00 sec)

MariaDB [inventory]> SELECT * FROM category;
+----+------------+
| id | name       |
+----+------------+
| 1  | Networking |
| 2  | Servers    |
| 3  | Ssd        |
+----+------------+
3 rows in set (0.00 sec)

MariaDB [inventory]> SELECT * FROM manufacturer;
+----+----------+----------------+-------------------+
| id | name     | seller         | phone_number      |
+----+----------+----------------+-------------------+
| 1  | SanDisk  | John Miller    | +1 (941) 329-8855 |
| 2  | Kingston | Mike Taylor    | +1 (341) 375-9999 |
| 3  | Asus     | Wilson Jackson | +1 (432) 367-8899 |
| 4  | Lenovo   | Allen Scott    | +1 (876) 213-4439 |
+----+----------+----------------+-------------------+
4 rows in set (0.00 sec)

The previous tables show:

The product table has four records. Each record has six attributes: (id, name, price, stock, id_category, id_manufacturer).
X110 64GB is an SSD manufactured by SanDisk.
The seller responsible for the ThinkServer TS140 product is Allen Scott.

There are two relational database packages provided with CentOS/RHEL 7: 1. PostgreSQL - An open-source database developed by the PostgreSQL Global Development Group, consisting of Postgres users (both individuals and companies) and other companies and volunteers, supervised by companies such as Red Hat and EnterpriseDB. 2. MariaDB - A community-developed branch of MySQL built by some of the original authors of MySQL. It offers a rich set of feature enhancements, including alternate storage engines, server optimizations, and patches. The MariaDB Foundation works closely and cooperatively with the larger community of users and developers in the spirit of free and open-source software.

MariaDB Installation

A full MariaDB database installation requires both the mariadb and mariadb-client groups of software to be installed. The following packages will be installed with the mariadb group:

mariadb-server — The MariaDB server and related files (mandatory package).
mariadb-bench — MariaDB benchmark scripts and data (optional package).
mariadb-test — The test suite distributed with MariaDB (optional package).

The following packages will be installed with the mariadb-client group:

mariadb - A community-developed branch of MySQL (mandatory package).
MySQL-python — A MariaDB interface for Python (default package).
mysql-connector-odbc — ODBC driver for MariaDB (default package).
libdbi-dbd-mysql — MariaDB plug-in for libdbi (optional package).
mysql-connector-java — Native Java driver for MariaDB (optional package).
perl-DBD-MySQL — A MariaDB interface for Perl (optional package).

The /etc/my.cnf file has default configurations for MariaDB, such as the data directory, socket bindings, and log and error file location.

Note: Instead of adding new configurations to the /etc/my.cnffile, a newly created file named *.cnf can be added to the /etc/my.cnf.d/ directory holding the configuration of MariaDB.

In this example, we will explain to install a MariaDB database server.

1. Install MariaDB on serverX with the yum command.

# yum groupinstall mariadb mariadb-client -y

2. Start the MariaDB service on serverX with the systemctl command.

# systemctl start mariadb

Note: The default MariaDB log file is /var/log/mariadb/mariadb.log. This file should be the first place to look when troubleshooting MariaDB.

3. Enable the MariaDB service to start at boot on serverX.

# systemctl enable mariadb

4. Verify the status of the service on serverX.

# systemctl status mariadb

The status option reports some attributes, if the database is started:

Loaded — Shows if this service is loaded and enabled.
Active — Shows if this service is activated.
Main PID — Shows the main process ID from this service.
CGroup — Shows all processes that belong to this service.

Note: If the database is stopped, the status option will report the last known PID, and that the service is inactive

Improve MariaDB Installation Security

MariaDB provides a program to improve security from the baseline install state. Run mysql_secure_installation without arguments:

# mysql_secure_installation

This program enables improvement of MariaDB security in the following ways:

Sets a password for root accounts.
Removes root accounts that are accessible from outside the local host.
Removes anonymous-user accounts.
Removes the test database.

The script is fully interactive and will prompt for each step in the process.

Mariadb And Networking

MariaDB can be configured to be accessed remotely, or limited to just local connections.

In the first scenario, the database can only be accessed locally. Security is greatly improved, because the records will only be accessed from applications that are on the same server. The disadvantage is that the server will share the same resources with other services, and this may impact performance in the database server.

In the second scenario, the database can be accessed remotely. In this case, safety decreases because another port is opened on the server, which may result in an attack. On the other hand, the performance of the server increases by not having to share resources. When MariaDB is accessed remotely, by default, the server listens for TCP/IP connections on all available interfaces on port 3306.

Note: Although the MariaDB service listens on all interfaces by default, no users have remote access permission, also by default.

Configuring MariaDB networking

MariaDB network configuration directives are found in the **/etc/my.cnf **file, under the [mysqld] section.

bind-address

The server will listen based on this directive. Only one value may be entered. Possible values are:

Host name
IPv4 address
IPv6 address
Set this value to :: to connect to all available addresses (IPv6 and IPv4), or leave blank (or set to 0.0.0.0) for all IPv4 addresses.

Note: There can be only one bind-address entry in /etc/my.cnf. On a system with multiple addresses, selecting a single address is possible, or all addresses, but nothing in between.

skip-networking

If set to 1, the server will listen only for local clients. All interaction with the server will be through a socket, located by default at /var/lib/mysql/mysql.sock. This location can be changed with a socket value in /etc/my.cnf.

Be aware that if networking is shut off in this manner, this disables connections via localhost as well. The MySQL client can still make local connections through the socket file automatically.

port

Port to listen on for TCP/IP connections. For remote access, the firewall needs to be modified. Fortunately, it is a known service, so it can be simply added via:

# firewall-cmd --permanent --add-service=mysql
# firewall-cmd --reload