How to Install MySQL Database Server in CentOS 8 Running in VirtualBox Application

Posted on

Introduction

This article will show how to install MySQL Database Server in CentOS 8. The server in this context is a virtual server. It has a CentOS 8 operating system running in it. The virtual server is available and run as an emulation server in the VirtualBox application. Basically, in order to install the MySQL Database Server, there are several steps to achieve it. The first one is the main installation process. After the installation of MySQL Database Server is a success, there are another important part consisting of several steps. Those steps are the configuration steps. The configuration steps consists of adding rule to the firewall service, starting and enabling the service. Furthermore, there is a step for adding a forwarding port in a NAT interface type since it runs in a VirtualBox application.

MySQL Database Server Installation Process

This is the first part for the installation of MySQL Database Server. The following steps are the steps for installing MySQL Database Server  :

1. There are several options for MySQL Database Server installation. Directly using root account or just a normal user. In order to perform a secure installation, just use a normal user. But make sure that the normal user can execute all the commands. Read the article with the title of ‘How to Add User as Super User in CentOS 8 running in a VirtualBox Application’ in this link. The following is a snippet code of showing the remote process to the virtual server using SSH method with a normal user of ‘admin’ :

Microsoft Windows [Version 10.0.18362.1082]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\Personal>ssh -p 9922 admin@localhost
admin@localhost's password:
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Thu Dec 31 06:05:22 2020 from 10.0.2.2
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Thu Dec 31 06:05:22 2020 from 10.0.2.2
[admin@10 ~]$

2. After that, just execute the installation command as exist in the following command :

[admin@10 ~]$ sudo dnf install mysql-server
[sudo] password for admin:
Last metadata expiration check: 0:03:14 ago on Sun 03 Jan 2021 08:48:28 PM EST.
Dependencies resolved.
============================================================================================================================================================================ Package                                          Architecture               Version                                                    Repository                     Size
============================================================================================================================================================================Installing:
 mysql-server                                     x86_64                     8.0.21-1.module_el8.2.0+493+63b41e36                       AppStream                      22 M
Upgrading:
 libsemanage                                      x86_64                     2.9-3.el8                                                  BaseOS                        165 k
Installing dependencies:
 checkpolicy                                      x86_64                     2.9-1.el8                                                  BaseOS                        348 k
 libevent                                         x86_64                     2.1.8-5.el8                                                BaseOS                        253 k
 mariadb-connector-c-config                       noarch                     3.0.7-1.el8                                                AppStream                      13 k
 mecab                                            x86_64                     0.996-1.module_el8.2.0+493+63b41e36.9                      AppStream                     393 k
 mysql                                            x86_64                     8.0.21-1.module_el8.2.0+493+63b41e36                       AppStream                      12 M
 mysql-common                                     x86_64                     8.0.21-1.module_el8.2.0+493+63b41e36                       AppStream                     148 k
 mysql-errmsg                                     x86_64                     8.0.21-1.module_el8.2.0+493+63b41e36                       AppStream                     581 k
 policycoreutils-python-utils                     noarch                     2.9-9.el8                                                  BaseOS                        251 k
 protobuf-lite                                    x86_64                     3.5.0-13.el8                                               AppStream                     149 k
 python3-audit                                    x86_64                     3.0-0.17.20191104git1c2f876.el8                            BaseOS                         86 k
 python3-libsemanage                              x86_64                     2.9-3.el8                                                  BaseOS                        127 k
 python3-policycoreutils                          noarch                     2.9-9.el8                                                  BaseOS                        2.2 M
 python3-setools                                  x86_64                     4.3.0-2.el8                                                BaseOS                        626 k
Enabling module streams:
 mysql                                                                       8.0

Transaction Summary
============================================================================================================================================================================Install  14 Packages
Upgrade   1 Package

