CentOS 8.2 deploying MariaDB 10.5.3 database service

Time:2021-12-2

What is a MariaDB server?


MariaDBIs a free open sourceRDBMS (relational database management system)。 MariaDB isMySQLA branch of the database maintained by some original developers of the MySQL project. Like its predecessor,MariaDBIt is mainly used as the back-end database of [PHP based server]. manyFOSS (free and open source software)The utility also uses MariaDB as its data repository.

The MariaDB server is highly scalable and can be used as a [standalone based] or [Galera based MariaDB cluster] environment.

MariaDB Server 10.5.3Released on May 12, 2020, with many new features and some major changes. This is the latest version at the time of writing.

New features in MariaDB server 10.5:


Some changes and features in MariaDB server 10.5 include:

  • All previouslymysqlBinary files that begin with are nowStarting with MariaDBWith correspondingmysqlSymbolic links for commands
  • S3 storage engine – used to archive MariaDB tables inAmazon S3Or any third-party public or private cloud that implements the S3 API
  • Both the S3 table and the partitioned S3 table are discoverable. This means that if you create a partitioned S3 table, the partitioned table and its partitions can be used directly by another server that has access to S3 storage.
  • By segmentationSUPERPrivileges to add new privileges so that you can fine tune the actions that each user can perform
  • Speed up binary line recording code
  • Increase connection speed (up to 25%)

You canMariaDB official websiteA complete list of new features in MariaDB 10.5.3.

Environmental specifications:


  • CPU: 3.4 GHz (2 cores)
  • Memory:2 GB
  • Storage space:20 GB
  • operating system:CentOS 8.2
  • host name:mariadb.lianglab.cn
  • IP address:192.168.6.200/24

Step 1: update CentOS 8 package


usesshTools torootConnect to as usermariadb.lianglab.cn

Best practices use the latest Linux packages

[[email protected] ~]# hostnamectl set-hostname mariadb.lianglab.cn

[[email protected] ~]# dnf update -y
Dependencies resolved.
Nothing to do.
Complete!

Step 2: install MariaDB Yum repository in CentOS 8


MariaDBServer 10.5.3 is the latest version, so inCentOS8 is not available in the yum repository.

MariaDBProvides a formal Yum repository that we can use inCentOS8 installation onMariaDB Server 10.5 。

【1】 Create the yum repository file as follows.

[[email protected] ~]# vi /etc/yum.repos.d/MariaDB.repo

Add the following directives to this file.

# MariaDB 10.5 CentOS repository list - lianglab created 2020-12-26
# https://www.cnblogs.com/lianglab/
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos8-amd64
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

【2】 MariaDB built the cache using Yum repository.

[[email protected] ~]# dnf clean all
44 files removed
[[email protected] ~]# dnf makecache
CentOS-8 - Base - mirrors.tongdun.cn                                         1.2 MB/s | 3.9 kB     00:00    
CentOS-8 - Extras - mirrors.tongdun.cn                                       817 kB/s | 1.5 kB     00:00    
CentOS-8 - AppStream - mirrors.tongdun.cn                                    2.7 MB/s | 4.3 kB     00:00    
MariaDB                                                                      1.7 kB/s | 2.9 kB     00:01    
bintray--apache-couchdb-rpm                                                  2.1 kB/s | 1.3 kB     00:00    
Extra Packages for Enterprise Linux 8 - x86_64                               2.7 MB/s | 4.7 kB     00:00    
Extra Packages for Enterprise Linux 8 - x86_64 - Debug                       1.6 MB/s | 3.0 kB     00:00    
Extra Packages for Enterprise Linux 8 - x86_64 - Source                      2.1 MB/s | 3.5 kB     00:00    
Metadata cache created.

Step 3: install MariaDB server 10.5 on CentOS 8


【1】 We have added the MariaDB Yum repository and now we can use itdnfCommand to easily install MariaDB server.

[[email protected] ~]# dnf install -y MariaDB-server
Last metadata expiration check: 0:00:26 ago on Sat 26 Dec 2020 09:55:49 PM CST.
Dependencies resolved.
=============================================================================================================
 Package                      Architecture         Version                     Repository               Size
=============================================================================================================
Installing:
 MariaDB-server               x86_64               10.5.8-1.el8                mariadb                  27 M
Installing dependencies:
 MariaDB-client               x86_64               10.5.8-1.el8                mariadb                  13 M
 MariaDB-common               x86_64               10.5.8-1.el8                mariadb                  87 k
 MariaDB-shared               x86_64               10.5.8-1.el8                mariadb                 115 k
 galera-4                     x86_64               26.4.6-1.el8                mariadb                  13 M
 socat                        x86_64               1.7.3.3-2.el8               AppStream               302 k
