Installing MySQL Server on Rocky Linux and Enabling Remote Access

August 16, 2023

At some point, you're going to need to set up a database server on Rocky Linux. When you do, you might need to configure it so that it can be used as a database server for other machines. This is a great way to offload database services to other servers or to create a single database server to be used by multiple applications on different machines.

For example, you could host WordPress on one machine and point that platform to a database on a second machine. One benefit of this is that, should the WordPress machine go down, you won't lose your data.

Whatever the reason, you'll want to know how to install MySQL and then configure it for remote access. In this how-to, we'll show you how.

What you'll need

To get this set up, you'll need at least one machine running Rocky Linux and a user with sudo privileges. For testing purposes, you'll also need a second machine that has MySQL (or just mysql-client) installed so you can log into the remote server to ensure that it's working.

With those things at the ready, it's time to get to work.

Install MySQL

The first thing to do is install the MySQL server. To do that, log into your instance of Rocky Linux and open a terminal window. From the terminal, issue the following installation command:

sudo dnf install mysql-server -y

When the installation completes, you'll then need to start and enable the service with the command:

sudo systemctl enable --now mysqld

Open the firewall

For remote connections to be allowed through, you must open the proper port in the firewall, which can be done with the command:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
sudo firewall-cmd --reload

Secure MySQL

The next step is to secure MySQL, which is done with the command:

sudo mysql_secure_installation

The first thing you'll be presented with is the VALIDATE PASSWORD COMPONENT. If you want to be certain only strong passwords are allowed, type Y when you see the following:

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: Y

If you enable this, you'll then have to select a level of password validation policy, which are:

LOW - The length of the password must be greater than or equal to 8 characters.

MEDIUM - The length of the password must be greater than or equal to 8 characters and include a combination of numeric, mixed case, and special characters.

STRONG - The length of the password must be greater than or equal to 8 characters and include a combination of numeric, mixed case, special characters, and cannot be found in a dictionary file.

You'll then be asked to enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

If you opt to not enable the policy, type N. You will then be required to type and verify the new MySQL admin user password. Once you've done that, type Y for the remaining questions.

Configure MySQL

Next, we must configure MySQL to be accessible from beyond localhost. To do that, open the configuration file with:

sudo nano /etc/my.cnf.d/mysql-server.cnf

Add the following two lines at the bottom of that file:

bind-address = 0.0.0.0
default_authentication_plugin=mysql_native_password

Save and close the file. Restart MySQL with:

sudo systemctl restart mysqld

One thing to keep in mind is that the last line added makes the mysql_native_password module available to all users. If you're really concerned about security, don't add that line. If you opt out of making that option global, you'll have to enable the module on a per-user basis.

Speaking of users…

Create a MySQL user

You do not want to be using the MySQL admin user. Instead, you'll create users on a case-by-case basis. For example, if you're using MySQL for WordPress, you might create the user wpuser.

Let's stick with that example. We'll also create a database for WordPress and give wpuser access to it.

Log into the MySQL console with:

mysql -u root -p

Create the wpdb database with the command:

CREATE DATABASE wpdb;

Next, we're going to run two commands to create the wp-user (one for localhost and one for remote access) with the following commands:

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'wpuser'@'%' IDENTIFIED BY 'mypass';

Where mypass is a strong, unique password.

Remember when we mentioned the global authentication module earlier? If you don't want to make that global, you can do it on a case-by-case basis with the CREATE USER query like this:

CREATE USER 'wpuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'mypass';

Next, give wpuser full access to wpdb with:

GRANT ALL ON wpdb.* TO 'wpuser'@'localhost';
GRANT ALL ON wpdb.* TO 'wpuser'@'%';

Finally, flush the privileges and exit the MySQL console with:

FLUSH PRIVILEGES;
exit

Test the connection

Log into a different machine (on the same LAN) that has MySQL (or mysql-client) installed. To test the connection, issue the command:

mysql -u wpuser -h SERVER -p

Where SERVER is the IP address of your Rocky Linux host. You should be prompted to type the password for wpuser. On successful authentication, you'll find yourself at the MySQL console.

Congratulations! You now have a MySQL database server up and running on Rocky Linux, accepting connections from beyond localhost.