Introduction

MySQL is a widely used open-source relational database management system (RDBMS). It helps store, manage, and retrieve data for various applications. MySQL offers high performance, reliability, and ease of use, making it a popular choice for web applications, data warehousing, and logging. It is cross-platform, running on multiple operating systems like Windows, macOS, and Linux. MySQL integrates well with other technologies such as PHP, Python, and Java, providing a comprehensive solution for data management.

This guide shows you how to install MySQL on a Rocky Linux 9 server.

Prerequisites

Before you begin, ensure you've:

• A Rocky Linux 9. We recommend a Digital Ocean VPS server.
• A user account with sudo privileges.
• An internet connection.

Install MySQL

In this section, you will update your package list and install MySQL. Keeping your package list updated ensures you get the latest available packages.

1. Update your package list.

   CONSOLE

   $ sudo dnf update -y
   

   This command refreshes your package list to get the most recent version of MySQL.

2. Install MySQL.

   CONSOLE

   $ sudo dnf install mysql-server -y
   

   This command downloads and installs MySQL on your system.

Start and Manage MySQL Service

In this section, you will start the MySQL service and manage it using systemctl. This includes enabling MySQL to start at boot, stopping, and restarting MySQL.

1. Start MySQL service.

   CONSOLE

   $ sudo systemctl start mysqld
   

2. Enable MySQL to start at boot.

   CONSOLE

   $ sudo systemctl enable mysqld
   

3. Stop MySQL service.

   CONSOLE

   $ sudo systemctl stop mysqld
   

4. Restart MySQL service.

   CONSOLE

   $ sudo systemctl restart mysqld
   

Understand MySQL Package Directory Structure

In this section, you will learn about the MySQL package directory structure and the location of various configuration files.

1. MySQL configuration file.

   CONSOLE

   /etc/my.cnf
   

2. MySQL data directory.

   CONSOLE

   /var/lib/mysql/
   

3. MySQL log files.

   CONSOLE

   /var/log/mysqld.log
   

Create a Sample Database and Table (Optional)

In this section, you will create a sample database named company and a table named products with three fields: product_id, product_name, and retail_price. You will also insert records into the table and query the table to confirm the records.

1. Log in to the MySQL shell.

   CONSOLE

   $ sudo mysql -u root -p
   

   You will be prompted to enter the MySQL root password. Press Enter to continue without providing a password. By default, MySQL uses socket authentication to log you in using your Linux user account.

2. Create a sample database.

   MySQL

   mysql> CREATE DATABASE company;
   

   Output:

   MySQL

   Query OK, 1 row affected (0.00 sec)
   

3. Select the database.

   MySQL

   mysql> USE company;
   

   MySQL

   Database changed
   

4. Create a sample products table.

   MySQL

   mysql> CREATE TABLE products (
       product_id INT AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(255) NOT NULL,
       retail_price DECIMAL(10, 2) NOT NULL     
   );
   

   Output:

   Query OK, 0 rows affected (0.01 sec)
   

5. Insert records into the table.

   MySQL

   mysql> INSERT INTO products (product_name, retail_price) VALUES
   ('WIRELESS OUTDOOR CAMERA', 19.99),
   ('5G WIRLESS ROUTER', 29.99),
   ('20 INCH 4K SCREEN', 39.99);
   

   Output:

   Query OK, 3 rows affected (0.01 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   

6. Query the products table to confirm the records.

   SQL

   mysql> SELECT 
       product_id,
       product_name,
       retail_price 
   FROM products;
   

   You should see the records you inserted in the table.

   Output:

   MySQL

   +------------+-------------------------+--------------+
   | product_id | product_name            | retail_price |
   +------------+-------------------------+--------------+
   |          1 | WIRELESS OUTDOOR CAMERA |        19.99 |
   |          2 | 5G WIRLESS ROUTER       |        29.99 |
   |          3 | 20 INCH 4K SCREEN       |        39.99 |
   +------------+-------------------------+--------------+
   3 rows in set (0.00 sec)
   

   Your database is working as expected.

Conclusion

You have installed MySQL on your Rocky Linux 9 server. This guide covered updating your package list, installing MySQL, managing the MySQL service, creating a sample database and table, and understanding the MySQL package directory structure. MySQL is now ready to store and manage your data. You can start configuring your database, creating tables, and performing data operations. Consider setting up user accounts, securing your MySQL installation, and optimizing performance to enhance your database capabilities.