===========================================Omit=====================================================
Downloading Packages:
(1/6): socat-1.7.3.3-2.el8.x86_64.rpm                                        6.2 MB/s | 302 kB     00:00    
(2/6): MariaDB-common-10.5.8-1.el8.x86_64.rpm                                6.8 kB/s |  87 kB     00:12    
(3/6): MariaDB-shared-10.5.8-1.el8.x86_64.rpm                                 27 kB/s | 115 kB     00:04    
[MIRROR] MariaDB-server-10.5.8-1.el8.x86_64.rpm: Curl error (28): Timeout was reached for http://yum.mariadb.org/10.5/centos8-amd64/rpms/MariaDB-server-10.5.8-1.el8.x86_64.rpm [Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds]
[MIRROR] galera-4-26.4.6-1.el8.x86_64.rpm: Curl error (28): Timeout was reached for http://yum.mariadb.org/10.5/centos8-amd64/rpms/galera-4-26.4.6-1.el8.x86_64.rpm [Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds]

===========================================Omit=====================================================

[[email protected] tdops]# dnf -y install galera-4-26.4.6-1.el8.x86_64.rpm  MariaDB-server-10.5.8-1.el8.x86_64.rpm  MariaDB-client-10.5.8-1.el8.x86_64.rpm

Installed:
  MariaDB-client-10.5.8-1.el8.x86_64       MariaDB-common-10.5.8-1.el8.x86_64       MariaDB-server-10.5.8-1.el8.x86_64       MariaDB-shared-10.5.8-1.el8.x86_64      
  galera-4-26.4.6-1.el8.x86_64            
Complete!

[[email protected] tdops]# dnf install -y MariaDB-server
Last metadata expiration check: 0:21:18 ago on Sat 26 Dec 2020 09:55:49 PM CST.
Package MariaDB-server-10.5.8-1.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!

【2】 StartMariaDBService.

[[email protected] ~]# systemctl enable --now mariadb.service
Created symlink /etc/systemd/s[[email protected] tdops]# systemctl enable --now mariadb.service
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

【3】 VerifyMariaDBService status and listening port

[[email protected] ~]# systemctl status mariadb.service
â mariadb.service - MariaDB 10.5.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor pre>
  Drop-In: /etc/systemd/system/mariadb.service.d
           ââmigrated-from-my.cnf-settings.conf
   Active: active (running) since Fri 2020-05-29 13:59:04 PKT; 12s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 12496 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_ST>
  Process: 12475 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && V>
  Process: 12473 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_STA>
 Main PID: 12485 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 11 (limit: 5916)
   Memory: 75.8M
   CGroup: /system.slice/mariadb.service
           ââ12485 /usr/sbin/mysqld

May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>

[[email protected] tdops]# netstat -anptl | grep mariadb
tcp6       0      0 :::3306                 :::*                    LISTEN      125273/mariadbd     
[[email protected] tdops]#

Step 4: MariaDB database user password setting

  • The root user of Linux can connect toMariaDB’sOf server MariaDBrootThe user does not need any password
  • Linux mysqlUsers can act as MariaDB without any passwordmysqlThe user connects to the MariaDB server

To allow other Linux users torootormysqlTo log in as a user, we need to set passwords for these database users.

【1】 Use newmariadbCommand to connect to the MariaDB server.

[[email protected] ~]# mariadb -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]>

【2】 ForrootandmysqlUser set strong password to[email protected]

MariaDB [(none)]> alter user 'root'@'localhost' identified by '[email protected]';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> alter user 'mysql'@'localhost' identified by '[email protected]';
Query OK, 0 rows affected (0.001 sec)

【3】 Reload the privilege table.

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

【4】 Lists the available databases in the MariaDB server.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)

【5】 Exit from the MariaDB shell.

MariaDB [(none)]> exit
Bye

【6】 Our MariaDB server 10.5 has been successfully installed.

Blogger site:https://www.cnblogs.com/lianglab/

Step 5: configure database security for MariaDB server


We can choose to delete the test database and restrict itrootUser’s remote login.

We can use the new onemariadb-secure-installationCommand.

[[email protected] ~]# mariadb-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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): ## enter the password we created [email protected]
OK, successfully used password, moving on...

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

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

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!
[[email protected] ~]#

We have successfully installed MariaDB server 10.5 on CentOS 8.2 and configured administrator users and database security.