The Ultimate Guide: Effortlessly Install MySQL on Ubuntu 20.04 (2024)

Want to install MySQL on Ubuntu 20.04, but find the process intimidating? You're not alone! This guide simplifies the process, offering a step-by-step approach to get your database up and running quickly and securely.

Why Install MySQL on Ubuntu?

MySQL is a popular open-source database management system perfect for numerous applications. If you're building a website or app, MySQL offers a reliable and scalable solution for data storage.

Prerequisites

Before you jump in, ensure you have:

• An Ubuntu 20.04 server.
• A non-root user with sudo privileges (follow this guide for setup).

Step 1: Install MySQL - It's Easier Than You Think

First, do these commands to update your package list and get the installation ready.

1. Update the Package Index: Keep your server current with these commands.

   sudo apt update
   

2. Install the MySQL Server: Install the mysql-server package using apt:

sudo apt install mysql-server

These commands grab and install the MySQL server, but some extra steps are needed to make sure the setup is secure.

Step 2: Secure Your MySQL Installation

For a secure setup, run MySQL's security script. This script hardens your installation by addressing common vulnerabilities.

Important: As of July 2022, you may encounter an error due to password authentication issues for the root user. To avoid this, follow these steps before running mysql_secure_installation:

1. Access the MySQL Prompt:

   sudo mysql
   

2. Alter Root Authentication:

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

   Replace your_password with a strong password.

3. Exit the MySQL Prompt:

   exit
   

4. Run the Security Script: This will prompt you with a series of questions.

   sudo mysql_secure_installation
   

5. Revert Root Authentication: After running the security script, you can reopen MySQL and change the root user’s authentication method back to the default, auth_socket. Run these commands to do that:

mysql -u root -p

Then go back to using the default authentication method using this command:

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

This will mean that you can once again connect to MySQL as your root user using the sudo mysql command.

Navigating the Security Script Prompts

• Validate Password Plugin: Consider enabling this to enforce strong passwords for all MySQL users.
• Root Password: Set a strong and unique password for the MySQL root user.
• Remove Anonymous Users: Recommended for enhanced security.
• Disallow Remote Root Login: Prevents remote access to the root account.
• Remove Test Database: Removes a sample database, reducing potential vulnerabilities.

Step 3: Create a Dedicated User

Creating a dedicated MySQL user:

1. Log in to MySQL as Root:

   sudo mysql
   

2. Create a New User: Create a user with a secure password.

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

Note: There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:

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

3. Grant Privileges: Assign necessary permissions to the new user. This example grants broad privileges.

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

Warning: Some users may want to grant their MySQL user the ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges.

4. Flush Privileges:

   FLUSH PRIVILEGES;
   

5. Exit MySQL:

   exit
   

Step 4: Testing the Installation

Verify MySQL is running by checking its status:

systemctl status mysql.service

If not running, start it:

sudo systemctl start mysql

This comprehensive guide equips you with the knowledge to confidently install MySQL on Ubuntu 20.04, setting the stage for your next successful project.