How to Install MySQL on Ubuntu 20.04: A Complete Guide

Looking to install MySQL on Ubuntu 20.04? This comprehensive guide will walk you through each step, ensuring a secure and functional database server for your applications. We'll cover everything from initial installation to creating users and granting appropriate privileges.

Why Install MySQL on Ubuntu?

MySQL is a popular, open-source relational database management system used in a wide range of applications. Installing it on Ubuntu provides a robust and reliable platform for storing and managing your data.

• Open Source: Benefit from a free and community-supported database solution.
• Reliability: Leverage a stable and widely-used database system.
• Flexibility: Easily integrate with various programming languages and frameworks.

Prerequisites: Server Setup

Before installing MySQL, ensure you have the following:

• An Ubuntu 20.04 server.
• A non-root administrative user.
• A firewall configured with UFW (Uncomplicated Firewall).

Refer to DigitalOcean's initial server setup guide for Ubuntu 20.04 for detailed instructions.

Step 1: Installing the MySQL Server Package

The easiest way to install MySQL on Ubuntu is using the APT package repository. Follow these steps:

1. Update Package Index: Keep your system up-to-date.

   sudo apt update
   

   Copy

2. Install MySQL: Use apt to retrieve the mysql-server package.

   sudo apt install mysql-server
   

   Copy

3. Start MySQL Service: Start the database server.

   sudo systemctl start mysql.service
   

   Copy

This will download and install MySQL version 8.0.

Step 2: MySQL Secure Installation Configuration

After installation, configure MySQL for enhanced security. This involves running the mysql_secure_installation script.

Important: Due to authentication changes, you may encounter an error. We will address this first.

1. Access MySQL Prompt: Open up the MySQL prompt.

   sudo mysql
   

   Copy

2. Alter User Authentication: Change the MySQL root user's authentication method to mysql_native_password. Replace 'password' with a secure password.

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

   Copy

3. Exit MySQL Prompt: Exit the MySQL environment before running the script.

   exit
   

   Copy

4. Run Security Script: Execute the secure installation script.

   sudo mysql_secure_installation
   

   Copy

Follow the prompts:

• Validate Password Plugin: Consider setting this up for stronger passwords.
• Root Password: Set a strong password for the MySQL root user.
• Remove Anonymous Users: Recommended for security.
• Disallow Remote Root Login: Enhances security.
• Remove Test Database: Reduces potential vulnerabilities.
• Reload Privilege Tables: Ensures immediate application of changes.

5. Change Authentication Back (Optional):

Once the security script completes, you can then reopen MySQL and change the root user’s authentication method back to the default, auth_socket. To authenticate as the root MySQL user using a password, run this command:

mysql -u root -p

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

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

Step 3: Creating a Dedicated MySQL User

For security reasons, avoid using the root account for regular database operations. Create a dedicated user with specific privileges.

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

   sudo mysql
   

   Copy

   Or, if you've configured a password for root:

   mysql -u root -p
   

   Copy

2. Create a New User: Replace sammy with your desired username and password with a strong password.

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

   Copy

   Note: If you encounter issues with PHP applications, use the mysql_native_password plugin:

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

   Copy

3. Grant Privileges: Grant the new user the necessary privileges. Adjust privileges based on your needs.

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

   Copy

   Warning: Avoid granting ALL PRIVILEGES unless absolutely necessary.

4. Flush Privileges: Reload the grant tables.

   FLUSH PRIVILEGES;
   

   Copy

5. Exit MySQL: Exit the MySQL client.

   exit
   

   Copy

Step 4: Testing your MySQL Installation

Verify that MySQL is running correctly.

1. Check MySQL Status: Use systemctl to check the server.

   systemctl status mysql.service
   

   Copy

2. Connect to MySQL: Log in as your newly created user.

   mysql -u sammy -p
   

   Copy

You should be prompted for the user's password. After entering it successfully, you'll have access to the MySQL command-line interface.

Long-Tail Keywords Summary

Remember these long-tail keywords when optimizing your MySQL setups:

• Install MySQL on Ubuntu 20.04: The main goal covered in the guide.
• Secure MySQL installation Ubuntu: We covered best practices for securing your MySQL server.

Conclusion

Congratulations! You've successfully installed and configured MySQL on Ubuntu 20.04. This guide provides a solid foundation for building and deploying your data-driven applications. Remember to regularly update and maintain your MySQL installation for optimal performance and security.