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.

Update and Upgrade Rocky Linux 9

Before installing MySQL, it's important to make sure your system packages are up-to-date. This ensures a stable environment for MySQL and avoids potential compatibility issues. To do this, follow these steps to update and upgrade your Rocky Linux 9 server.

1. Update Package Repository.

Start by refreshing the package repository to ensure you have the latest list of available packages. This command downloads the latest package lists from the repositories, ensuring you have the most recent information about available packages:

sudo dnf update

2. Upgrade Installed Packages.

After updating the package repository, upgrade your installed packages to their latest versions. This ensures your system includes the latest features, bug fixes, and security updates, making your system more resilient to potential threats.

sudo dnf upgrade

It's recommended to run these updates to ensure your system is always up-to-date.

Install MySQL

MySQL is available in the default DNF repositories on Rocky Linux 9. Follow these steps to install the MySQL server package and verify the new system service details.

1. Install MySQL Server.

Install the MySQL server package using the following command:

sudo dnf install mysql-server

2. View the Installed MySQL Version.

Verify the installation by checking the installed MySQL version:

mysql --version

You should see an output similar to:

mysql  Ver 8.0.36 for Linux on x86_64 (Source distribution)

3. Enable MySQL to Start at Boot

Enable the MySQL system service to start at boot time:

sudo systemctl enable mysqld

4. Start MySQL Service

Start the MySQL service on your server:

sudo systemctl start mysqld

5. Check MySQL Service Status

Verify that the MySQL service is running:

sudo systemctl status mysqld

You should see an output similar to:

    ● 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

Secure the MySQL Server

Securing your MySQL installation is critical to protecting your data from unauthorized access. It's crucial to take steps to prevent unauthorized access to your database and ensure the integrity of your data. Vultr recommends running the MySQL security script to enhance security.

1. Run the MySQL Security Script

Execute the MySQL secure installation script to improve security:

sudo mysql_secure_installation

Follow the prompts to set a root password, remove anonymous users, disallow remote root login, and remove the test database:

• VALIDATE PASSWORD component: Press Y.

You'll be asked to enable the password validation component, which helps enforce strong passwords and improve security.

• Password Validation Policy: Enter 2 for STRONG.

This prompt asks you to select a password validation policy. Choose 0 for LOW, 1 for MEDIUM, or 2 for STRONG for higher security.

• New password: Enter the new password you want to set for the root user.

This password should be strong and meet the criteria mentioned in the previous prompts.

• Re-enter new password: Re-enter the same password to confirm it.

This helps ensure that you have entered the password accurately and prevents accidental typos.

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

The script will give you an estimate of the password strength. Press Y to continue if the strength is satisfactory.

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

It's highly recommended to remove anonymous users for security purposes.

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

Disallowing remote root login is a crucial security measure.

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

You'll be prompted to remove the test database and access to it. The test database is created by default for testing purposes and is not necessary for the normal operation of MySQL.

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

You'll be asked to reload the MySQL privilege tables to apply the changes you have made. It's important to answer Y for the changes to be saved.

Manage the MySQL System Service

1. Check the Status of MySQL:

sudo systemctl status mysqld

2. Restart MySQL Service:

sudo systemctl restart mysqld

3. Stop MySQL Service:

sudo systemctl stop mysqld

4. Start MySQL Service:

sudo systemctl start mysqld

Access MySQL

You can access the MySQL database server console using the mysql command. Follow these steps to access MySQL and create a test database.

1. Log in to MySQL

Log in to the MySQL shell as the root user:

sudo mysql -u root -p

Enter the root password you set earlier.

You should see an output similar to:

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.

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>

2. Create a New Database

Create a test database named example_vultr:

CREATE DATABASE example_vultr;

3. View All Databases

Verify that the new database has been created:

SHOW DATABASES;

You should see example_vultr listed in the output.

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

4. Create a New Database User

Create a new database user with a strong password. For example, vultr_user:

CREATE USER 'vultr_user'@'localhost' IDENTIFIED BY 'secure_password';

5. Grant Privileges to the User

Grant the new user full privileges to the example_vultr database:

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

6. Flush Privileges

Refresh the MySQL privilege tables to apply the changes:

FLUSH PRIVILEGES;

7. Verify User Privileges

Verify that the new user, vultr_user, has the necessary privileges to access the example_vultr database. Execute the following command in the MySQL shell:

SHOW GRANTS FOR 'vultr_user'@'localhost';

This command will display a list of privileges granted to the vultr_user account. You should see an output similar to this:

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

Conclusion

You have installed and configured MySQL on your Rocky Linux 9 server. You also secured the MySQL installation and created a test database. For more information and advanced configurations, refer to the official MySQL documentation. Integrate MySQL with your applications to use it as a robust backend database.