How to install MySQL on Ubuntu 20.04

MySQL is the most popular open source relational database management system. It's fast, easy to use, easy to expand, and part of the popular LAMP and LEMP.

This guide explains how to install and protect MySQL on Ubuntu 20.04.

prerequisite

Make sure you are logged in as sudo user

Install MySQL on Ubuntu

At the time of writing, the latest MySQL version number in the Ubuntu source repository is MySQL 8.0. To install it, run the following command:

sudo apt update
sudo apt install mysql-server

Once the installation is complete, the MySQL service will start automatically. To verify that the MySQL server is running, enter:

sudo systemctl status mysql

The output should show that the service is enabled and running:

● 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"
     ...

Protect and reinforce MySQL

The MySQL installation file comes with a file named mysql_secure_installation script, which allows you to easily improve the security of the database server.

Run this script without parameters:

sudo mysql_secure_installation

You will be asked to configure VALIDATE PASSWORD PLUGIN, which is used to test the strength of MySQL user password and improve security:

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

There are three levels of password authentication strategy, low level, medium level and high level. If you want to set up the authentication password plug-in, press y or any other key to move to the next step:

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

Next time prompted, you will be asked to set a password for the MySQL root user:

Please set the password for root here.


New password: 

Re-enter new password: 

If you set up the password verification plug-in, this script will show you the strength of your new password. Enter y to confirm the 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

Next, you will be asked to remove any anonymous users, restrict root access to the local machine, remove the test database, and reload the permission table. You should answer y to all the questions.

Log in as root

Use MySQL client tool to interact with MySQL server on the command line. The MySQL client has been installed as a dependent package of MySQL server installation package.

On MySQL 8.0, the root user defaults to auth_socket plug-in authorization.

auth_ The socket plug-in verifies all users connected to localhost through the Unix socket file. This means that you cannot verify as root by providing a password.

Log in to MySQL server as root user and enter;

sudo mysql

You will be shown the MySQL shell as follows:

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>

If you want to log in to the MySQL server as root and use other programs, such as phpMyAdmin, you have two options.

The first is to change the verification method from auth_ Modify socket to mysql_native_password . You can do this by running the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
FLUSH PRIVILEGES;

The second option, the recommended option, is to create a new independent management user with access to all databases:

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