How to Secure MySQL Database Server on CentOS

MySQL is a popular open-source relational database management system, widely used for web applications, data storage, and content management systems. As the backbone for storing critical data, it is crucial to ensure that your MySQL database server is well-secured to protect sensitive information from unauthorized access, data breaches, and other security threats. Implementing proper security measures can significantly reduce the risk of attacks, safeguarding your valuable data.

A Default MySQL installation is completely vulnerable.

In this guide, we will walk you through a step-by-step process to help you secure your MySQL database server.

Step 1: Run mysql_secure_installation

MySQL comes with a built-in security script called mysql_secure_installation. This script helps you set a strong root password, remove anonymous users, disable remote root login, and remove the test database. To run the script, execute the following command:

sudo mysql_secure_installation

Follow the on-screen prompts to complete the process.

[root@CentOS57 ~]# mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


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? [Y/n] y
 ... Success!

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? [Y/n] n
 ... skipping.

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? [Y/n] n
 ... skipping.

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

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Step 2: Create a dedicated user with limited privileges

Rather than using the root user for everyday tasks, create a dedicated user with the necessary privileges. Replace newuser and newpassword with the desired username and password:

mysql -u root -p

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Step 3: Change the default MySQL port

By default, MySQL listens on port 3306. To make it more difficult for attackers to target your MySQL server, change the default port to a non-standard one. Open the MySQL configuration file in a text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Locate the line containing port = 3306 and change the port number to a non-standard one, for example, port = 4567. Save and close the file.

See also  How to Switching or Changing Between Different Runlevels on Linux

Restart the MySQL service to apply the changes:

sudo systemctl restart mysql

Step 4: Enable a firewall

Use a firewall like UFW (Uncomplicated Firewall) to limit incoming connections to your MySQL server. First, enable the UFW firewall:

sudo ufw enable

Allow incoming connections only from specific IP addresses:

sudo ufw allow from your_ip_address to any port 4567

Replace your_ip_address with the IP address you want to allow and 4567 with the MySQL port you set in Step 3.

Step 5: Regularly update your MySQL installation

Keep your MySQL installation up-to-date with security patches by regularly updating your system:

sudo apt update
sudo apt upgrade

Programs Mentioned:

  • MySQL – An open-source relational database management system used for managing databases and organizing data.
  • mysql_secure_installation – A built-in security script provided by MySQL to help secure the initial installation of a MySQL server.
  • UFW (Uncomplicated Firewall) – A user-friendly frontend for managing iptables firewall rules, making it easy to secure your server.
See also  How to Fix "Warning: RPMDB altered outside of yum" Error on Linux Fedora 16 Server

Conclusion

By following this guide, you have taken important steps to secure your MySQL database server. While these steps help improve security, it is essential to stay informed about new vulnerabilities and apply security best practices to maintain the highest level of protection for your data.

If you have any questions, comments, or suggestions for improvements, please feel free to share your thoughts.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *