MySQL, the world’s most popular open source database and the second most widely used open-source relational database management system (RDBMS), is a crucial component of many web applications. However, its default installation is not securely configured. For the sake of security, it’s essential to manually run the mysql_secure_installation wizard to perform basic MySQL hardening.
This guide will walk you through the steps to secure your MySQL server, specifically focusing on MySQL Community Server 5.5.39 running on CentOS 6.5 and CentOS 6.6.
Before we dive into the tutorial, it’s worth noting that securing your MySQL server is a critical step in setting up a secure web environment. Whether you’re using a dedicated, a VPS, or cloud hosting, ensuring the security of your MySQL server is paramount.
Step 1: Run mysql_secure_installation Wizard
The first step in securing your MySQL server is to run the mysql_secure_installation wizard. This script is recommended for all MySQL servers in production use. It will guide you through several steps to secure your MySQL installation.
[root@vps ]# mysql_secure_installation
The script will first ask for the current password for the root user. If you’ve just installed MySQL and haven’t set the root password yet, the password will be blank, so you should just press enter.
The script will then guide you through several steps, including setting the root password, removing anonymous users, disallowing root login remotely, removing the test database and access to it, and reloading the privilege tables.
[root@vps ]# 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] y ... Success! 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] 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? [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: Set “bind-address” Parameter
The next step is to set the “bind-address” parameter within the “[mysqld]” section in /etc/my.cnf. Configure this to your VPS local loopback network device, which is “127.0.0.1”. Please make sure that you only perform this step if you confirm no other server will need to access the database on your VPS.
[root@vps ~]# vi /etc/my.cnf [mysqld] .. bind-address = 127.0.0.1 ..
Step 3: Restart Your MySQL Server
After setting the “bind-address” parameter, you need to restart your MySQL server for the changes to take effect.
[root@vps ~]# service mysqld restart
Step 4: Verify the MySQL Port
Finally, verify that the MySQL port is listening to 127.0.0.1 only.
[root@vps ~]# netstat -plunt | grep 3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 8224/mysqld
By following these steps, you can ensure that your MySQL server is secure. Whether you’re running a LiteSpeed, Apache, or Nginx server, securing your MySQL installation is a critical step in maintaining a secure web environment.
- mysql_secure_installation – This command runs the MySQL secure installation wizard, which guides you through several steps to secure your MySQL installation.
- vi /etc/my.cnf – This command opens the MySQL configuration file in a text editor, allowing you to set the “bind-address” parameter.
- service mysqld restart – This command restarts your MySQL server, allowing any changes you’ve made to take effect.
- netstat -plunt | grep 3306 – This command checks whether the MySQL port is listening to 127.0.0.1 only.
Securing your MySQL server is a crucial step in maintaining a secure web environment. By following the steps outlined in this guide, you can ensure that your MySQL installation is secure, regardless of whether you’re using a dedicated, a VPS, or cloud hosting.
Remember, securing your MySQL server is not a one-time task, but an ongoing responsibility. Regularly reviewing and updating your security settings, as well as staying informed about the latest security best practices and potential vulnerabilities, will help keep your MySQL server, and by extension your web applications, safe and secure.
Whether you’re a seasoned webmaster or a beginner, this guide provides a comprehensive and easy-to-follow approach to securing your MySQL server. By following these steps, you can contribute to the overall security of your web environment and ensure the integrity and confidentiality of your data.
What is the purpose of the mysql_secure_installation command?
The mysql_secure_installation command is a script that performs several security-related operations on your MySQL installation. It helps you secure your MySQL server by setting a password for the root accounts, removing root accounts that are accessible from outside the localhost, removing anonymous-user accounts, and removing the test database.
Why do I need to set the “bind-address” parameter?
Setting the “bind-address” parameter to “127.0.0.1” ensures that your MySQL server only accepts connections from clients running on the same machine, i.e., localhost. This is a security measure to prevent remote connections to your MySQL server.
Why is it necessary to restart the MySQL server after changing the configuration?
Restarting the MySQL server allows any changes you’ve made to the configuration file to take effect. If you don’t restart the server, it will continue to use the old configuration until the next restart.
What does the netstat command do?
The netstat command is used to display network connections, routing tables, interface statistics, and more. In this context, it’s used to verify that the MySQL port is listening to 127.0.0.1 only.
What are the security risks of not securing a MySQL server?
If a MySQL server is not secured, it can be vulnerable to a variety of attacks. These include unauthorized access to the database, data theft, data corruption, and even denial-of-service (DoS) attacks. By not securing your MySQL server, you risk exposing sensitive data and potentially disrupting your web services.