How to Install and Configure MySQL on Ubuntu 20.04

MySQL is the most popular open-source remote database management system (RMDBS). It’s reliable, performant, mature, and has widespread support and adoption. From LAMP stack to Online Transaction Processing (OLTP) databases, a varfiety of mission-critical applications depend on MySQL.

Here, to help you hit the ground running with this popular RMDBS, we’ll walk you through a step-by-step tutorial on how to install and configure MySQL on Ubuntu 20.04. At the end of the tutorial you’ll have MySQL server running on Ubuntu 20.04 configured with:

• A STRONG password validation policy
• Remote root login disallowed
• Anonymous login and the test database disabled
• A dedicated MySQL user that uses the caching_sha2_password authenitcation mechanism and has ALTER, CREATE, DELETE, DROP, INSERT, REFERENCES, RELOAD, SELECT, and UPDATE privileges on all tables and databases

Then, we’ll provide a cheat sheet of tips to help you work with MySQL on Ubuntu 20.04.

If you just want to spin-up MySQL and go, check out the Tl;dr version. If you want all the details proceed to the Install MySQL on Ubuntu 20.04 tutorial.

#Tl;dr: a Fast Way to Install MySQL Server on Ubuntu 20.04

We don’t like it when tutorials stretch things out longer than they need to. So, while we recommend you read the full tutorial and tips below, we’ll give you the Tl;dr version too.

If you just want to get MySQL installed on Ubuntu 20.04 fast for testing or non-production environments, here are the steps (replace yourmysqluser with your username and password with your password):

1. Login to your Ubuntu 20.04 machine with a user that has sudo privileges and run this command to update your repositories: sudo apt-get update -y
2. Install MySQL with this command: sudo apt install mysql-server -y
3. Run the script to harden your MySQL install with this command: sudo mysql_secure_installation
4. Respond to the prompts to harden your install as desired
5. Launch MySQL and then create a non-root user with your desired set of MySQL privileges (our example privilege set is used in command c) using these commands:
   1. sudo mysql
   2. CREATE USER 'yourmysqluser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
   3. GRANT ALTER, CREATE, DELETE, DROP, INSERT, REFERENCES, RELOAD, SELECT, UPDATE ON *.* TO 'yourmysqluser'@'localhost' WITH GRANT OPTION;
   4. exit
6. Make sure you can login to MySQL with your newly created user using this command: mysql -u yourmysqluser -p

You should now be at a MySQL prompt and can proceed using MySQL on your Ubuntu 20.04 machine.

#Install and Configure MySQL on Ubuntu 20.04

In this detailed tutorial, we’ll explain each step as we go so you have context around what you’re doing. We’re focused on installing and configuring a standard MySQL server on Ubuntu 20.04 that should serve as a useful starting point for many general-purpose applications.

#Prerequisites

Before we get started, you’ll need:

• Ubuntu 20.04 machine with Internet access (to access the Ubuntu repositories)
• User with sudo privileges

#Step 1: Upgrade Your Ubuntu Repositories

MySQL is available in the default repositories for Ubuntu 20.04. To make sure you download the latest version in the next step, update your repositories with this command:

sudo apt install mysql-server -y

You’ll notice we appended -y to the end of the command. That simply makes apt - Ubuntu’s package manager - accept prompts and run non-interactively. If you’d prefer to run the command interactively and respond to the prompts, omit the -y.

#Step 2: Install MySQL

Next, well use apt to install the latest version of MySQL server with this command:

sudo apt install mysql-server -y

Congrats! Once that command completes, you’ll have MySQL installed on Ubuntu 20.04, You can validate that MySQL is installed with the mysqld --version command. Here’s an example of what the expected output should look like:

pepperandegg@humorous-wombat:~$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

#Step 3: Harden Your MySQL Install With mysql_secure_installation

Now it’s time to configure your MySQL installation. We’ll start with hardening the install. Fortunately, MySQL makes basic hardening straightforward with an interactive mysql_secure_installation script.

1. To begin, run the command:

sudo mysql_secure_installation

2. That will launch an interactive prompt. The first question is Would you like to setup VALIDATE PASSWORD component? Enter y as your response.

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

3. Next, you’ll be prompted to select a password validation policy level. Possible responses are:
   • LOW (0) passwords must at least 8 characters
   • MEDIUM (1) passwords must be at least 8 characters and contain at least 1 lowercase character, 1 uppercase character, and 1 special character
   • STRONG (2) all the requirements of MEDIUM plus passwords must NOT match words in the validate_password_dictionary_file

Enter 2 to select the STRONG password validation policy.

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

4. Next, you’ll be prompted to enter a New password. This password is for the MySQL root user. Enter a strong password here and confirm at the Re-enter new password. If the password meets the requirements for the STRONG password policy, you’ll see an estimated strength of 100 like in the example below.

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100

5. Now, you’ll be prompted: Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No). Enter y to continue and use the password you configured as the root password.

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

6. By default, MySQL has an anonymous user, in the next Remove anonymous users? (Press y|Y for Yes, any other key for No): respond with a y to remove it.

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

7. Next, disallow remote root login by entering y at the Disallow root login remotely? (Press y|Y for Yes, any other key for No) prompt:

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

8. Remove the default test database by entering y at the Remove test database and access to it? (Press y|Y for Yes, any other key for No) prompt:

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

9. Finally, to ensure that our changes take effect immediately, enter y at the Reload privilege tables now? (Press y|Y for Yes, any other key for No) prompt. After that, you’ll return to the standard Ubuntu prompt for your user.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
pepperandegg@humorous-wombat:~$

#Step 4: Create a dedicated MySQL user

While you could use the root user to access MySQL for everything, it’s better from a security perspective to create a MySQL user that only has the privileges required for a given use case. In this step, we’ll use an example set of privileges that should work for many use cases, but feel free to modify it to meet your needs.

1. Launch MySQL with the command

sudo mysql

💡 Pro-tip: If you modified your MySQL install not to use the auth_socket plugin or don’t have sudo privileges, you can authenticate as root using the password from step 3 with the command: mysql -u root -p

2. Create a MySQL user with this command (replace pepperandegg with your username and password with your password):

CREATE USER 'pepperandegg'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

📝 Important notes on authentication mechanisms: In our example here, we used MySQL’s recommended caching_sha2_password authentication mechanism. However, there are some cases where you may want to use a different authentication mechanism.

For example, many PHP versions aren’t compatible with caching_sha2_password. In those cases, as indicated in this StackOverflow answer and this Laradock GitHub issue, the solution is to use mysql_native_password. To create a user that uses mysql_native_password and has the same permissions as our example user here, use this command (replace php_pepperandegg with your username and password with your password):

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

Alternatively, if you’re using MySQL source-replica data replication, you may need to use the sha256_password authentication mechanism. To create a user using the sha256_password authentication mechanism, run this command (replace sha256nocache_pepperandegg with your username and password with your password):

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

3. Grant your MySQL user privileges. You can view a complete list of privileges in the MySQL docs. For our example, we’ll grant our user ALTER, CREATE, DELETE, DROP, INSERT, REFERENCES, RELOAD, SELECT, and UPDATE privileges on all tables and databases (.) with this command (replace pepperandegg with your username):

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

4. Now, you can exit the MySQL prompt with the exit command to return to your Ubuntu user prompt.

mysql> exit
Bye
pepperandegg@humorous-wombat:~$

#Step 5: Validate your MySQL Install and Test Basic MySQL Commands

Now that MySQL is installed and configured, you can use it as-is. However, for this tutorial, let’s validate the install by performing these basic tasks:

• Login to MySQL with our newly created user
• Create a breakfast database with a food table that has sandwich and description fields
• Write to the food table
• Read from the food table
• Delete the breafast database
• Exit MySQL

1. First, we’ll login with this command (replace pepperandegg with your username and enter your password at the prompt)

mysql -u pepperandegg -p

2. Create a new table named breakfast with this command:

CREATE DATABASE breakfast;

3. Next, we’ll create the food table in the breakfast database. We’ll add sandwich and description columns to the database that use the varchar data type with this command:

CREATE TABLE breakfast.food (sandwich VARCHAR(20), description VARCHAR(255));

4. Now, let’s create a new row in the database with an INSERT INTO SQL statement:

INSERT INTO breakfast.food(sandwich, description) VALUES ('Pepper and Egg', 'The absolute best sandwich ever');

5. View all the rows in our table with this command:

SELECT * FROM breakfast.food;

The output should look like this:

mysql> SELECT * FROM breakfast.food;
+----------------+---------------------------------+
| sandwich       | description                     |
+----------------+---------------------------------+
| Pepper and Egg | The absolute best sandwich ever |
+----------------+---------------------------------+
1 row in set (0.00 sec)

mysql>

6. Now that we’re done with our tests, let’s delete our breakfast database with this command:

DROP database breakfast;

7. Finally, let’s exit MySQL and drop back to our Ubuntu command prompt with the exit command:

mysql> exit
Bye
pepperandegg@humorous-wombat:~$

#MySQL Ubuntu Command Cheat Sheet

If you’re just getting started with MySQL and Ubuntu, you may spend a lot of time Googling (or DuckDuckGo-ing) basic commands. To help ease some of that pain, we’ve compiled a MySQL and Ubuntu command cheat sheet for you. Below are some common commands you may find useful. For simplicity’s sake, we’re assuming you’re running the commands on a local MySQL server you have root access to.

#Show all databases

SHOW DATABASES;

#Show all tables in a database

USE your_database_name;
SHOW TABLES;

#View all MySQL users

USE mysql;
SELECT user FROM user;

#View a MySQL user’s privileges

Replace username with the desired username:

SHOW GRANTS  FOR 'username'@'localhost';

#Delete a MySQL user

Replace username with the desired username.

DROP USER 'username'@'localhost';

#Final Thoughts: Build Something Great!

We hope you enjoyed our MySQL Ubuntu install and configuration tutorial! There are plenty of interesting use cases for MySQL and it can be an excellent RDBMS for your next project. Now that you know the basics, you can get started building something great.

Of course, there is plenty of nuance in designing secure and performant apps, so if you’d like to learn more, check out the official MySQL docs and subscribe to the Cherry Servers blog for more content like this!