Total download size: 39 M
Is this ok [y/N]: y
Downloading Packages:
(1/15): mariadb-connector-c-config-3.0.7-1.el8.noarch.rpm                                                                                   244 kB/s |  13 kB     00:00
(2/15): mysql-common-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64.rpm                                                                        346 kB/s | 148 kB     00:00
(3/15): mecab-0.996-1.module_el8.2.0+493+63b41e36.9.x86_64.rpm                                                                              563 kB/s | 393 kB     00:00
(4/15): mysql-errmsg-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64.rpm                                                                        230 kB/s | 581 kB     00:02
(5/15): protobuf-lite-3.5.0-13.el8.x86_64.rpm                                                                                               233 kB/s | 149 kB     00:00
(6/15): checkpolicy-2.9-1.el8.x86_64.rpm                                                                                                    158 kB/s | 348 kB     00:02
(7/15): libevent-2.1.8-5.el8.x86_64.rpm                                                                                                     544 kB/s | 253 kB     00:00
(8/15): policycoreutils-python-utils-2.9-9.el8.noarch.rpm                                                                                   515 kB/s | 251 kB     00:00
(9/15): python3-audit-3.0-0.17.20191104git1c2f876.el8.x86_64.rpm                                                                            370 kB/s |  86 kB     00:00
(10/15): python3-libsemanage-2.9-3.el8.x86_64.rpm                                                                                           352 kB/s | 127 kB     00:00
(11/15): python3-policycoreutils-2.9-9.el8.noarch.rpm                                                                                       496 kB/s | 2.2 MB     00:04
(12/15): python3-setools-4.3.0-2.el8.x86_64.rpm                                                                                             493 kB/s | 626 kB     00:01
(13/15): libsemanage-2.9-3.el8.x86_64.rpm                                                                                                   384 kB/s | 165 kB     00:00
(14/15): mysql-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64.rpm                                                                              459 kB/s |  12 MB     00:26
(15/15): mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64.rpm                                                                       643 kB/s |  22 MB     00:35
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Total                                                                                                                                       1.1 MB/s |  39 MB     00:37
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                    1/1
  Installing       : mariadb-connector-c-config-3.0.7-1.el8.noarch                                                                                                     1/16
  Installing       : mysql-common-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                          2/16
  Installing       : mysql-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                                 3/16
  Installing       : mysql-errmsg-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                          4/16
  Upgrading        : libsemanage-2.9-3.el8.x86_64                                                                                                                      5/16
  Installing       : python3-libsemanage-2.9-3.el8.x86_64                                                                                                              6/16
  Installing       : python3-setools-4.3.0-2.el8.x86_64                                                                                                                7/16
  Installing       : python3-audit-3.0-0.17.20191104git1c2f876.el8.x86_64                                                                                              8/16
  Installing       : libevent-2.1.8-5.el8.x86_64                                                                                                                       9/16
  Installing       : checkpolicy-2.9-1.el8.x86_64                                                                                                                     10/16
  Installing       : python3-policycoreutils-2.9-9.el8.noarch                                                                                                         11/16
  Installing       : policycoreutils-python-utils-2.9-9.el8.noarch                                                                                                    12/16
  Installing       : protobuf-lite-3.5.0-13.el8.x86_64                                                                                                                13/16
  Installing       : mecab-0.996-1.module_el8.2.0+493+63b41e36.9.x86_64                                                                                               14/16
  Running scriptlet: mecab-0.996-1.module_el8.2.0+493+63b41e36.9.x86_64                                                                                               14/16
  Running scriptlet: mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                         15/16
  Installing       : mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                         15/16
  Running scriptlet: mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                         15/16
