How to Install MySQL on Rocky Linux 9

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.

1. Update the DNF package index on your server.

   CONSOLE

   Copy

   $ sudo dnf update

   Explain Code

   Powered by Vultr AgentBeta

2. Install the latest MySQL database server package.

   CONSOLE

   Copy

   $ sudo dnf install mysql-server

   Explain Code

   Powered by Vultr AgentBeta

3. View the installed MySQL version on your server.

   CONSOLE

   Copy

   $ mysql --version

   Explain Code

   Powered by Vultr AgentBeta

   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.

1. Enable the MySQL service to start at boot time.

   CONSOLE

   Copy

   $ sudo systemctl enable mysqld

   Explain Code

   Powered by Vultr AgentBeta

   Output:

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

2. Start the MySQL service.

   CONSOLE

   Copy

   $ sudo systemctl start mysqld

   Explain Code

   Powered by Vultr AgentBeta

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

   CONSOLE

   Copy

   $ sudo systemctl status mysqld

   Explain Code

   Powered by Vultr AgentBeta

   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

4. Run the following command to stop the MySQL service.

   CONSOLE

   Copy

   $ sudo systemctl stop mysqld

   Explain Code

   Powered by Vultr AgentBeta

5. Restart the MySQL service to reload the database server.

   CONSOLE

   Copy

   $ sudo systemctl restart mysqld

   Explain Code

   Powered by Vultr AgentBeta

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.

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

   CONSOLE

   Copy

   $ sudo mysql_secure_installation

   Explain Code

   Powered by Vultr AgentBeta
   • 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) :

2. Restart the MySQL service to apply your configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart mysqld

   Explain Code

   Powered by Vultr AgentBeta

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.

1. Log in to the MySQL console as the root user.

   CONSOLE

   Copy

   $ sudo mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   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>

   Explain Code

   Powered by Vultr AgentBeta

2. Create a sample database such as example_vultr.

   SQL

   Copy

   mysql> CREATE DATABASE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

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

   SQL

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

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

4. Create a new database user with a strong password based on your MySQL server password strength policy. For example, vultr_user.

   SQL

   Copy

   mysql> CREATE USER 'vultr_user'@'localhost' IDENTIFIED BY 'Se_cu_re@@pa_ss_word24';

   Explain Code

   Powered by Vultr AgentBeta

5. Grant the new user full privileges to the sample database.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

6. Refresh the MySQL privilege tables to apply changes.

   SQL

   Copy

   mysql> FLUSH PRIVILEGES;

   Explain Code

   Powered by Vultr AgentBeta

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

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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.

8. Exit the MySQL shell.

   SQL

   Copy

   mysql> EXIT

   Explain Code

   Powered by Vultr AgentBeta

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.