How to Install and Set Up mysql 8 on Ubuntu 20.04

In this guide we are going through the process of installing and configuring mysql server 8 in Ubuntu 20.04. We will also test our installation by creating a database and a user.

MySQL is one of the popular open-source relational database management system. It is commonly installed as part of the popular LAMP or LEMP (Linux, Apache/Nginx, MySQL/MariaDB, PHP/Python/Perl) stack.

Preprequisites

To follow along this guide, ensure you have the following:

Up to date Ubuntu Server 20.04 with Internet access
Server root access or user with sudo acces

Ensure our ubuntu server is up to date

Before proceeding let us ensure that the ubuntu server is up to date. First update the repos then do a system upgrade to ensure all the installed packages are up to date:

In your terminal, type these. The -y option in apt upgrade is to ensure that the system doesn’t pause for us to accept the upgrade.

1
2
sudo apt update
sudo apt upgrade -y

Set up the repo for mysql 8 installation

Mysql server 8 is not available in the default ubuntu repositories. The mysql team provides a downloadable .deb file that will configure the repositories for mysql server 8 installation. Download it with this command:

1
curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb

You should see an output almost similar to this:

1
2
3
4
5
$ curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 35536  100 35536    0     0  55874      0 --:--:-- --:--:-- --:--:-- 55874

Once the download is done, we need to install the downloaded deb file. Use this command to install:

1
sudo dpkg -i ./mysql-apt-config_0.8.19-1_all.deb

This will open a configuration window prompting you to choose mysql server version and other components such as cluster, shared client libraries, or the MySQL workbench.

For now since we are only interested in the Mysql Server Installation, leave the default settings and click OK to proceed.

This is the output on successful installation and configuration.

1
2
3
4
5
6
7
$ sudo dpkg -i ./mysql-apt-config_0.8.19-1_all.deb
(Reading database ... 91535 files and directories currently installed.)
Preparing to unpack .../mysql-apt-config_0.8.19-1_all.deb ...
Unpacking mysql-apt-config (0.8.19-1) over (0.8.19-1) ...
Setting up mysql-apt-config (0.8.19-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK

Install Mysql 8 Server

Now that the repos have been added to include mysql server, we can now install the mysql-server package. First, refresh the repositories to get the latest from the added repo:

1
sudo apt update

Then Install Mysql 8 Server using this command:

1
sudo apt install -y mysql-server

Enter your administrator credentials, and the system will install the MySQL server package, client packages, and database common files.

The installation will prompt you to enter and confirm a root user and password for the MySQL database.

After that you will be asked to select Authentication plugin. It is recommended that you choose to use a strong password:

After that the new password will be set and service reloaded.

Confirm that mysql server is up and running with this command:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
$ sudo systemctl status mysql
&#x25CF; mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2021-09-28 13:08:22 UTC; 2min 23s ago
       Docs: man:mysqld(8)
             http:&#47;&#47;dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 45123 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 45173 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4710)
     Memory: 353.0M
     CGroup: /system.slice/mysql.service
             &#x2514;&#x2500;45173 /usr/sbin/mysqld

Sep 28 13:08:21 ip-172-26-11-229 systemd&#91;1]: Starting MySQL Community Server...
Sep 28 13:08:22 ip-172-26-11-229 systemd&#91;1]: Started MySQL Community Server.

The Active: active (running) since ... portion in the above shows that the app is up and running.

Test connection to mysql with the root password

Now that mysql is all set up and is running, we need to confirm that it can accept connections.

To test, connect to mysql with root user mysql -h 127.0.0.1 -u root -p

Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

Check mysql version to confirm that everything is Ok:

1
2
3
4
5
6
7
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

Extra - Creating users in mysql

To connect remotely to mysql, it is recommended to create an app specific user and a database. Let us do that here.

To create a database, use this command in the mysql prompt; This command creates a. database called citizix_db.

1
create database citizix_db;

To create a user, use this in the mysql prompt; this command will create a user citizix_user that can connect from anywhere with the specified password:

1
create user 'citizix_user'@'%' identified by 'S0mStrongPa$word';

Now grant the created user all privileges on the created database:

1
grant all privileges on citizix_db.* to 'citizix_user'@'%';

Confirm that you can connect as the new user. Use the mysql command on the terminal specifying the host with -h and user with -u. Also supply the password prompt flag -p then enter the password:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
$ mysql -h 192.168.10.20 -u citizix_user -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

Confirm the permissions we have using these commands:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
mysql> show grants;
+--------------------------------------------------------------+
| Grants for citizix_user@%                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `citizix_user`@`%`                     |
| GRANT ALL PRIVILEGES ON `citizix_db`.* TO `citizix_user`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.44 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| citizix_db         |
| information_schema |
+--------------------+
2 rows in set (0.22 sec)

mysql>

Up to this point, we have been able to install mysql server 8 on Ubuntu 20.04 and tested that its working as expected by adding some user and a database.