How to Install and Secure MySQL on Ubuntu 16.04 VPS Server

MySQL is one of the most popular database systems that utilize Structured Query Language (SQL). The relational database is commonly used alongside Apache/LightSpeed/Ngnix web servers and PHP/Perl/Python scripting languages.

Due to its stability, MySQL is used by many developers in production. Popular uses include WordPress, Joomla and Drupal content management systems. MySql is also used in major websites including Facebook, Twitter, YouTube and Google.

The database management system is very secure, scalable and performs well especially in an environment that demands the use of transactions. MySql’s complete workflow and reduced total cost of ownership gives developers the flexibility of using it.

MySQL works pretty well in an Ubuntu 16.04 Virtual Private Server and here is how to install it.

Prerequisites

  • An Ubuntu 16.04 VPS server from Digital OceanVultr or Linode.
  • A command line tool like putty.
  • A non-root username with sudo rights.

Step 1: Downloading MySQL from the Ubuntu Package Repository

MySQL is maintained in the Ubuntu central repository and we can pull it using the apt-get command. But before we do this, we need to make sure that our Ubuntu 16.04 server is up to date.

Run the command below to update your Ubuntu server

sudo apt-get update

Then install MySQL server by running this command.

sudo apt-get install mysql-server

Press Y then Enter when you are promoted to install the package.

Step 2: Setting the MySQL Server root Password

MySQL server has a default user called root and should not be confused with the root of your Linux system. Once you complete the MySQL installation, you will be requested to supply a password for the root user.

Kindly use a strong password for security purposes. A mix of upper and lower case characters combined with numbers and special characters form a hard-to-crack password. Press Enter after entering the password and confirm the same on the next screen

Step 3: Securing the MySQL Server

The default MySql installation is not secure because it contains anonymous users and a test database. So we need to harden the installation to avoid somebody from login on the system with the default settings.

Luckily, we can run a command that will make things easier for us. Just type the following on your command line tool:

sudo mysql_secure_installation

The command will run and you will be prompted to enter the MySQL root password that you created in Step 2 above.

Password Validation Plug-in

MySql secure installation will prompt you to setup a Validate Password Plug-in which is used for testing MySQL passwords to improve security.

The command will read as follows

Would you like to setup VALIDATE PASSWORD plugin?

Press Y and Enter to enable the Plug-in

The Validation Plug-in has three levels of password validation policy

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

You will get a prompt to choose your desired policy which reads as follows

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

Just enter 2 and press Enter. This will make sure that passwords created for any MySQL database user will contain a mix of numeric, mixed case and special characters.

Changing Password for the root User

Next, you will get a prompt to change the password for the MySQL root user.

The prompt will read as follows:

Change the password for root ? ((Press y|Y for Yes, any other key for No) :

Since we chose a strong password while installing MySQL in the above steps, we are not going to change the password. So just press N and Enter to proceed.

Removing Anonymous Users

Leaving any anonymous users on your MySQL database is very dangerous. You will get a prompt to remove the user.

The prompt will read as the below text:

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter to remove the user

Disallowing Remote root Login

MySQL remote root login leaves an open door for hackers who can recursively connect from any IP address to your MySQL server and probably brute-force your password.

To disable this, you will get a prompt which reads:

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

Press Y and then Enter to disable root login remotely

Deleting the MySQL default test Database

Fresh MySQL installation comes with a test database named ‘test’ that can be used by anyone. This is intended for testing and should be removed in a production environment.

You will get a prompt to remove the database which reads as follows:

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter to remove the database

Reloading MySQL Server Privileges

The changes we have made so far have not yet been committed to the system and MySQL privileges need to be reloaded.

The prompt for refreshing the settings will read:

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter for the changes to be effected. Finally, you will see a success message.

Success.

All done!

Step 4: Disabling Strict SQL Mode in MySQL 5.7

The default MySQL strict mode can bring problems when you try to insert data in a table. If a field is null and there is no default value defined, the transaction will fail.

Let’s create a new MySQL configuration file and make the changes using a nano text editor.

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

Once the text editor opens, enter the following text

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Press CTRL + X then Y and Enter to save the changes

You need to restart the MySQL server for the changes to be effected by entering the command below on your terminal.

sudo service mysql restart

Step 5: Checking the Status of MySQL server

The MySQL server installation on our Ubuntu 16.04 VPS server is complete but we can test whether everything is working by running the below command.

sudo systemctl status mysql.service

If there were no hiccups with the MySQL installation, you should see the following status text:

Conclusion

That is all it takes to Install MySQL server on your Ubuntu VPS. From this point, you can create MySQL databases, tables and users using the command line. However, if you want a better tool to manage your MySQL database from a web browser, you should consider installing phpMyadmin. See my guide for installing phpMyadmin on Ubuntu server 16.04.

Ads by Google

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.