Install MySQL on Rocky Linux 9 Blue Onyx

In this guide we will see how to install MySQL 8.0 on Rocky Linux 9 "Blue Onyx" with dnf, Red Hat's default package manager

It is common in our Linux Support, being the basis for LAMP (Linux, Apache, MySQL, PHP), LEMP (Linux, NginX, MySQL, PHP) web stacks and several others, needed for webapps, CMS and e-Commerce

Installing MySQL in Rocky Linux 9

As a first step we update Rocky Linux packages with:

sudo dnf upgrade

We accept the package upgrade and import the new key if required

Rocky Linux 9 - AppStream 1.7 MB/s | 1.7 kB 00:00
Importing GPG key 0x350D275D:
Userid : "Rocky Enterprise Software Foundation - Release key 2022 "
Fingerprint : 21CB 256A E16F C54C 6E65 2949 702D 426D 350D 275D
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-Rocky-9
Is this ok [y/N]: Y

We proceed to install MySQL with dnf install:

sudo dnf install mysql-server

We accept the installation of the necessary packages

Total download size: 48 M
Installed size: 273 M
Is this ok [y/N]: Y

Check if the mysql process is active

sudo systemctl status mysqld

○ mysqld.service - MySQL 8.0 database server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disabled)
     Active: inactive (dead)

In case it is not active we can restart MySQL and repeat the previous check

sudo systemctl restart mysqld.service

sudo systemctl status mysqld

● mysqld.service - MySQL 8.0 database server
    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
    Active: active (running) since Sat 2024-08-10 09:44:42 UTC; 32s ago

Let's set up automatic startup for MySQL at boot

sudo systemctl enable mysqld

Let's check the default MySQL port, the 3306 TCP, which should be listening at this point

sudo dnf install net-tools

sudo netstat -tnplu |grep 3306

The command should return the following output, showing port 3306 listening locally

tcp6 0 0 :::3306 :::* LISTEN 86216/mysqld
tcp6 0 0 :::33060 :::* LISTEN 86216/mysqld 

Configure MySQL

The server is up, for a new installation the configurations used are the default ones, security is poor, at this point it is recommended to run the DBMS configuration script to set authentications and remove anonymous logins

sudo mysql_secure_installation

The script will guide us in configuring the server security options.

First we enable password control for users, and select strong passwords, with dictionary search of common passwords

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 passwords
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: 2

Enter password

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

Remove anonymous users, to increase security

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

Configure local user authentication root, which will be via socket, only on the local machine

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

Remove test databases, useless in production

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

We reload the privilege tables, so that our changes will take effect immediately.

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

All done!

Login to MySQL from console

The installation is finished and we can log in with the user root and previous password from our server console

sudo mysql -u root -p
Enter password:

As a test we can list the databases present

show databases;

+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

The output shows us the databases present, we have succeeded in installing MySQL in Rocky Linux 9, we can use with the usual syntax our MySQL, connect our web apps and CMS

Linux Professional Support

Trust one of our consultants. Expert Linux Support to your production deployments and 30m analysis Free. Contact us now!

MySQL's root password

To log in remotely you will need to set the code>root password of MySQL. We can use this command, but we recommend using a "strong" password, with numbers, upper and lower case, and special characters.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'LA_MIA_PASSWORD';

Accessing remote MySQL

To access a remote MySQL it is recommended to use an SSH tunnel by redirecting the remote 3306 port of our server, which only listens in localhost, to our local 33006 port

ssh root@IP-o-DNS -L 33006:localhost:3306

Thanks to the tunnel we could connect directly to our port 33006 in localhost or 127.0.0.1 with the root credentials created earlier

For that we can use one of these commands:

mysql -h localhost -P 33006 -u root -p

Logging in with MySQL Workbench

MySQL Workbench is a great tool for Database Admin (D.B.A.) MySQL and Operation, allows you to create DBs, manage server configuration, administer users, make backups, and more, and also incorporates the SSH tunneling capability already described above

1. We create a new connection
2. We select Standard TCP/IP over SSH
3. We enter the SSH configurations for our server of
   1. SSH Host name
   2. SSH User name
   3. Password or SSH key
4. The MySQL Database configurations
   1. Hostname
   2. Server Port
   3. Username
   4. Password
5. We test the connection