Install MySQL 8 on Ubuntu 20.04

This guide provides a step-wise tutorial on how to install MySQL 8 on Ubuntu 20.04. MySQL is a fast, stable and true multi-user, multi-threaded SQL database server with its main goals being speed, robustness and ease of use. To see a comprehensive description of the features offered by MySQL 8, navigate to MySQL 8 Reference Manual.

Installing MySQL 8 on Ubuntu 20.04

Ubuntu 20.04 ships with MySQL 8 on its default repositories.

apt show mysql-server

Package: mysql-server
Version: 8.0.19-0ubuntu5
Priority: optional
Section: database
Source: mysql-8.0
Origin: Ubuntu
Maintainer: Ubuntu Developers [email protected]
Original-Maintainer: Debian MySQL Maintainers [email protected]
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 110 kB
Depends: mysql-server-8.0
Homepage: http://dev.mysql.com/
Task: lamp-server
Download-Size: 9,544 B
APT-Sources: http://us.archive.ubuntu.com/ubuntu focal/main amd64 Packages

This makes the installation of MySQL 8 a seamless task.

Before you can proceed, update and upgrade your system packages.

apt update
apt upgrade

Next, install MySQL 8 by executing the command below;

apt install mysql-server

The command installs MySQL 8 and all required package dependency.

Secure MySQL 8 Installation on Ubuntu 20.04

MySQL ships with a security script called mysql_secure_installation that enables you to implement initial security of MySQL installation in the following ways:

• You can set a password for root accounts.
• You can remove root accounts that are accessible from outside the local host.
• You can remove anonymous-user accounts.
• You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

The script can be simply execute by running;

mysql_secure_installation

When run, the script prompts you on whether you want to implement password complexity checks. Accept the choose the strength of the 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: 2
...

Next, set the root password and accept other prompts to remove anonymous database users, disallow remote root login, remove test databases and reload privileges tables to effect the changes on MySQL.

Checking the Version of MySQL Installed

You can verify the version of MySQL installed by executing;

mysql -V

mysql Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))

Logging in to MySQL 8

You can now connect to MySQL 8 as a root user with the password you just set above.

mysql -u root -p

Another thing to note is that by default, MySQL 8 uses unix socket authentication plugin by default. This enables you to login to your MySQL server from the localhost as a root user without a password.

Therefore, running either of the commands below logs you in to MySQL server;

mysql

mysql -u root

Once logged in to MySQL, you can as well check the version by executing the command;

mysql> SHOW VARIABLES LIKE "%version%";

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.19                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.19-0ubuntu5               |
| version_comment          | (Ubuntu)                      |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
11 rows in set (0.00 sec)

Enable Password-Based MySQL Authentication

As mentioned above, MySQL uses unix socket authentication plugin by default.

SELECT plugin from mysql.user where User='root';

+-------------+
| plugin      |
+-------------+
| auth_socket |
+-------------+
1 row in set (0.00 sec)

To enable password based authentication, you need to switch to MySQL native password plugin, mysql_native_password.

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'auth_socket';

Once that is done, reset root user password;

ALTER USER root@localhost identified with mysql_native_password by 'myStr0nP@ssW0rd';

Reload privileges tables;

flush privileges;

Verify the changes;

SELECT User,plugin from mysql.user where User='root';

+------+-----------------------+
| User | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)

Exit the database connection and try to login as root again;

mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

There you go. You have disabled no password authentication for MySQL root user on your localhost.

Want to Master SQL? Check out the link below;

That marks the end of our guide.

Related Tutorials

Install MySQL 8 on CentOS 8

Install MySQL 8 on Debian 10 Buster

Install MySQL 8 on FreeBSD 12

Install MySQL 8 on Debian 9