Install and Secure MySQL on CentOS, Fedora and any Red Hat Linux

Introduction

MySQL is a free and open-sourced relational database management system.

It is a fast, scalable and secure database system, and it is an integral part of the LAMP stack that runs much of the internet.

This article will show how to install and secure a production-ready MySQL server on CentOS.

Prerequisites

Install MySQL on CentOS, Fedora and Other Red Hat Variants

On centOS, fedora and other redhat linux distributions, MySQL is installed using the rpm package repository. Similar to the ubuntu installation, the MySQL .rpm file needs to be downloaded and added to the server’s rpm local repository.

NOTE: The rpm files vary based on the Red Hat Enterprise Linux version and you can verify this by simply running this command in your terminal:

cat /etc/redhat-release

1. Add the MySQL Software Repository

The first step is to install GnuPG, which ensures the MySQL ‘.rpm’ package downloaded is intact and has not been tampered with. To do this, simply run:

sudo yum install gnupg 

Next, download the MySQL .rpm file using wget:

CentOS 9 / CentOS 8:

wget  https://repo.mysql.com/mysql80-community-release-el8-3.noarch.rpm

CentOS 7:

wget repo.mysql.com/mysql80-community-release-el..

CentOS 6:

wget https://repo.mysql.com/mysql80-community-release-el6-5.noarch.rpm

Fedora 35:

wget  https://dev.mysql.com/get/mysql80-community-release-fc35-2.noarch.rpm

Fedora 34:

wget  https://dev.mysql.com/get/mysql80-community-release-fc34-3.noarch.rpm

After downloading the package for your Linux platform, now install the downloaded package:

CentOS 9 / CentOS 8:

sudo yum install mysql80-community-release-el8-3.noarch.rpm

CentOS 7:

sudo yum install mysql80-community-release-el7-5.noarch.rpm 

CentOS 6:

sudo yum install mysql80-community-release-el6-5.noarch.rpm 

Fedora 35:

sudo dnf install mysql80-community-release-fc35-2.noarch.rpm

Fedora 34:

sudo dnf install mysql80-community-release-fc34-3.noarch.rpm

Step 2. Install MySQL

To install MySQL, run:

CentOS:

sudo yum install mysql-server

Fedora::

sudo dnf install mysql-server

After successful installation of MySQL, it’s time to start and enable the MySQL server with the following commands:

sudo service mysqld start

sudo systemctl enable mysqld.service

Verify the status of the MySQL server:

sudo systemctl status mysql

This is the sample output of running MySQL it shows:

Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
 Active: active (running) since Sat 2022-02-26 09:47:40 UTC; 18s ago
   Docs: man:mysqld(8)
         http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 60150 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 60224 (mysqld)
 Status: "Server is operational"
  Tasks: 38 (limit: 12354)
 Memory: 464.2M
    CPU: 4.606s
 CGroup: /system.slice/mysqld.service
         └─60224 /usr/sbin/mysqld

Feb 26 09:47:33 rpm systemd[1]: Starting MySQL Server...
Feb 26 09:47:40 rpm systemd[1]: Started MySQL Server.

Now finally verify the installed MySQL version using the following command.

mysql --version

mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

Configuring & Securing MySQL

The MySQL instance on your machine is insecure immediately after installation. For fresh installations, you’ll want to run the DBMS’s included security script. This script changes the less secure default options for things like remote root logins and sample users.

NOTE: On CentOS, MySQL version 8.0 or higher generates a temporary random password in /var/log/mysqld.log after installation.

Use the below command to see the password before running MySQL secure command.

grep 'temporary password' /var/log/mysqld.log

Once the password word is known, you might want to write it down as it would be needed during the configuration.

Use the below command to see the password before running MySQL secure command.

grep 'temporary password' /var/log/mysqld.log

Once the password word is known, write it down as it would be needed during the configuration.

1. Run the security script with sudo

sudo mysql_secure_installation

Follow the onscreen instructions carefully, for reference see the output of the above command below:

Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
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
There are three levels of password validation 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: 2
Please set the password for root here.
New password: 
Re-enter new password: 

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

NOTE : In setting root passwords, it is important to note that the VALIDATE PASSWORD component requires passwords to be strings of words containing one uppercase, one lowercase, one special character and one number.

2. Delete anonymous users

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

3. Disable root login over the internet

This is an excellent security feature, it ensures no one can guess the root password of the MySQL server from the public internet.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

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

4. Delete the default test database and reload privileges

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


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

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

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

All done!

Create a dedicated non-root user and grant privileges

The MySQL server creates a root user upon installation. This user has complete control over every database, table, user, and so on. Because of this, it is best to avoid using this account outside administrative functions. Below, outlines steps to create a non-root user and grant it privileges:

1. Log in to the MySQL shell

sudo mysql -u root -p

Enter the password created when configuring and securing the mysql installation.

2. Create a user

mysql> CREATE USER "username" @ "host"  IDENTIFIED WITH caching_sha2_password BY "password";

Where username represents the name of your username, host is represented by localhost since it is on your server you are performing the creation and the password is any password of your choice and caching_sha2_password is the default authentication plugin.

Remember the password has to conform to the VALIDATE PASSWORD COMPONENT requirements enabled previously.

NOTE: The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the previousmysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password.

3. Grant privileges to the newly created user

Run this GRANT statement to grant these privileges to the new user:

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO "username"@"localhost" WITH GRANT OPTION;

4 Flush Privileges

This frees up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

mysql> FLUSH PRIVILEGES;

Exit the shell

mysql > exit

5. Log in using the new user credentials

mysql -u charles -p 

Enter the password and hit enter

mysql> 

This output shows you have logged in successfully.

Next Steps

A production-ready MySQL has been installed on your server. However, It is best practise to create a database on the server to work with in your application.

Conclusion

For more information about the basics of MySQL, the following documentations can help extensively:

Connecting And Disconnecting From MySQL Server

Creating Common Queries