Install MySQL on Ubuntu 20.04: The Definitive Guide

Want to install MySQL on Ubuntu 20.04 but struggling with outdated instructions? This step-by-step guide provides the latest, most accurate method for setting up MySQL 8.0, even tackling common installation pitfalls! Learn how to install MySQL, configure it securely, and create dedicated users for optimal database management.

Why Install MySQL on Ubuntu 20.04?

MySQL is a powerful, open-source database management system perfect for web development. Installing MySQL on Ubuntu 20.04 provides a robust foundation for applications requiring relational databases, offering benefits like:

Data Integrity: Ensures data consistency and reliability.
Scalability: Handles growing data needs as your application expands.
Security: Protects sensitive information with robust security features.
Flexibility: Compatible with various programming languages and frameworks.

Prerequisites: Getting Your Server Ready

Before diving into the installation, make sure you have this one critical thing:

An Ubuntu 20.04 server with a non-root user with sudo privileges and a UFW firewall. Follow DigitalOcean's initial server setup guide for Ubuntu 20.04

Step 1: Installing the MySQL Server Package

This section covers installing the mysql-server package using Ubuntu's apt package manager.

Update Package Index: First, update your server's package list. This ensures you're retrieving the newest software versions from the repositories:
```
sudo apt update
```
Install MySQL: Now, install the mysql-server package. This pulls all the necessary files and dependencies to set up your MySQL database:
```
sudo apt install mysql-server
```
Start the MySQL Service: Verify the MySQL server is activated and running.
```
sudo systemctl start mysql.service
```

Step 2: Configuring MySQL for Enhanced Security

Securing your MySQL installation is paramount. This step focuses on running the mysql_secure_installation script to harden your database server.

Important Warning: A known bug exists in recent mysql_secure_installation versions regarding the root password. Follow these steps before running the script to prevent errors:

Access the MySQL Prompt: Open the MySQL command-line interface using sudo:
```
sudo mysql
```
Alter Root Authentication: Run this command to change the root user's authentication to a password-based method:
```
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';
```
Replace 'your_strong_password' with a strong, unique password.
Exit: Leave the MySQL prompt by typing:
```
exit
```
Run Security Script: Now execute the mysql_secure_installation script:
```
sudo mysql_secure_installation
```
Follow Prompts: The script will ask about:
- Validate Password Plugin: Improve password security by enforcing strength requirements.
- Root Password: Set (or reset) the root password.
- Remove Anonymous Users: Disable anonymous user accounts for added security.
- Disallow Remote Root Login: Prevent remote access to the root account, reducing vulnerability.
- Remove Test Database: Delete the default test database to minimize attack surface.
- Reload Privilege Tables: Apply the settings.
Revert Root Authentication (Recommended): After the script, revert back to the more secure default auth_socket:
```
sudo mysql
```
```
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
```
```
exit
```

Step 3: Creating a Dedicated MySQL User

Avoid using the root account for routine database operations. Create a dedicated user with specific privileges.

Access MySQL Prompt: Connect to MySQL as the root user:
```
sudo mysql
```
Create a New User: Replace sammy with their desired new name and password with well, a secure password:
```
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
```
Consider using mysql_native_password if you're having PHP connectivity issues.

Grant Privileges: Grant the user necessary permissions --here's an example:

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

Flush Privileges: Apply recent privilege changes:
```
FLUSH PRIVILEGES;
```
Exit MySQL:
```
exit
```

Step 4: Testing Your MySQL Installation

Confirm that MySQL is running correctly.

Check MySQL Status: Verify if the service is running:
```
systemctl status mysql.service
```
Connect to MySQL as the New User: Log in using the created user credentials:
```
mysql -u sammy -p
```
Enter the password that corresponds to sammy for authentication.

Congratulations! MySQL is now successfully installed and configured on your Ubuntu 20.04 server. You’re ready to create and manage databases for your web applications.

Install MySQL on Ubuntu 20.04: The Definitive Guide

Why Install MySQL on Ubuntu 20.04?

Data Integrity: Ensures data consistency and reliability.

Scalability: Handles growing data needs as your application expands.

Security: Protects sensitive information with robust security features.

Flexibility: Compatible with various programming languages and frameworks.

Step 1: Installing the MySQL Server Package

This section covers installing the mysql-server package using Ubuntu's apt package manager.

Update Package Index: First, update your server's package list. This ensures you're retrieving the newest software versions from the repositories:

sudo apt update

Install MySQL: Now, install the mysql-server package. This pulls all the necessary files and dependencies to set up your MySQL database:

sudo apt install mysql-server

Start the MySQL Service: Verify the MySQL server is activated and running.

sudo systemctl start mysql.service

Step 2: Configuring MySQL for Enhanced Security

Securing your MySQL installation is paramount. This step focuses on running the mysql_secure_installation script to harden your database server.

Important Warning: A known bug exists in recent mysql_secure_installation versions regarding the root password. Follow these steps before running the script to prevent errors:

Access the MySQL Prompt: Open the MySQL command-line interface using sudo:

sudo mysql

Alter Root Authentication: Run this command to change the root user's authentication to a password-based method:

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

Replace 'your_strong_password' with a strong, unique password.

Exit: Leave the MySQL prompt by typing:

exit

Run Security Script: Now execute the mysql_secure_installation script:

sudo mysql_secure_installation

Follow Prompts: The script will ask about:

Validate Password Plugin: Improve password security by enforcing strength requirements.
Root Password: Set (or reset) the root password.
Remove Anonymous Users: Disable anonymous user accounts for added security.
Disallow Remote Root Login: Prevent remote access to the root account, reducing vulnerability.
Remove Test Database: Delete the default test database to minimize attack surface.
Reload Privilege Tables: Apply the settings.

Revert Root Authentication (Recommended): After the script, revert back to the more secure default auth_socket:

sudo mysql

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

exit

Step 3: Creating a Dedicated MySQL User

Avoid using the root account for routine database operations. Create a dedicated user with specific privileges.

Access MySQL Prompt: Connect to MySQL as the root user:

sudo mysql

Create a New User: Replace sammy with their desired new name and password with well, a secure password:

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

Consider using mysql_native_password if you're having PHP connectivity issues.

Grant Privileges: Grant the user necessary permissions --here's an example:

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

Flush Privileges: Apply recent privilege changes:

FLUSH PRIVILEGES;

Exit MySQL:

exit

Step 4: Testing Your MySQL Installation

Confirm that MySQL is running correctly.

Check MySQL Status: Verify if the service is running:

systemctl status mysql.service

Connect to MySQL as the New User: Log in using the created user credentials:

mysql -u sammy -p

Enter the password that corresponds to sammy for authentication.

Congratulations! MySQL is now successfully installed and configured on your Ubuntu 20.04 server. You’re ready to create and manage databases for your web applications.