{"id":4892,"date":"2014-04-07T18:28:02","date_gmt":"2014-04-07T10:28:02","guid":{"rendered":"https:\/\/webhostinggeeks.com\/howto\/?p=4892"},"modified":"2023-06-23T16:12:40","modified_gmt":"2023-06-23T16:12:40","slug":"how-to-secure-your-mysql-on-vps-or-dedicated-server","status":"publish","type":"post","link":"https:\/\/webhostinggeeks.com\/howto\/how-to-secure-your-mysql-on-vps-or-dedicated-server\/","title":{"rendered":"How to Secure your MySQL on VPS or Dedicated Server"},"content":{"rendered":"

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.<\/p>\n

This guide will walk you through the process of configuring and securing your MySQL on a VPS or a dedicated server.<\/p>\n

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.<\/p>\n

Step 1: Run the Pre-install MySQL Script<\/h2>\n

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:<\/p>\n

Set the root password to ensure that nobody can log into the MySQL root user without the proper authorization.
\nRemove anonymous users.
\nRemove the test database and access to it.
\nDisallow root login remotely. This is optional and can be done later.
\nTo run the script, use the following command:<\/p>\n

\r\n\/usr\/bin\/mysql_secure_installation\r\n<\/pre>\n
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.<\/p>\n
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.<\/p>\n
Example:<\/p>\n
\r\n[root@mysql-server ~]# \/usr\/bin\/mysql_secure_installation\r\n\r\n\r\n\r\n\r\nNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL\r\n      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!\r\n\r\n\r\nIn order to log into MySQL to secure it, we'll need the current\r\npassword for the root user.  If you've just installed MySQL, and\r\nyou haven't set the root password yet, the password will be blank,\r\nso you should just press enter here.\r\n\r\nEnter current password for root (enter for none):\r\nOK, successfully used password, moving on...\r\n\r\nSetting the root password ensures that nobody can log into the MySQL\r\nroot user without the proper authorisation.\r\n\r\nSet root password? [Y\/n] y\r\nNew password:\r\nRe-enter new password:\r\nPassword updated successfully!\r\nReloading privilege tables..\r\n ... Success!\r\n\r\n\r\nBy default, a MySQL installation has an anonymous user, allowing anyone\r\nto log into MySQL without having to have a user account created for\r\nthem.  This is intended only for testing, and to make the installation\r\ngo a bit smoother.  You should remove them before moving into a\r\nproduction environment.\r\n\r\nRemove anonymous users? [Y\/n] y\r\n ... Success!\r\n\r\nNormally, root should only be allowed to connect from 'localhost'.  This\r\nensures that someone cannot guess at the root password from the network.\r\n\r\nDisallow root login remotely? [Y\/n] n\r\n ... skipping.\r\n\r\nBy default, MySQL comes with a database named 'test' that anyone can\r\naccess.  This is also intended only for testing, and should be removed\r\nbefore moving into a production environment.\r\n\r\nRemove test database and access to it? [Y\/n] y\r\n - Dropping test database...\r\n ... Success!\r\n - Removing privileges on test database...\r\n ... Success!\r\n\r\nReloading the privilege tables will ensure that all changes made so far\r\nwill take effect immediately.\r\n\r\nReload privilege tables now? [Y\/n] y\r\n ... Success!\r\n\r\nCleaning up...\r\n\r\n\r\n\r\nAll done!  If you've completed all of the above steps, your MySQL\r\ninstallation should now be secure.\r\n\r\nThanks for using MySQL!\r\n<\/pre>\n
Step 2: List of MySQL Users<\/h2>\n
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:<\/p>\n
\r\nSELECT User,Host,Password FROM mysql.user;\r\n<\/pre>\n
This command will display a list of all MySQL users, their hosts, and their passwords.<\/p>\n
Example:<\/p>\n
\r\nmysql> SELECT User,Host,Password FROM mysql.user;\r\n+---------------+-------------+-------------------------------------------+\r\n| User          | Host        | Password                                  |\r\n+---------------+-------------+-------------------------------------------+\r\n| root          | localhost   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| root          | mysql       | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| root          | 127.0.0.1   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| wordpressuser | 192.168.0.5 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n+---------------+-------------+-------------------------------------------+\r\n4 rows in set (0.00 sec)\r\n<\/pre>\n
Step 3: Set a Strong Password for the MySQL Root Account and Existing User Account<\/h2>\n
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:<\/p>\n
\r\nselect Host,User,Password from user;\r\n<\/pre>\n
This command will display a list of all user accounts, their hosts, and their passwords.<\/p>\n
\r\nmysql> select Host,User,Password from user;\r\n+-------------+---------------+-------------------------------------------+\r\n| Host        | User          | Password                                  |\r\n+-------------+---------------+-------------------------------------------+\r\n| localhost   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| mysql       | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| 127.0.0.1   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n| 192.168.0.5 | wordpressuser | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |\r\n+-------------+---------------+-------------------------------------------+\r\n4 rows in set (0.00 sec)\r\n<\/pre>\n
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:<\/p>\n
\r\nset password for 'root'@'localhost'=password('newstrongpassword');\r\n<\/pre>\n
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.<\/p>\n
\r\nmysql> set password for 'root'@'localhost'=password('newstrongpassword');\r\nmysql> set password for 'root'@'127.0.0.1'=password('newstrongpassword');\r\nmysql> set password for 'wordpressuser'@'192.168.0.5'=password('newstrongpassword');\r\n<\/pre>\n
Step 4: Enable Logging<\/h2>\n
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.<\/p>\n
To do this, open the MySQL configuration file with the following command:<\/p>\n
\r\nvim \/etc\/my.cnf\r\n<\/pre>\n
Then, add the following lines to the [mysqld] and [mysqld_safe] sections:<\/p>\n
\r\n[mysqld]\r\n...\r\ngeneral_log_file=\/var\/log\/mysql\/mysqld.log\r\ngeneral_log=1\r\nslow_query_log_file=\/var\/log\/mysql\/mysqld.slow.log\r\nslow_query_log=1\r\n\r\n[mysqld_safe]\r\nlog-error=\/var\/log\/mysql\/mysqld.error.log\r\n...\r\n<\/pre>\n
Next, create a folder for the MySQL logs and change the folder owner to MySQL:<\/p>\n
\r\nchown -R mysql:mysql \/var\/log\/mysql\r\n<\/pre>\n
You can verify the logs with the following command:<\/p>\n
\r\nll \/var\/log\/mysql\r\n<\/pre>\n
This will display a list of all the MySQL logs.<\/p>\n
Step 5: Restart the MySQL Service<\/h2>\n
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:<\/p>\n
\r\nservice mysqld restart\r\n<\/pre>\n
This will stop and then start the MySQL service.<\/p>\n
\r\n[root@mysql-server ~]# service mysqld restart\r\nStopping mysqld:                                           [  OK  ]\r\nStarting mysqld:                                           [  OK  ]\r\n<\/pre>\n
Commands Mentioned<\/h2>\n