How to Install MySQL on Rocky Linux 9

Updated on July 2, 2024

Introduction

MySQL is an open-source relational database management system (RDBMS) that uses the structured query language (SQL) and known for its reliability, performance, and ease of use. MySQL supports small and large-scale web applications to store data in separate table structures. This guide explains how to install MySQL on a Rocky Linux 9 and secure access to the database server console.

Prerequisites

Before you begin:

Deploy a Rocky Linux 9 server instance on Vultr.
Access the server using SSH as a non-root user with sudo privileges.
Update the server.

Install MySQL

The latest MySQL database server package is available in the default dnf package manager repositories on Rocky Linux 9. You can install specific MySQL versions using the EPEL (Extra Packages for Enterprise Linux) repository on your server. In the following steps, install the latest MySQL version on your server.

Update the DNF package index on your server.
console
```
$ sudo dnf update
```
Install the latest MySQL database server package.
console
```
$ sudo dnf install mysql-server
```

View the installed MySQL version on your server.

console

$ mysql --version

Output:

mysql  Ver 8.0.36 for Linux on x86_64 (Source distribution)

Manage the MySQL System Service

MySQL uses the mysqld system service to run on your Rocky Linux 9 server. Follow the steps below to manage the MySQL system service to start, verify and restart the database server.

Enable the MySQL service to start at boot time.

                            console
                            
$ sudo systemctl enable mysqld

Output:

Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

Start the MySQL service.
console
```
$ sudo systemctl start mysqld
```

View the MySQL system service status and verify that it's running on your server.

                            console
                            
$ sudo systemctl status mysqld

Output:

● mysqld.service - MySQL 8.0 database server
    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
    Active: active (running) since Tue 2024-06-18 22:19:47 UTC; 11s ago
    Process: 6235 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
    Process: 6257 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Main PID: 6331 (mysqld)
    Status: "Server is operational"
    Tasks: 38 (limit: 48896)
    Memory: 469.6M
        CPU: 2.254s
    CGroup: /system.slice/mysqld.service
            └─6331 /usr/libexec/mysqld --basedir=/usr

Run the following command to stop the MySQL service.
console
```
$ sudo systemctl stop mysqld
```
Restart the MySQL service to reload the database server.
console
```
$ sudo systemctl restart mysqld
```

Secure the MySQL Server

The MySQL database server runs with insecure default settings such as test databases and low password policies on your server after installation. Secure the MySQL database server by setting up a new root user password, remove insecure defaults, and disable remote access to the root user as described in the following steps.

Run the following command to start the MySQL secure installation script.

console

$ sudo mysql_secure_installation

Enter Y and press Enter when prompted to enable the VALIDATE PASSWORD component to set up the minimum password requirements on your database server.

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:

Enter your desired password strength policy to apply on your server. For example, enter 2 to set a strong password 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:

Enter a new strong root user password when prompted for a New Password.
Repeat the password and press Enter to create the new root user passsword.

Please set the password for root here.

New password: 

Re-enter new password:

Enter Y when prompted to continue apply the root user password.

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :

Enter Y when prompted to remove anonymous users from your MySQL database server.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

Enter Y to disable remote login access for the root user.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

Enter Y to remove all test databases on your server.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Enter Y when prompted to reload privilege tables and apply your new MySQL database server configurations.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Restart the MySQL service to apply your configuration changes.
console
```
$ sudo systemctl restart mysqld
```

Access MySQL

The MySQL client utility command mysql allows you to access your database server console by specifying a user and password. Follow the steps below to access your MySQL database server as the root user and create new database users on your server.

                            console
                            
$ sudo mysql -u root -p

Enter the root password you set earlier when prompted. Then, verify that your prompt changes to the MySQL shell similar to the following output.

                            sql
                            
                        
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

mysql>

Create a sample database such as example_vultr.
sql
```
mysql> CREATE DATABASE example_vultr;
```

View all databases on your server to verify that the new database is available.

                            sql
                            
mysql> SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| example_vultr      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Create a new database user with a strong password based on your MySQL server password strength policy. For example, vultr_user.
sql
```
mysql> CREATE USER 'vultr_user'@'localhost' IDENTIFIED BY 'Se_cu_re@@pa_ss_word24';
```

Grant the new user full privileges to the sample database.

                            sql
                            
mysql> GRANT ALL PRIVILEGES ON example_vultr.* TO 'vultr_user'@'localhost';

Refresh the MySQL privilege tables to apply changes.
sql
```
mysql> FLUSH PRIVILEGES;
```

View all privileges available to the new MySQL user to verify access to the database.

                            sql
                            
mysql> SHOW GRANTS FOR 'vultr_user'@'localhost';

Output:

+-----------------------------------------------------------------------+
| Grants for vultr_user@localhost                                       |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `vultr_user`@`localhost`                        |
| GRANT ALL PRIVILEGES ON `example_vultr`.* TO `vultr_user`@`localhost` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

Based on the above output, the sample MySQL user vultr_user has full privileges to the sample database example_vultr.

Exit the MySQL shell.
sql
```
mysql> EXIT
```

Conclusion

You have installed and configured MySQL on your Rocky Linux 9 server and created sample databases for use with MySQL users. MySQL accepts connection requests on the localhost port 127.0.0.1 on your server, open the MySQL configuration file /etc/mysql/my.cnf to modify the listen address to enable remote MySQL connections to the database server. For more information, visit the MySQL documentation.

How to Install MySQL on Rocky Linux 9

Introduction

Prerequisites

Install MySQL

Manage the MySQL System Service

Secure the MySQL Server

Access MySQL

Conclusion

Products

Features

Marketplace

Resources

Company