Installing MySQL on Ubuntu 20.04 (Focal Fossa)

Reviewed on 03 October 2024 • Published on 22 June 2020

networking
database
sql
MySQL
MariaDB

MySQL is a reliable and mature open-source relational database management system, and its first release dates back to 1995. It has been in constant development since then and is one of the most used database management systems. MySQL exists in different editions, a free and open-source community edition, as well as several commercial licenses offering additional features. The software was initially developed by the company “MySQL AB” and is currently owned and developed by Oracle. The source code of the MySQL server is publicly available on GitHub.

The database management system provides a stand-alone client, allowing users to interact directly with their databases using Structured Query Language (SQL). MySQL is used as a database engine for many popular web applications like Drupal, phpBB, Joomla, and WordPress. It is also being used by numerous other popular websites, including Facebook, Twitter, and YouTube, to name a few.

MySQL is also part of the LAMP software stack, an acronym for Linux, Apache (webserver), MySQL, and PHP.

Ready-to-use binary packages of MySQL are available for various operating systems, from macOS X, and Windows to most Linux distributions. This tutorial will teach you how to install MySQL on Ubuntu Focal Fossa (20.04 LTS). For installation instructions on other platforms, refer to the official installation guide provided by MySQL.

Before you start

To complete the actions presented below, you must have:

A Scaleway account logged into the console
Owner status or IAM permissions allowing you to perform actions in the intended Organization
An SSH key
An Instance running on Ubuntu Focal Fossa (20.04) or later
sudo privileges or access to the root user

Installing MySQL

Oracle provides a .deb package to configure the MySQL repository on your machine automatically.

Download the MySQL apt configuration from the official MySQL developer server:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb

Install the package using dpkg by running the following command:

dpkg -i mysql-apt-config_0.8.22-1_all.deb

Select MySQL 8 server when you are prompted which MySQL product you want to install. Click OK to confirm.

Verify the configuration file for the apt package manager, generated during the installation of the package above, by running the command cat /etc/apt/sources.list.d/mysql.list. The output should look like the following example:

### THIS FILE IS AUTOMATICALLY CONFIGURED ###
# You may comment out entries below, but any other modifications may be lost.
# Use command 'dpkg-reconfigure mysql-apt-config' as root for modifications.
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-apt-config
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-8.0
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-tools
#deb http://repo.mysql.com/apt/ubuntu/ focal mysql-tools-preview
deb-src http://repo.mysql.com/apt/ubuntu/ focal mysql-8.0

If it does not look like the example above, or if you want to add additional features to MySQL you can reconfigure the configuration file by running dpkg-reconfigure mysql-apt-config.

Update the apt package manager to ensure it uses the newly added repository:

apt update

Install the MySQL server and client applications using apt:

apt install mysql-server mysql-client -y

Initializing MySQL

Once the MySQL server is installed on your system, it needs to be initialized to secure connections to it.

Run the interactive configuration tool:

mysql_secure_installation

You will be prompted the following questions during the configuration:

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 set up VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: Y <-- Type y or Y and press Enter to confirm
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 <-- Enter 2 to force strong passwords with a password length >= 8, numeric, mixed case, special characters and validation against a dictionary file
Please set the password for root here.
New password: <-- Enter your strong password
Re-enter new password: <-- Repeat your 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 <-- MySQL has evaluated the strength of your password in the line above. If you wish to continue with the password provided, type y or Y and confirm by pressing Enter.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to remove anonymous users from the MySQL server
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y <-- Type y our Y and press Enter to restrict connections to the database server from localhost. If you have need to access your database from a remote machine, enter any other key before confirming by pressing Enter.
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to remove the MySQL test database then confirm by pressing Enter.
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to reload the MySQL privilege table to ensure all changes made so far will take effect. Confirm by pressing Enter.
All done!

Your MySQL server application is now secured and ready for production.

Connecting to the MySQL server using mysql client

As your MySQL server is up and running now, you can connect to it for the first time using the mysql client application.

Run the following command to log into MySQL using the superuser account root:

mysql -u root -p

When prompted, enter the password set during the initialization process and confirm by pressing Enter:

Enter password:

