Easy Steps To Install MySQL on Ubuntu 22.04 – OrcaCore

This tutorial is designed to guide you through the process of How To Install MySQL on Ubuntu 22.04. MySQL is a widely used open-source SQL relational database management system, developed and supported by Oracle. It organizes information into separate "tables" and connects them using "keys," making it a relational database. Understanding how to install MySQL on Ubuntu 22.04 is a valuable skill for any developer or system administrator.

You can now proceed to the following steps on the Orcacore website to set MySQL database management on Ubuntu 22.04.

Steps To Install and Configure MySQL on Ubuntu 22.04

Before you begin the installation process, it’s important to log in to your server as a non-root user with sudo privileges. You can refer to our guide on Initial Server Setup with Ubuntu 22.04 for detailed instructions on how to do this.

1. Install MySQL on Ubuntu 22.04

First, update your local package index to ensure you have the latest versions of available packages:

sudo apt update

Next, use the following command to install the MySQL server package:

sudo apt install mysql-server -y

Start and Enable MySQL Server

Once the installation is complete, start the MySQL service and enable it to start automatically on boot:

# sudo systemctl start mysql.service
# sudo systemctl status mysql.service

To verify that the service is running correctly, use the following command:

sudo systemctl status mysql.service

2. Configure MySQL on Ubuntu 22.04 | Set MySQL Root Password

When attempting to run the MySQL secure installation script on Ubuntu, you might encounter an error. This is because the script tries to set a password for the installation’s root MySQL account. However, by default on Ubuntu installations, this account is not configured to connect using a password.

To resolve this issue, log in to your MySQL shell:

sudo mysql

Then, execute the following ALTER USER command to change the root user’s authentication method to one that uses a password:

<mark><strong>mysql&gt;</strong></mark>
 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<mark>password</mark>';

Remember to replace <mark>password</mark> with your desired password.

After making this change, exit the MySQL prompt on Ubuntu 22.04:

<mark><strong>mysql&gt;</strong></mark> exit

Run MySQL Secure Installation Script

Following that, you can run the mysql_secure_installation script without issue.

sudo mysql_secure_installation

The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid.

From there press Y to continue.

When you are done, you access your mySQL shell by using the password you have created:

sudo mysql -u root -p

If you want to connect to MySQL as your root user using the sudo mysql command, you can run the command below from your MySQL shell:

<mark><strong>mysql&gt;</strong></mark> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

That’s it, you are done. Now you should have successfully completed the process of how to install MySQL on Ubuntu 22.04.

Conclusion

At this point, you have learned How to Install and Configure MySQL on Ubuntu 22.04. MySQL Server on Ubuntu 22.04 is used for database management, storing, organizing, and retrieving structured data efficiently.

Please subscribe to us on Facebook, Instagram, and YouTube.

FAQs

Why use MySQL Server on Ubuntu 22.04?

It is lightweight, secure, and scalable, making it ideal for web applications, enterprise databases, and cloud-based storage.

Where is the MySQL configuration file located?

The main configuration file is /etc/mysql/mysql.conf.d/mysqld.cnf.

What should I do if MySQL fails to start?

You can check logs with the following command:
sudo journalctl -u mysql --no-pager | tail -20

How do I check if MySQL is running?

You can easily use the command below:
systemctl status mysql

Alternative Solutions for Configuring MySQL Root Access

The article highlights a specific method for configuring MySQL root access after installation on Ubuntu 22.04, focusing on changing the authentication method. While effective, there are alternative approaches to achieving a similar outcome, providing flexibility and catering to different security preferences.

Alternative 1: Using the unix_socket Plugin for Enhanced Security

Instead of switching to mysql_native_password, which relies on password-based authentication, you can leverage the unix_socket plugin for local connections. This plugin authenticates users based on their Unix socket credentials, eliminating the need for a password when connecting from the same server. This significantly enhances security, especially for local development or internal applications where network access to MySQL isn’t required.

To configure the root user to use unix_socket, follow these steps:

Log in to the MySQL shell as root using the sudo mysql command (this leverages the auth_socket plugin, which is often the default after installation).

Execute the following SQL command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

Exit the MySQL shell:
```
exit
```

Now, you can connect to MySQL as the root user from the command line using sudo mysql without needing a password. This approach is particularly useful when managing MySQL locally or when you want to restrict root access to the server itself. It’s important to note that this method only works for local connections; you’ll need a different authentication mechanism for remote access.

Alternative 2: Creating a Dedicated Administrative User

Instead of directly modifying the root user’s authentication, a more secure and recommended practice is to create a dedicated administrative user with specific privileges. This approach adheres to the principle of least privilege, limiting the potential damage if the administrative account is compromised.

Here’s how to create a dedicated administrative user:

Log in to the MySQL shell as root using the sudo mysql command.
Create a new user with a strong password:
```
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_strong_password';
```
Replace 'admin'@'localhost' with your desired username and hostname (or % for any host) and 'your_strong_password' with a strong, unique password.
Grant the necessary privileges to the new user. For full administrative access, grant all privileges:
```
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
```
Alternatively, grant only the specific privileges needed for administrative tasks:
```
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'admin'@'localhost' WITH GRANT OPTION;
```
The WITH GRANT OPTION allows the administrative user to grant privileges to other users.
Flush the privileges to apply the changes:
```
FLUSH PRIVILEGES;
```
Exit the MySQL shell:
```
exit
```

Now, you can connect to MySQL as the new administrative user using the following command:

mysql -u admin -p

This approach offers several advantages:

Improved Security: Limits the potential damage if the administrative account is compromised.
Auditing: Easier to track administrative actions performed by the dedicated user.
Flexibility: Allows for fine-grained control over privileges, granting only the necessary permissions.

Choosing between these alternatives depends on your specific security requirements and environment. The unix_socket plugin offers enhanced security for local connections, while creating a dedicated administrative user promotes better security practices and auditing capabilities. Regardless of the chosen method, ensuring proper configuration of MySQL root access is crucial for maintaining the security and integrity of your database. Following these steps carefully will ensure a smooth process on how to install MySQL on Ubuntu 22.04.