RH358 configuring MariaDB SQL database – installing MariaDB database

Chapter 7 introduces how to configure the database MariaDB. Although it is not deep enough, it is a good start for beginners. It is a more practical chapter in 358 course.

1. Describe the importance of relational databases

Relational databases store structured data in a set of interrelated information tables. This means that information in one table can be easily combined with information in another table to process and provide reports on the data, possibly using only the selected subset of data in each table.

relational database management system (RDBMS) is a software used to manage relational databases. Most relational database management systems allow the use of Structured Query Language (SQL) to find and manage data in the database.

Many people use relational databases to store business information for inventory tracking, sales, and financial information. Relational database management systems also play a key role in many application stacks. For example, many web applications that need to support dynamic content generation are built around the LAMP solution stack, including:

• Provides the basic environment of Linux operating system.

• Apache HTTPS server (or other web server, such as Nginx).

• MariaDB, MySQL, or other relational databases (such as PostareSQL) to store site data.

• A programming language run by a web server, such as PHP, Python, Perl, Ruby, Java, server-side JavaScript, or others, that can update data in a database and use it to dynamically build web pages for users.

Because relational databases are usually integrated with applications managed by system administrators, even if you are not a database administrator, you must understand the installation and management of these services and their data.

2. Install MariaDB

Red Hat includes many relational database management systems Enterprise Linux 8. The three most commonly used options are MariaDB, mysql, and postqresq. This course is represented by MariaDB. MariaDB is a community development branch of MySQL. It is designed to be highly compatible with the database to facilitate the conversion from one database to another and is widely used.

In RHEL 8, MariaDB is provided by the AppStream repository, which means that it may be supported in a separate lifecycle than the operating system. This allows Red Hat to provide an updated version of MariaDB as an option during the life cycle of the operating system and deactivate the old version of the software at the end of the natural life cycle of the software.

For example, MariaDB 10.3 will be available with RHEL 8.0.0 in May 2019, and its support will end in May 2023. For more information, see https://access.redhat.com/support/policy/updates/rhel8pp-streams-life-cycle/ .

You can use the yum module list command to list all modules in AppStream, or use the following command to view the versions of MariaDB:

[root@host ~]# yum module list mariadb
Updating Subscription Management repositories.
Last metadata expiration check: 1:51:18 ago on Mon 15 Jun 2020 10:30:35 AM CDT.
Red Hat Enterprise Linux 8 for x86_64 - AppStream (RPMs)
Name Stream Profiles Summary
mariadb 10.3 [d][e] client, galera, server [d] [i] MariaDB Module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

To install MariaDB 10.3 and all necessary server and client packages, install the MariaDB module using a specific stream.

[root@host ~]# yum module install mariadb:10.3/server

**Important: * * RHEL 8 RPM packages of MariaDB and MySQL database servers cannot be installed at the same time due to packaging conflict because they provide very similar commands and components.

Start and enable mariadb Service.
[root@host ~]# systemctl enable --now mariadb

take mysql Services are added to the firewall to allow remote access MariaDB The server.
[root@host ~]# firewall-cmd --permanent --add-service=mysql
[root@host ~]# firewall-cmd --reload

If you only want to install the client package, you can use the following command. If MariaDB This can happen if you are running on a separate system and you want to connect to it over the network.
[root@host ~]# yum install mariadb

3. Secure installation of MariaDB

The default configuration for the new MariaDB service may have a test database and some less secure configuration settings. Run mysql_secure_installation to configure more secure defaults.

[root@host ~]# mysql_secure_installation

This interactive script prompts you to make some changes, including:

• Set the password for the root account.

• Delete the root account that can be accessed from outside the local host.

• Delete anonymous user accounts.

• Delete the test database used for the demonstration, if it exists.

4. Connection management of MariaDB

The MariaDB client can connect to the server in two ways.

If the client is running on the same machine as the server, it can connect to a special socket file to communicate with MariaDB. This is more secure because MariaDB does not need to listen for connections from network clients. However, if you rely on this approach, all clients using the database need to run on the MariaDB server, which may affect performance.

Alternatively, the client can connect to the MariaDB service over a TCP/IP network. Both remote clients and clients running on the same host as the MariaDB server may use this method. If this feature is enabled, the server listens for connections on port 3306/TCP by default.

Control network availability

Both methods are enabled by default. MariaDB listens to the network addresses of all systems to connect to 3306/TCP, and the socket file is available.

To completely shut down the TCP/IP network and rely on the local socket file, or to limit which network address MariaDB will use, you need to edit the MariaDB configuration. Its main configuration file is / etc / my CNF, but the file automatically contains / etc / my All files in the conf.d directory are part of the configuration file. You can add / etc / my cnf. d/mariadb-server. Add instructions in the [mysqld] section of the CNF file to adjust the network settings of the server.

bind-address

This instruction specifies the network address used by MariaDB to listen for client connections.

Only one option can be entered. Possible options include:

Single IPv4 address

A single IPv6 address.

:: connect to all available addresses (IPv6 and IPv4).

All IPv4 addresses are left blank (or set to 0.0.0.0).

If you want local clients to be able to use a network connection without allowing remote access to MariaDB, you can use 127.0.0.1 or:: 1 as the network address.

Important: only one bound address table entry can be used. On a system with multiple addresses, you can use this instruction to select all addresses or one address, but not several addresses.

skip-networking

If skip networking or skip networking = 1 is set in the [mysqld] section of the configuration file, networking is disabled and the client must use the socket file to communicate with MariaDB. This limits access to clients on the same system as MariaDB.

If you set skip networking = O (the default if not set), MariaDB will listen for network connections.

port

You can use this setting to specify a network port instead of 3306/TCP.

**Important: * * if the firewall of the system has been enabled, don't forget to allow the connection to the port of MariaDB service. As mentioned earlier, in order to allow users to remotely access MariaDB, you also need to configure the user account, MariaDB password and database permissions in MariaDB service.

5. Textbook exercises

[student@workstation ~]$ lab database-intro start

You are required to install the MariaDB database server on servera. Install and protect the MariaDB service and configure it to accept only connections from local clients.

1. Install MariaDB Server package.

[root@servera ~]# yum -y install mariadb-server

2. Start and enable the mariadb service.

[root@servera ~]# systemctl enable --now mariadb

3. Configure mariadb to listen only to local clients.

# Use the skip networking = 1 command to listen only to local clients.
[root@servera ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
skip-networking=1

[root@servera ~]# systemctl restart mariadb

# Verify that the MariaDB daemon mysqld no longer listens to all interfaces.
[root@servera ~]# ss -tulpn | grep mysqld
[root@servera ~]#

4. Use MySQL_ secure_ The installation command protects the MariaDB service.

# Set the root user password to redhat, and answer Y to all other questions.
[root@servera ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:  redhat
Re-enter new password: redhat
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

# Verify that the root user cannot log in without a password.
[root@servera ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# Confirm that the test database has been deleted.
[root@servera ~]# mysql -u root -p
Enter password: redhat
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> exit;
Bye

Complete the experiment

[student@workstation ~]$ lab database-intro finish

summary

• Introduce the importance of database.
• How to install MariaDB.
• How to perform secure installation and connection management.
• If you like a little girl's article, please give it a compliment.