You are now logged into the MySQL shell. The server displays some connection information as well as information about the installed MySQL version. In the last line the mysql> prompt appears. Send your SQL statements from this prompt to the server for execution:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.20-0ubuntu0.20.04.1 (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>

Creating a new MySQL database

Later in this tutorial, we will store and modify data in a database. Therefore, we need to create a new database and verify its creation:

To get an idea of the existing database structure on our MySQL server, we will use the SHOW DATABASES command which returns a list of all existing databases on the server:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

As you can see in the output above, our server currently contains four databases: information_schema, mysql, performance_schema, and sys. These are internal databases in use by MySQL.

Create a new MySQL database called tutorial with the following command. We will use this database to store our data:

mysql> CREATE DATABASE tutorial;

The response from the server looks as follows: Query OK, 1 row affected (0.00 sec) and confirms that the query has been executed. 3. To ensure that the database has been created, run the SHOW DATABASES command again:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tutorial           |
+--------------------+
5 rows in set (0.00 sec)

As you can see, the output includes one line more - our database tutorial. It has been created successfully.

Adding a new MySQL user & Granting permissions

We do not want to use the superuser account root to manipulate data in our tutorial database for security reasons. Therefore, we will create a regular user with restricted permissions.

Add a new user using the CREATE USER command:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'a_secret_password';

Replace myuser with the login for your new user, the part localhost defines from which host your user is allowed to log in. As we have restricted access to the MySQL server to our local machine, keep this value. Replace a_secret_password with a strong and secure password for the user.

By default, the user has no permissions. Grant permission to the tutorial table so the user can make changes to all data stored in this table:

mysql> GRANT ALL PRIVILEGES ON tutorial.* TO 'myuser'@'localhost';

The ALL privilege allows a user to have full access to a designated database. Other GRANT commands define more restrictive permissions:

CREATE grants permission to a user to create new tables or databases
DROP grants permission to a user to delete tables or databases
DELETE grants permission to a user to delete rows from tables
INSERT grants permission to a user to insert rows into tables
SELECT grants permission to a user to use the SELECT command to read through databases
UPDATE grants permission to a user to update table rows

The syntax for all grant options is the same and can be applied on the database or even table level:

mysql> GRANT grant_type ON database_name.table_name TO ‘myuser'@'localhost';

Once the user is created and permissions are configured, leave the superuser account:

mysql> EXIT;

Creating tables and adding data

Now that we have our table and user ready, it is time to create some tables, fill them with data, and update the data.

Reconnect to the MySQL prompt using the myuser user, you created in the previous step:

mysql -u myuser -p

The MySQL prompt displays. Change into the tutorial database you have created in a previous step:

mysql> USE tutorial;

A Database changed message indicates that you have changed to the tutorial database.

Create a new table employees to manage the inventory of all employees of a fictive company

mysql> CREATE TABLE employees
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name            VARCHAR(150) NOT NULL,                # Name of the employee
  team            VARCHAR(150) NOT NULL,                # Team of the employee
  birthday        DATE NOT NULL,                        # Birthday of the employee
  PRIMARY KEY     (id)                                  # Make the ID the primary key
);

Use DESCRIBE to visualize the table structure and to verify everything has been created accordingly:

mysql> DESCRIBE employees;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(150) | NO   |     | NULL    |                |
| team     | varchar(150) | NO   |     | NULL    |                |
| birthday | date         | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

Now that we have the table ready, update it by filling in some fictitious employee data. When you look at the query, you notice that the value for ID is not in it. This value will be auto-incremented by MySQL when entering the data:

mysql> INSERT INTO employees ( name, team, birthday) VALUES
  ( 'Peter Smith', 'DevOps', '2003-07-21' ),
  ( 'John White', 'Marketing', '1988-06-04' ),
  ( 'Steve Shining', 'DevOps', '2001-06-04' ),
  ( 'Charlie Connor', 'Support', '1987-03-03' );

MySQL will check the data and return a status report of your query: Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0.

Retrieving data from a table

The SELECT statement can be used to retrieve data stored in an existing table.

Use a wildcard (*) to retrieve a list of all your employees:

mysql> SELECT * FROM employees;
+----+----------------+-----------+------------+
| id | name           | team      | birthday   |
+----+----------------+-----------+------------+
|  1 | Peter Smith    | DevOps    | 2003-07-21 |
|  2 | John White     | Marketing | 1988-06-04 |
|  3 | Steve Shining  | DevOps    | 2001-06-04 |
|  4 | Charlie Connor | Support   | 1987-03-03 |
+----+----------------+-----------+------------+
4 rows in set (0.00 sec)

You can use a WHERE clause on specific columns and rows to filter results by a certain condition. Run the following command to return the names of all employees in the DevOps team that are born before January 1st 2003:

mysql> SELECT name FROM employees WHERE birthday < '2003-01-01' AND team = 'DevOps';
+---------------+
| name          |
+---------------+
| Steve Shining |
+---------------+
1 row in set (0.00 sec)

Deleting data from tables

If one of your employees has left the company, you want to delete his record from your employee database.

Your employee Steve Shining has left the company. Remove his data from the database using the DELETE statement:

mysql> DELETE FROM employees WHERE name='Steve Shining';
Query OK, 1 row affected (0.09 sec)

Verify if the row has been removed from the table:

mysql> SELECT * FROM employees;
+----+----------------+-----------+------------+
| id | name           | team      | birthday   |
+----+----------------+-----------+------------+
|  1 | Peter Smith    | DevOps    | 2003-07-21 |
|  2 | John White     | Marketing | 1988-06-04 |
|  4 | Charlie Connor | Support   | 1987-03-03 |
+----+----------------+-----------+------------+
3 rows in set (0.00 sec)

Adding and deleting columns on tables

You also want to keep a record of your employees’ email addresses to make internal mailings easier for the marketing team.

Add a column using an ALTER TABLE...ADD statement. By specifying, for example, an AFTER clause, you can define the location of the new column:

mysql> ALTER TABLE employees ADD email varchar(150) AFTER name;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verify that the table has been modified as wanted:

mysql> DESCRIBE employees;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(150) | NO   |     | NULL    |                |
| email    | varchar(150) | YES  |     | NULL    |                |
| team     | varchar(150) | NO   |     | NULL    |                |
| birthday | date         | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

If you no longer need a certain column of your table, remove it using ALTER TABLE...DROP:

mysql> ALTER TABLE employees DROP birthday;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Updating the MySQL root password

You can update the MySQL root password of your database server when logged as root onto the MySQL shell:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secret_password';

You have now discovered the basics of managing MySQL databases. For more information, refer to the official documentation.