ValueError: File context for /var/log/mysql(/.*)? already defined

  Cleanup          : libsemanage-2.9-2.el8.x86_64                                                                                                                     16/16
  Running scriptlet: libsemanage-2.9-2.el8.x86_64                                                                                                                     16/16
  Verifying        : mariadb-connector-c-config-3.0.7-1.el8.noarch                                                                                                     1/16
  Verifying        : mecab-0.996-1.module_el8.2.0+493+63b41e36.9.x86_64                                                                                                2/16
  Verifying        : mysql-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                                 3/16
  Verifying        : mysql-common-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                          4/16
  Verifying        : mysql-errmsg-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                          5/16
  Verifying        : mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                                                                          6/16
  Verifying        : protobuf-lite-3.5.0-13.el8.x86_64                                                                                                                 7/16
  Verifying        : checkpolicy-2.9-1.el8.x86_64                                                                                                                      8/16
  Verifying        : libevent-2.1.8-5.el8.x86_64                                                                                                                       9/16
  Verifying        : policycoreutils-python-utils-2.9-9.el8.noarch                                                                                                    10/16
  Verifying        : python3-audit-3.0-0.17.20191104git1c2f876.el8.x86_64                                                                                             11/16
  Verifying        : python3-libsemanage-2.9-3.el8.x86_64                                                                                                             12/16
  Verifying        : python3-policycoreutils-2.9-9.el8.noarch                                                                                                         13/16
  Verifying        : python3-setools-4.3.0-2.el8.x86_64                                                                                                               14/16
  Verifying        : libsemanage-2.9-3.el8.x86_64                                                                                                                     15/16
  Verifying        : libsemanage-2.9-2.el8.x86_64                                                                                                                     16/16

Upgraded:
  libsemanage-2.9-3.el8.x86_64

Installed:
  checkpolicy-2.9-1.el8.x86_64                                                         libevent-2.1.8-5.el8.x86_64
  mariadb-connector-c-config-3.0.7-1.el8.noarch                                        mecab-0.996-1.module_el8.2.0+493+63b41e36.9.x86_64
  mysql-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                                    mysql-common-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64
  mysql-errmsg-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64                             mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64
  policycoreutils-python-utils-2.9-9.el8.noarch                                        protobuf-lite-3.5.0-13.el8.x86_64
  python3-audit-3.0-0.17.20191104git1c2f876.el8.x86_64                                 python3-libsemanage-2.9-3.el8.x86_64
  python3-policycoreutils-2.9-9.el8.noarch                                             python3-setools-4.3.0-2.el8.x86_64

Complete!
[admin@10 ~]$

The process above has a command pattern for MySQL Database Server installation. That command pattern exist as follows :

sudo dnf install mysql-server

sudo : it is a command to execute a command as another user.

dnf install mysql-server : it is a command to execute a tool with the name of dnf. Furthermore, this tool has a similar function with the previous tool named yum. It is the next upcoming major version of yum. It also works as a package manager for RPM-based Linux distributions. In the above command pattern, there is an additional argument ‘install’ which instruct ‘dnf’ tool to perform installation. Furthmore, the package name for further installation is defined as an argument. The argument is the package name which is ‘mysql-server’.

3. After finishing the installation process, don’t forget to check the program whether it has been installed or not. Actually, just execute the following command :

dnf list installed | grep mysql-server

The following image displaying on how to check it using normal user. There is another additional command for executing the above command as a normal user. The following is that command :

sudo dnf list installed | grep mysql-server
How to Install MySQL Database Server in CentOS 8 Running in VirtualBox Application
How to Install MySQL Database Server in CentOS 8 Running in VirtualBox Application

But normally as root the command execution above will in the following command pattern  :

dnf list installed | grep mysql-server

Fortunately, as in the above sequence fo steps, the installation of the MySQL Database Server is a success.

 

Adding MySQL Port Forwarding Rule in the Virtual Server Setting

After successfully installing MySQL database server, just add MySQL port forwarding rule in the virtual server setting. It is very useful for defining the port in order host machine to access the MySQL database server running in the virtual server. In order to add it, just read the article with the title of ‘How to Add MySQL Port Forwarding Rule to access MySQL Database Server of Guest Machine running in VirtualBox Manager from Host Machine’ in this link. In that article context, port 9933 in host machine is available for further usage to forward request to port 3306 of the database server running in the virtual server.

 

Adding MySQL Port Filter Rule in the Virtual Server Firewall

After successfully defining the port forwarding rule in the virtual server setting, there is another step in order to access the MySQL database server’s service. The step is to add filter rule in the virtual server firewall. The following are steps for adding MySQL port forwarding rule in a CentOS virtual server :

1. Start the VirtualBox application.

2. Start the virtual server.

