How To Install/Secure MySQL On Ubuntu 20.04

In this tutorial, we will discuss how to install and secure MySQL on Ubuntu 20.04.

MySQL is a widely known open-source database management system, that is based on SQL (Structured Query Language). Since this platform offer a great speed and efficiency to users, it’s chosen as the best choice for most of the purposes in logging, running an e-commerce applications, websites, etc. So, to install MySQL on your Ubuntu 20.04 server, you might be looking for a step by step guide, and this tutorial will easily help you.

Prerequisites

• Ubuntu 20.04 server
• Superuser (sudo) enabled user

Steps to install MySQL on Ubuntu

Since the latest version of MySQL available for Ubuntu is MySQL version 8.0, we need to install it with following steps.

Step 1: Install MySQL on Ubuntu

It’s always a good practice to update Apt package manager in Ubuntu server before installing any new package. So, even we suggest you to update the package manager before installing MySQL. Just run the command below.

sudo apt update

Once update is finished, you can install MySQL server with the command below.

sudo apt install mysql-server

The command will now install MySQL version 8.0. In order to verify the installation run the command below.

sudo systemctl status mysql

here’s the output you can expect to appear on screen.

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2020-04-28 20:59:52 UTC; 10min ago
Main PID: 8617 (mysqld)
Status: "Server is operational"
…

Step 2: Securing MySQL Configuration

Since you have successfully installed MySQL on Ubuntu 20.04, you should now secure the installation. Securing here means to make some changes in configuration to change less secure features like root login remotely, and so on. To do so, run the command below.

sudo mysql_secure_installation

After running the command, the MySQL will ask you to enter a new password to configure Validate Password Plugin. This is usually done to secure MySQL user’s passwords which improves the security feature. Here’s the output you will see on screen.

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

Just press “y” button on keyboard and press enter. In the nest step, you will see details about 3 levels of password validation policy. The three levels are low, medium, and strong. In order to answer this, you need to select 0,1,2 respectively. The output of above command will be shown as the details provided below.

There are 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

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

We suggest you to press 2 to assign a strong password for your MySQL root user. After the selection is done, you will be asked to enter your password to set it as new passcode.

Please set the password for root here.


New password:

Re-enter new password:

Enter your desired password

Feed your desired password for root user, and the validate password plugin will validate the strength of your password.

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

In order to confirm the password, press “y”. or press any other key as indicated to re-enter another password.

In the next step, it will be asked to remove anonymous user, restrict root user access to local machine, remote the test database and to reload privileges table one by one. Just answer “y” to every questions further.

Step 3: Login as “root” user

In order to internet with MySQL sever through command line a utility called MySQL client is used. This utility is installed as a dependency of MySQL server package.

Alike previous versions, MySQL version 8.0 also have auth_socket through which root user is authenticated as default. The auth_socket plugin authenticates root user or other users which attempt to connect on localhost machine through Unix socket file. This indicate that unless you change this setting, you can not authenticate root user by offering a password. In order to change the settings, login to MySQL server as root with below command.

sudo mysql

you will be shown with MySQL shell as shown below.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.19-0ubuntu5 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Mysql>

In case you intend to login to your MySQL server as root user through external application (like phpmyadmin), then you must have to do either of two things as discussed below.

As a first option, you need to alter the authentication method from auth_socket to mysql_native_password. To do this, run the command below.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strong_password';
mysql> FLUSH PRIVILEGES;

or, the second option is to create a new administrative user with access privilege to all databases. Just run the command below.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';

After all the settings are configured properly in MySQL shell, you can exit the application by entering the command below.

mysql> FLUSH PRIVILEGES;
mysql> exit

So, this way, we have successfully learned to install & secure MySQL on Ubuntu 20.04. And your database management system is ready for your personal use. Click here to learn how to create new database, user accounts, and to manage them. Hope you enjoyed reading this article.