MySQL is the most popular open-source relational database management system. It is fast, easy to use, scalable, and an integral part of the popular LAMP and LEMP stacks.

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

Prerequisites

Make sure you are logged in as a user with sudo privileges.

Installing MySQL on Ubuntu

At the time of writing this article, the latest version of MySQL available in the Ubuntu repositories is MySQL version 8.0. To install it run the following commands:

sudo apt updatesudo apt install mysql-server

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

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

Securing MySQL

MySQL installation comes with a script named mysql_secure_installation that allows you to easily improve the database server security.

Invoke the script without arguments:

sudo mysql_secure_installation

You will be asked to configure the VALIDATE PASSWORD PLUGIN which is used to test the strength of the MySQL users’ passwords and improve the 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 validation policy, low, medium, and strong. Press y if you want to set up the validate password plugin 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

On the next prompt, 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 validate password plugin, the script will show you the strength of your new password. Type 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’ll be asked to remove the anonymous user, restrict root user access to the local machine, remove the test database, and reload privilege tables. You should answer y to all questions.

Login as root

To interact with the MySQL server from the command line use the MySQL client utility which is installed as a dependency of the MySQL server package.

On MySQL 8.0, the root user is authenticated by the auth_socket plugin by default.

The auth_socket plugin authenticates users that connect from the localhost through the Unix socket file. This means that you can’t authenticate as root by providing a password.

To log in to the MySQL server as the root user type:

sudo mysql

You will be presented with the 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>

If you want to login to your MySQL server as root using an external program such as phpMyAdmin, you have two options.

The first one is to change the authentication method from auth_socket to mysql_native_password. You can do that by running the following command:

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

The second, recommended option is to create a new dedicated administrative user with access to all databases:

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

Conclusion

We have shown you how to install MySQL on Ubuntu 20.04. Now that your database server is up and running, your next step could be to learn how to manage MySQL user accounts and databases.

If you have any questions or feedback, feel free to leave a comment.