How to install MySQL on Ubuntu 20.04

Preamble

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 article explains how to install and protect MySQL in Ubuntu 20.04.

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

Install MySQL in Ubuntu

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

sudo apt update
sudo apt install mysql-server

After installation is complete, the MySQL service will start automatically. To make sure that the MySQL server is running, type:

sudo systemctl status mysql

The output should show that the service is on 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

The MySQL installation comes with a script named mysql_secure_installation, which makes it easy to improve the security of the database server.

Call the script without arguments:

sudo mysql_secure_installation

You will be asked to configure the VALIDATE PASSWORD PLUGIN, which is used to check the reliability of MySQL users’ passwords 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 the password
and allows the users to set only those passwords which are
secure enough. Would you like to setup the VALIDATE PASSWORD component?

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

There are three levels of password checking policy: low, medium, and strong. Click, Wesley you want to configure a password verification plugin or any other key to proceed 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

The next invitation will ask you to set a password for the user root MySQL:

Please set the password for root here.

New password:

Re-enter new password:

If you installed the password verification plugin, the script will show you the strength of your new password. Enter the password validation plugin:

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 prompted to delete an anonymous user, restrict root access to the local computer, delete the test database, and reboot the privilege tables. You shall be asked to answer all questions.

Log in as root

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

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

The auth_socket plugin authenticates users who connect from localhost via Unix socket file. This means that you cannot authenticate as root by providing a password.

To login to the MySQL server as root, 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.
affiliates. Other 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 into your MySQL server as root using an external program such as phpMyAdmin, you have two options.

The first is to change the authentication method from auth_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 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 showed you how to install MySQL on Ubuntu 20.04. Now that the database server is up and running, the next step is to learn how to manage MySQL user accounts and databases.

MYSQL on Ubuntu

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.