How to Secure your MySQL on VPS or Dedicated Server

Running a WordPress blog on your own VPS or a dedicated server is not as straightforward as running it on a shared account from a hosting provider. There are several components that need to be installed and configured, primarily a web server (Apache, Nginx, or Lighttpd) and a database server (MySQL). MySQL, the most popular database for WordPress, is easy to install but can be challenging to configure.

This guide will walk you through the process of configuring and securing your MySQL on a VPS or a dedicated server.

MySQL is essentially the brain of your website or blog, storing all the configuration information, posts, comments, login information, user information, and more. This tutorial assumes that you have already installed the MySQL server on your VPS or dedicated server and are ready to configure and secure it.

Step 1: Run the Pre-install MySQL Script

The first step in securing your MySQL server is to run the pre-install MySQL script, mysql_secure_installation. This script will perform several security measures:

Set the root password to ensure that nobody can log into the MySQL root user without the proper authorization.
Remove anonymous users.
Remove the test database and access to it.
Disallow root login remotely. This is optional and can be done later.
To run the script, use the following command:

/usr/bin/mysql_secure_installation

This script is recommended for all MySQL servers in production use. It will 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 the process of setting the root password, removing anonymous users, disallowing root login remotely, and removing the test database and access to it. It will also reload the privilege tables to ensure that all changes made so far will take effect immediately.

Example:

[root@mysql-server ~]# /usr/bin/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] 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: List of MySQL Users

After running the pre-install script, you should check the list of MySQL users to make sure all users have a password. You can do this with the following command:

SELECT User,Host,Password FROM mysql.user;

This command will display a list of all MySQL users, their hosts, and their passwords.

See also  How to Restart MySQL in Ubuntu

Example:

mysql> SELECT User,Host,Password FROM mysql.user;
+---------------+-------------+-------------------------------------------+
| User          | Host        | Password                                  |
+---------------+-------------+-------------------------------------------+
| root          | localhost   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | mysql       | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | 127.0.0.1   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| wordpressuser | 192.168.0.5 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+---------------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)

Step 3: Set a Strong Password for the MySQL Root Account and Existing User Account

Next, you should set a strong password for the MySQL root account and any existing user accounts. To do this, first check the existing user accounts with the following command:

select Host,User,Password from user;

This command will display a list of all user accounts, their hosts, and their passwords.

mysql> select Host,User,Password from user;
+-------------+---------------+-------------------------------------------+
| Host        | User          | Password                                  |
+-------------+---------------+-------------------------------------------+
| localhost   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| mysql       | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 192.168.0.5 | wordpressuser | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

Then, set a new strong password for each account. For example, to set a new password for the root account on localhost, use the following command:

set password for 'root'@'localhost'=password('newstrongpassword');

Repeat this process for each user account, replacing ‘root’@’localhost’ with the user and host for each account, and ‘newstrongpassword’ with the new password you want to set.

mysql> set password for 'root'@'localhost'=password('newstrongpassword');
mysql> set password for 'root'@'127.0.0.1'=password('newstrongpassword');
mysql> set password for 'wordpressuser'@'192.168.0.5'=password('newstrongpassword');

Step 4: Enable Logging

It’s important to enable logging in MySQL to help administrators monitor critical events and assist in troubleshooting. You should enable general_log, slow_query_log, and log-error in MySQL.

See also  How to Install MySQL Database Server on Fedora 16

To do this, open the MySQL configuration file with the following command:

vim /etc/my.cnf

Then, add the following lines to the [mysqld] and [mysqld_safe] sections:

[mysqld]
...
general_log_file=/var/log/mysql/mysqld.log
general_log=1
slow_query_log_file=/var/log/mysql/mysqld.slow.log
slow_query_log=1

[mysqld_safe]
log-error=/var/log/mysql/mysqld.error.log
...

Next, create a folder for the MySQL logs and change the folder owner to MySQL:

chown -R mysql:mysql /var/log/mysql

You can verify the logs with the following command:

ll /var/log/mysql

This will display a list of all the MySQL logs.

Step 5: Restart the MySQL Service

After making these changes, you need to restart the MySQL service for the changes to take effect. You can do this with the following command:

service mysqld restart

This will stop and then start the MySQL service.

[root@mysql-server ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Commands Mentioned

  • /usr/bin/mysql_secure_installation – This command runs the MySQL secure installation script, which performs several security measures such as setting the root password, removing anonymous users, and disallowing root login remotely.
  • SELECT User,Host,Password FROM mysql.user; – This command lists all MySQL users, their hosts, and their passwords.
  • set password for ‘user’@’host’=password(‘newpassword’); – This command sets a new password for a specific MySQL user account.
  • vim /etc/my.cnf – This command opens the MySQL configuration file for editing.
  • chown -R mysql:mysql /var/log/mysql – This command changes the owner of the MySQL log directory to the MySQL user.
  • ll /var/log/mysql – This command lists all the files in the MySQL log directory.
  • service mysqld restart – This command restarts the MySQL service, allowing any changes made to the configuration to take effect.

Conclusion

You can further fine-tune your database based on your expected security level. You can also implement host iptables, physical firewall protection, and operating system hardening to protect the MySQL server.

See also  How to Install and Configure Lighttpd on CentOS 6.2

Remember, securing your MySQL server is a crucial step in protecting your website or blog. By following these steps, you can help ensure that your MySQL server is secure and ready for production use.

For more information about web servers, you can visit our pages on Apache, Nginx, and LiteSpeed. If you’re interested in learning more about different types of hosting, check out our articles on dedicated server, VPS server, cloud hosting, and shared hosting.

FAQ

  1. What is the purpose of the mysql_secure_installation script?

    The mysql_secure_installation script is a security-oriented script that performs several operations to secure a MySQL installation. It sets a password for the root accounts, removes root accounts that are accessible from outside the localhost, removes anonymous-user accounts, and removes the test database (which by default can be accessed by anonymous users).

  2. Why is it important to set a strong password for MySQL user accounts?

    Setting a strong password for MySQL user accounts is crucial for the security of your database. A weak password can be easily guessed or cracked, allowing unauthorized users to access and manipulate your data. A strong password helps protect your database from such security breaches.

  3. What is the role of logging in MySQL?

    Logging in MySQL serves several purposes. It helps administrators monitor critical events, troubleshoot issues, and analyze the performance of the database. Logs can provide valuable information about what the server is doing, help identify errors or problematic queries, and provide insights into how to optimize the database.

  4. How can I verify the MySQL logs?

    You can verify the MySQL logs by accessing the directory where the logs are stored, typically /var/log/mysql. Using the command ‘ll /var/log/mysql’, you can list all the MySQL logs. These logs can then be opened and reviewed using a text editor or log viewer.

  5. Why do I need to restart the MySQL service after making changes?

    Restarting the MySQL service is necessary after making changes to the configuration because it allows the service to reload with the new settings. Without a restart, the MySQL service would continue to operate with the old configuration, and the changes would not take effect.

Comments

1 Comment

  • Avatar Rakesh Khuntia says:

    Thanks for your insight. I think the much secure your Mysql is on hosted in Dedicated Server.

Leave a Reply

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