How to Install MySQL on Ubuntu 20.04
Introduction
MySQL is an open-source database management system, commonly installed as part of the popular LEMP (Linux, Nginx, MySQL, PHP) stack. It implements the relational model and uses Structured Query Language to manage its data. This tutorial will go over how to install MySQL version 8.0 on an Ubuntu 20.04 server. By completing it, you will have a working relational database that you can use to build your next website or application.
Prerequisites
To follow this tutorial, you will need:
- One Ubuntu 20.04 server with a non-root administrative user and a firewall configured with UFW.
1 Installing MySQL
On Ubuntu 20.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.29.
To install it, update the package index on your server if you’ve not done so recently:
$ sudo apt update
Then install the mysql-server package:
$ sudo apt install mysql-server
Ensure that the server is running using the systemctl start command:
$ sudo systemctl start mysql.service
These commands will install and start MySQL but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.
2 Configuring MySQL
For fresh installations of MySQL, you’ll want to run the DBMS’s included security script. This script changes some of the less secure default options for things like remote root logins and sample users.
Run the security script with sudo:
$ sudo mysql_secure_installation
This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options.
3 Creating a Dedicated MySQL User and Granting Privileges
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges.
In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command, so you must invoke MySQL with sudo privileges to gain access to the root MySQL user:
$ sudo mysql
Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. These follow this general syntax:
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
After CREATE USER, you specify a username. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify localhost.
After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
It'll grant their MySQL user 'leric' the ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as necessary.
Run this GRANT statement, replacing 'leric' with your own MySQL user’s name, to grant some privileges to your user:
mysql> GRANT CREATE on dbname.* TO 'leric'@'localhost' WITH GRANT OPTION;
Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system.
Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
mysql> FLUSH PRIVILEGES;
Then you can exit the MySQL client:
mysql> exit
In the future, to log in as your new MySQL user, you’d use a command like the following:
$ mysql -u leric -p
The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.
Finally, let’s test the MySQL installation.
4 Testing MySQL
Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.
$ systemctl status mysql.service
You’ll see output similar to the following:
This means MySQL is up and running. If MySQL isn’t running, you can start it with
sudo systemctl start mysql
Conclusion
In this guide, you installed the MySQL relational database management system and secured it using the mysql_secure_installation script that it came installed with. You also had the option to create a new user that uses password authentication before testing the MySQL server’s functionality.