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!