How to install MySQL in Ubuntu 20.04

Introduction:

MySQL is an open source database management system that is usually installed as part of LAMP (Linux, Apache, MySQL, PHP/Python/Perl). It implements a relational model and uses a structured query language to manage its data.

In this tutorial, how to install MySQL version 8.0 on Ubuntu 20.04 server will be explained. After completing this you will have a working relational database that you can use to build your next website or application.

In this tutorial, you will need:

An Ubuntu 20.04 server with a normal user (i.e. no root) and a firewall configured with UFW to set up this server.

Step 1 – Install MySQL

On Ubuntu 20.04, you can install MySQL using the APT package repository. In this article, the version of MySQL available in the default Ubuntu repository is version 8.0.27.

 

To install it, update the packages on your server:

sudo apt update

Then install the mysql-server package:

sudo apt install mysql-server

It is executed using the systemctl start command on the server.

sudo systemctl start mysql.service

This will launch the install and MySql commands, but you won’t be prompted to set a password or make any other configuration changes, and since the MySQL installation isn’t secure, we’ll cover that later.

 

Step 2 – Configure MySQL

For a new installation of MySql, you will want to enable and run the DBMS security script commands. These script commands change some of the default less secure options for things like logging in with remote root access, etc.

Warning: As of July 2022, an error occurred when running the mysql_secure_installation script without further configuration. This is because these script commands try to install and set a MySQL password for the root user, but by default in the Ubuntu installation, this account is not configured to connect using a password.

Run the security script commands with sudo:

sudo mysql_secure_installation

This script guides you through a series of commands where you can make changes to your MySQL security installation options. The first prompt asks if you want to set up the password validation plugin, which can be used to test the password strength of new MySQL users before considering them.

If you choose to enable the password validation plugin, any MySQL user who authenticates with a password must have a password that matches the policy you selected.

The strongest policy level, which you can select by entering the number 2, is that passwords must be at least eight characters long and contain a combination of uppercase, lowercase, numeric, and special characters:

Output

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 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:

 

When you select the additional password validation settings, the next command will be to set the password for the root user in MySQL. Enter and then confirm the security of your chosen password:

Output Please 

set the password for root here. 

New password: 

Re-enter new password:

Note that even if you have set a password for the root user in MySQL, this user is not currently configured to authenticate with a password when connecting to MySQL.

Step 3 – Create a dedicated MySQL user

After installation, MySQL creates a root account that you can use to manage your database. This user has full privileges on the MySQL server, meaning full control over every database, table, user, etc. For this reason, it is best to avoid using this account outside of administrative functions. In this step, how to use the root user in MySQL to create a new user account is explained. So you need to invoke mysql with sudo privileges to gain access to the MySQL root user:

sudo mysql

After fast access to MySQL, you can create a new user with the CREATE USER statement. These commands follow this general syntax:

mysql > CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

username = mysql username

Host = You can specify localhost on the Ubuntu server.

authentication_plugin = You can leave it completely so that the user authenticates with the default caching_sha2_password plugin.

For example: make sure to set sammy as your username and password to a strong password:

mysql > CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

After creating your new user, you can give them the appropriate privileges. The syntax of the general command to register user points is as follows:

mysql > GRANT PRIVILEGE ON database.table TO 'username'@'host';

The PRIVILEGE value in this example syntax specifies what actions the user is allowed to perform on the specified database and table. You can assign multiple points to a user in one command by separating each with a comma. You can also record total points to the user by entering an asterisk (*) instead of the database and table name.

for example:

mysql > GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

After this, it is a good practice to run the FLUSH PRIVILEGES command. This frees any memory the server has cached as a result of the previous CREATE USER and GRANT commands:

mysql > FLUSH PRIVILEGES;

Then you can exit mysql:

mysql > exit

In the future, to log in as a new MySQL user, you will use a command like the following:

mysql -u sammy -p

p = This command causes the MySQL client to prompt you for the MySQL user password for authentication.

 

Step 4 – Testing MySQL

MySQL should have started automatically. To test this, check its status.

systemctl status mysql.service

 

You will see output similar to the following:

Output

 mysql.service - MySQL Community Server 

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) 

Active: active (running) since Tue 2020-04-21 12:56:48 UTC; 6min ago 

Main PID: 10382 (mysqld) 

Status: "Server is operational"

 Tasks: 39 (limit: 1137) 

Memory: 370.0M 

CGroup: /system.slice/mysql.service 

                                  └─10382 /usr/sbin/mysqld

If mysql does not run, you can use the following command:

sudo systemctl start mysql

To investigate further, you can connect to the database using the mysqladmin tool, which is a client that allows you to run administrative commands.

for example:

sudo mysqladmin -p -u sammy version

You should see output similar to this:

Output 

mysqladmin Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((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. 

Server version 8.0.19-0ubuntu5 

Protocol version 10

Connection                  Localhost via UNIX socket

 UNIX socket                /var/run/mysqld/mysqld.sock 

Uptime: 10 min 44 sec 

Threads: 2 Questions: 25 Slow queries: 0 Opens: 149 Flush tables: 3 Open tables: 69 Queries per second avg: 0.038

 

Check out our blog for more tutorials & articles.