3. Logging in to the virtual server. Either directly access login from the VirtualBox application or by accessing remotely using SSH connection. The article with the title of ‘How to Remote CentOS Virtual Server running in a VirtualBox with a NAT Network using SSH’ in this link is useful as a reference to access the virtual server remotely.

  1. Add the port to the firewall rule in order to allow incoming connection from any sources including the host machine. The series of the command is in the following image :
How to Install MySQL Database Server in CentOS 8 Running in VirtualBox Application
How to Install MySQL Database Server in CentOS 8 Running in VirtualBox Application

There are three commands available in the image above. Those commands are in the following sequence :

sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --list-port

In order to read the detail of the above command sequence, just read the article with the title of ‘How to Add Rule in the Firewall to Allow Incoming MySQL Connection in CentOS 8’ in this link.

 

MySQL Database Server Execution, Activation and Testing Process

In this part, after successfully installing MySQL Database Server, add MySQL port forwarding in the virtual server setting and also MySQL port filter in the virtual server, just start and enable the service. After that, test it by accessing the service. The access is from the host machine or the server which is running in the VirtualBox application. The access itself will be redirected to the MySQL Database Server’s service running in a guest machine in the VirtualBox application. The following are the steps to be able to do it :

1. First of all, execute the command the Windows Command Prompt as follows :

 
Microsoft Windows [Version 10.0.18362.1082]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\Personal>cd \

C:\>

2. From the Windows Command Prompt, using either SSH remote connection or direct access, try logging in to the virtual server. For a reference on accessing the virtual server using SSH remote connection, just read the article with the title of ‘How to Add SSH Port Forwarding Rule to remote Guest Machine running in VirtualBox Manager from Host Machine’ in this link. The following is the command execution for accessing virtual server remotely using port forwarding :

C:\>ssh -p 9922 admin@localhost
admin@localhost's password:
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Mon Jan  4 04:11:12 2021 from 10.0.2.2
[admin@localhost ~]$ systemctl start mysqld
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to start 'mysqld.service'.
Authenticating as: Administrator (admin)
Password:
==== AUTHENTICATION COMPLETE ====
[admin@localhost ~]$ systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-01-07 02:55:59 EST; 6s ago
  Process: 1880 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 1753 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 1729 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 1835 (mysqld)
   Status: "Server is operational"
    Tasks: 39 (limit: 11418)
   Memory: 490.5M
   CGroup: /system.slice/mysqld.service
           └─1835 /usr/libexec/mysqld --basedir=/usr

Jan 07 02:55:37 localhost.localdomain systemd[1]: Starting MySQL 8.0 database server...
Jan 07 02:55:38 localhost.localdomain mysql-prepare-db-dir[1753]: Initializing MySQL database
Jan 07 02:55:59 localhost.localdomain systemd[1]: Started MySQL 8.0 database server.
[admin@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: NO)
[admin@localhost ~]$

3. Apparently, there is a missing step after finishing MySQL Database server installation. That step is to execute the command for securing MySQL Database Server installation as follows :

[admin@localhost ~]$ sudo mysql_secure_installation
[sudo] password for admin:

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 8

Invalid option provided.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[admin@localhost ~]$

4. So, the above process is for securing MySQL Database Server. After finishing on the above step,  try to access MySQL Database Server as follows :

[admin@localhost ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create user 'admin'@'%' identified by 'password';
Query OK, 0 rows affected (0.14 sec)

mysql> create database db_guest_machine;
Query OK, 1 row affected (0.37 sec)

mysql> grant all on db_guest_machine.* to 'admin'@'%';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.26 sec)

mysql>

5. The above process is showing an activity for accessing MySQL Database Server, creating a new database, a new user account and also the access privilege. So, in order to test it, execute from the host machine to access the MySQL database server using the new user account as follows :

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uadmin -p -P9933 -hlocalhost
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_guest_machine   |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql>

As it exist in the above output command execution, accessing MySQL database server running in virtual server from the host server is a success.  The above command execution for accessing MySQL database server is using port 9933 in the host machine. The access will be forwarded to port 3306 of MySQL database server running in the virtual server.

Leave a Reply