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
Deploy either an CentOS 9 cloud server at Vultr with at least 2 GB of RAM
Log in to your server as the non-root user.
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_passwordandsha256_passwordauthentication plugins provide more secure password encryption than the previousmysql_native_passwordplugin, andcaching_sha2_passwordprovides better performance thansha256_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: