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
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.
- 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 :
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.