Installing MySQL on Ubuntu 22.04 LTS – A Step-by-Step Guide

MySQL, a commonly utilized open-source database management system, is
frequently incorporated into the well-known LAMP (Linux, Apache, MySQL,
PHP/Python/Perl) stack. It embraces the relational model and utilizes
Structured Query Language (SQL) to ensure efficient data
administration.

This guide will take you through the steps of installing MySQL version
8.0.33 on a server running Ubuntu 22.04. Upon completing this tutorial, you
will possess a functional relational database that you can utilize to power
your upcoming websites or applications.

Prerequisites

To follow along, ensure you have:

An Ubuntu 22.04 server with a non-root administrative user and a properly
configured firewall using UFW

Step1: Installing MySQL

On Ubuntu 22.04, MySQL installation is a breeze thanks to the APT package
repository. As of now, the default Ubuntu repository houses MySQL version
8.0.33.

Begin by updating your server’s package index if you haven’t done so
recently:

sudo apt update

Then, proceed to install the mysql-server package:

sudo apt install mysql-server

Ensure the server is up and running with the systemctl start command:

sudo systemctl start mysql.service

These commands install and start MySQL, but they won’t prompt you to set a
password or make further configuration changes. This can leave your MySQL
installation vulnerable. Let’s address this in the next step.

Step2: Configuring MySQL

For fresh MySQL installations, it’s advisable to run the included security
script. This script enhances security by modifying default settings like
remote root logins and sample users.

Warning: Running the mysql_secure_installation script without
additional configuration will result in an error. This is because the script
attempts to set a password for the root MySQL account, but the default
configuration on Ubuntu doesn’t allow password-based authentication for this
account.

The script would fail silently after trying to set the root account
password. However, the script now returns an error after you input and
confirm a password.

To bypass this issue, change how your root MySQL user authenticates:

1. Launch the MySQL prompt:

sudo mysql

2. Execute the following ALTER USER command to change the root user’s
authentication method to mysql_native_password:

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY
  'password';

3. Exit the MySQL prompt:

exit

Now you can run the mysql_secure_installation script without encountering
issues.

Upon script completion, you can revert the root user’s authentication method
back to the default auth_socket. To use password-based authentication for
the root MySQL user, run:

mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This allows you to reconnect using sudo mysql.

Execute the security script with sudo:

sudo mysql_secure_installation

This script guides you through enhancing your MySQL installation’s security.
The initial prompt asks if you want to set up the Validate Password Plugin,
which assesses password strength for new MySQL users.

The strongest policy (enter 2) mandates passwords to be at least eight
characters long, containing uppercase, lowercase, numeric, and special
characters. The script then guides you through securing your installation.

Step3:Creating a Dedicated MySQL User and Granting Privileges

Upon installation, MySQL creates a root user with full privileges over the
server. However, it’s best practice to avoid using this account for
non-administrative tasks. This step covers creating a new user and granting
them privileges.

For MySQL 5.7 and later versions, the root user defaults to auth_socket
plugin-based authentication rather than password-based. To access the root
MySQL user, invoke mysql with sudo privileges:

sudo mysql

To create a new user with password-based authentication, use the CREATE USER
statement:

CREATE USER 'deep'@'localhost' IDENTIFIED BY 'password';

Next, grant privileges to the user:

GRANT PRIVILEGE ON database.table TO 'deep'@'localhost';

For more granular privileges, use a command like:

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

After creating the user and assigning privileges, flush privileges:

FLUSH PRIVILEGES;

Exit the MySQL client:

exit

In the future, log in as your new MySQL user:

mysql -u deep -p

Step 4: Testing MySQL

MySQL should be running by default. Verify its status:

systemctl status mysql.service

If MySQL isn’t running, start it with:

sudo systemctl start mysql

For an extra check, connect to the database using mysqladmin:

sudo mysqladmin -p -u deep version

Conclusion

Congratulations, you’ve successfully installed MySQL on your Ubuntu 22.04
server!