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.
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.
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.
