Effortlessly Install MySQL on Ubuntu 20.04: A Complete Guide

Ready to set up your database? This tutorial provides a step-by-step guide to installing MySQL 8.0 on Ubuntu 20.04, ensuring a smooth and secure process. You'll have a working relational database ready for your next project in no time. Quickly set up a MySQL quickly.

Prerequisites: Before You Begin

• Ubuntu 20.04 Server: You'll need an Ubuntu 20.04 server.
• Non-Root User: Set up a non-root administrative user with sudo privileges.
• Firewall: Configure a firewall using UFW.

Refer to the initial server setup guide for Ubuntu 20.04 for detailed instructions.

Step 1: Installing MySQL Server on Ubuntu

Let's get started! The easiest method to install MySQL on Ubuntu 20.04 is through the APT package repository. This installs MySQL version 8.0.

1. Update Package Index: Keep your system current by updating the package index:

   sudo apt update
   

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

   sudo apt install mysql-server
   

3. Start MySQL Service: Make sure the MySQL server is running

   sudo systemctl start mysql.service
   

Step 2: Secure Your MySQL Installation

A fresh MySQL installation requires running a security script to enhance security.

Addressing a Potential Error (July 2022 Update)

• Root Authentication: The mysql_secure_installation script may fail due to root authentication issues.
• Workaround: You first have to adjust how your root MySQL user authenticates.

How to Fix the Authentication Error:

1. Access MySQL Prompt: Open the MySQL prompt as root.

   sudo mysql
   

2. Alter User Authentication: Use the ALTER USER command to switch the root user authentication method.

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

   Remember to replace 'password' with a strong password.

3. Exit MySQL Prompt: Exit the MySQL prompt.

   exit
   

After running the script, change the root user’s authentication method back to default:

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

Running the mysql_secure_installation Script

1. Execute the Script: Secure your MySQL installation by running the security script.

   sudo mysql_secure_installation
   

2. Validate Password Plugin (Optional): The script first prompts you to set up the Validate Password Plugin to test the strength of new MySQL user passwords.

   • Choose a policy level (0, 1, or 2) based on your password strength requirements, or press any key for "No".

3. Set Root Password: Set a secure password for the MySQL root user.

4. Answer Prompts: Accept the defaults for the remaining questions to:

   • Remove anonymous users.
   • Disallow remote root login.
   • Remove the test database.
   • Reload privilege tables.

Step 3: Create a Dedicated MySQL User with Specific Privileges

For security best practices, create a dedicated MySQL user for database management.

1. Access MySQL Prompt: Log in to MySQL as the root user.

   sudo mysql
   

2. Create a New User: Create a new user account with a strong password. You need to determine the authentication plugin.

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

3. Grant Privileges: Grant the necessary privileges to the new user. For example:

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

4. Flush Privileges: Apply the changes.

   FLUSH PRIVILEGES;
   

5. Exit MySQL: Exit the MySQL client.

   exit
   

Step 4: Testing Your MySQL Installation on Ubuntu 20.04

Verify that MySQL is running correctly.

1. Check MySQL Status: Check the status of the MySQL service.

   systemctl status mysql.service
   

   The output should indicate that the service is active (running).

2. Connect to the Database: Use the mysqladmin tool to connect and run administrative commands.

   mysqladmin -u sammy -p version
   

   Replace sammy with your MySQL username. You will be prompted for the password.

Conclusion

You've successfully installed MySQL on Ubuntu 20.04. This installation of MySQL is ready for use in your web applications. Always use the latest security measures.