How to Install MySQL on a Linux Web Server

How to Install MySQL on Ubuntu 20.04

Overview

MySQL is a widely-used open-source database management system, often included in the LAMP stack (Linux, Apache, MySQL, PHP/Python/Perl). It uses a relational model and SQL (Structured Query Language) to manage data.

This guide will show you how to install MySQL 8.0 on an Ubuntu 20.04 server. By the end, you’ll have a functional relational database for your website or application.

Step 1 — Installing MySQL

To install MySQL on Ubuntu 20.04, use the APT package repository. The default version available is MySQL 8.0.27.

Update your package index:

sudo apt update

Install MySQL server:

sudo apt install mysql-server

Start the MySQL service:

sudo systemctl start mysql.service

Step 2 — Configuring MySQL

After installing MySQL, run its security script to set a password and make other configurations.

Note: An error can occur if the root MySQL account isn’t configured to connect with a password.

Open the MySQL prompt:

sudo mysql

Change the root user’s authentication method:

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

Exit the MySQL prompt:

exit

Run the security script:

sudo mysql_secure_installation

Follow the prompts to configure security options, such as setting up the Validate Password Plugin and setting a root password.

After running the script, you can switch the root user’s authentication method back to the default auth_socket if needed:

Log in with:

mysql -u root -p

Change the authentication method:

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

Exit the MySQL prompt:

exit

Step 3 — Creating a MySQL User and Granting Privileges

Create a new MySQL user with limited privileges for regular operations.

Open the MySQL prompt:

sudo mysql

Create a new user:

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

Grant privileges to the new user:

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

Apply the changes:

FLUSH PRIVILEGES;

Exit the MySQL prompt:

exit

Step 4 — Testing MySQL

Check if MySQL is running:

Check the status:

systemctl status mysql.service

If MySQL isn’t running, start it with:

sudo systemctl start mysql

Connect to the database using mysqladmin:

sudo mysqladmin -p -u username version

This verifies MySQL is up and running.

Conclusion

You have successfully installed and configured MySQL on Ubuntu 20.04. You can now create and manage databases for your projects.