How to Install MySQL on Ubuntu

Published:26 April 2022 - 9 min. read

Nicholas Xuan Nguyen Image

Nicholas Xuan Nguyen

Read more tutorials by Nicholas Xuan Nguyen!

You’re the system-slash-database admin and need to build a database management system (DBMS) for a new project. If you choose to install MySQL on Ubuntu, then you’re in luck. MySQL is fast, reliable, versatile, and free — a good choice!

This tutorial will walk you through installing a MySQL server on Ubuntu, make it secure, and create a dedicated user with limited permissions to avoid exposing the root account. By the end, you’ll have built a MySQL server that you can use in your project to host databases!

Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have an Ubuntu Server 20.04 or later with a non-root user account with sudo privileges.

Installing MySQL from the Ubuntu Repository

The Ubuntu APT package repository includes the MySQL package by default, making it convenient to install MySQL Ubuntu in a single command.

NOTE: The default Ubuntu APT repository may not always have the latest MySQL version.

To install MySQL Ubuntu, proceed as follows.

1. Open your preferred SSH client and log in to your Ubuntu server.

2. Ensure that your package list is recent by running the below command. This command updates the list of available packages in the APT package repository.

sudo apt update -y

3. Now, install the mysql-server package by executing the following command. Wait for the installation to finish.

sudo apt install mysql-server -y

As you can see below, the latest MySQL version from the APT repository is 8.0.28.

Install MySQL Ubuntu package from the default APT repository
Install MySQL Ubuntu package from the default APT repository

4. Lastly, run the below command to check and confirm whether the mysql service is now running.

sudo systemctl status mysql
Verifying mysql service status
Verifying mysql service status

Installing MySQL from the Official Repository

If you don’t like waiting for your Linux distro repository to catch up, downloading and installing the DEB package is the best way to get the latest features and bug fixes.

The MySQL developers provide their software repository to install the latest version of MySQL, but Ubuntu doesn’t know where to find that repository (yet). Follow the below steps to download and install MySQL from the official repository.

1. Open the web browser on your computer and navigate to the MySQL APT Repository page.

2. Once on the download page, click the Download button, as you can see below.

Clicking the download link
Clicking the download link

3. On the next page, right-click the No thanks, just start my download link and click Copy link address.

Copying the MySQL download link
Copying the MySQL download link

4. Go back to your SSH terminal session and download the MySQL package to the /tmp directory home directory by running the curl command below. Make sure to change the URL value after the -OL parameter with the URL you copied in the previous step.

cd /tmp && curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
Downloading the MySQL DEB package
Downloading the MySQL DEB package

5. After downloading the package, run the dpkg command below to install the MySQL package file.

sudo dpkg -i mysql-apt-config*

6. On the configuration page, that shows up asking Which MySQL product do you wish to configure? – Press the down arrow until you select Ok, and press Enter.

Selecting the MySQL product to configure
Selecting the MySQL product to configure

7. Now, run the below command to update your package list so that APT can find the new MySQL packages.

sudo apt update -y

8. Now that APT knows where to find the latest MySQL version, run the below command to install the mysql-server package.

sudo apt update -y

9. Type in the new strong password for the MySQL database root account on the configuration prompt and press Enter.

Creating the MySQL root password
Creating the MySQL root password

Re-enter the password and press Enter to confirm.

Confirming the new password
Confirming the new password

10. Select Use Strong Password Encryption (RECOMMENDED), and press Enter on the default authentication plugin selection.

Selecting the default authentication method
Selecting the default authentication method

Wait for the installation to complete. The screenshot below shows the MySQL server version you installed. In this example, the latest version is 8.0.28.

Install MySQL Ubuntu package from the official repository
Install MySQL Ubuntu package from the official repository

11. Run the systemctl command below to verify that the MySQL service is running.

sudo systemctl status mysql

You will see the following output. This output shows that the service is active (running) and enabled.

Verifying MySQL service status
Verifying MySQL service status

Securing Your MySQL Server

The MySQL server default installation is not secured out-of-the-box, and you need to perform some additional steps to secure your MySQL server. Luckily, the MySQL developers have created a configuration script that does most of the work for you.

The security script is called mysql_secure_installation, which you can find under the /usr/bin directory. The script modifies less safe defaults, such as remote root logins and sample users.

1. Run the following command below to start the security script on the terminal. This script will ask you several Yes/No questions to configure the MySQL server security.

sudo mysql_secure_installation

If you installed MySQL from the default Ubuntu repository, you’d see the following message saying that you’re connecting to MySQL with a blank password! Don’t worry; you’ll be fixing that issue in the succeeding steps.

Connecting to MySQL without a password.
Connecting to MySQL without a password.

2. At the next prompt asking to set up the VALIDATE PASSWORD COMPONENT, type y and press Enter. This component validates whether MySQL user passwords comply with the complexity requirement.

Setting up password validation
Setting up password validation

3. Next, select the password validation policy level to implement. The options are:

  • LOW – requires a password with eight characters minimum.
  • MEDIUM – requires a password with eight or more characters, including numbers, mixed case, and special characters.
  • STRONG – same as MEDIUM but checks the password against a dictionary file for validation.

In this example, type 2 to select STRONG and press Enter.

Selecting the password validation policy level
Selecting the password validation policy level

MySQL does not have a default dictionary file. As such, you’ll need to create a dictionary file and modify the validate_password_dictionary_file system variable value to the dictionary file path. Creating and configuring the password dictionary file is not in the scope of this tutorial.

4. To ensure that the root password complies with the password complexity requirements, set the root password again and re-enter. The script will then show you the password strength score. Press y to confirm saving the password and press Enter.

Setting the strong root password
Setting the strong root password

5. Next, the script asks if it should remove the anonymous user that the MySQL installation created. This user is safe to remove, so type y and press Enter to remove the anonymous user.

Deleting the MySQL anonymous user
Deleting the MySQL anonymous user

6. To further lock down MySQL, type y and press Enter to disallow remote login for root. After this step, the root account can only log in to MySQL from the server and not from remote clients.

Disabling remote root login
Disabling remote root login

7. Next, remove the test database. This database is only for testing and should not go into production. To remove (drop) the test database, type y, and press Enter.

Removing the test database
Removing the test database

8. Finally, reload the privilege tables for all the changes to take effect immediately. Type y and press Enter to reload.

Reloading the MySQL privilege table
Reloading the MySQL privilege table

You have now successfully secured your MySQL server.

Creating a New MySQL User and Granting Limited Permissions

At this point, there’s only one user account in your MySQL server, which is the root user. As the name implies, the root account has full access to the MySQL server and every resource within.

Constantly using the root user account is not a best practice and can be a security risk. You should reserve the root account for administrative tasks that need its permissions. Otherwise, you should be using an account with limited privileges, and below are the instructions for creating one.

1. Log in to MySQL as the root user on the terminal by executing the below command.

sudo mysql
Logging in to the MySQL prompt
Logging in to the MySQL prompt

2. Next, run the CREATE USER statement to create a new user. The below example creates a new user account named ata which can only log in from the localhost. Make sure to provide a strong password that complies with the password policy you configured earlier.

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

Suppose you create a user who can log in from a specific remote computer, run the below command instead. This command replaces localhost with the remote computer’s ip_address.

CREATE USER 'ata'@'ip_address' IDENTIFIED BY 'password';

To create a user that can log in to MySQL from anywhere, replace the localhost or ip_address with % instead.

CREATE USER 'ata'@'%' IDENTIFIED BY 'password';

Always end your MySQL statements with a semi-colon (;). Also, enclosing the values in single quotes (‘) helps prevent SQL syntax errors.

The result below shows the successful user creation

Creating a new MySQL user account
Creating a new MySQL user account

3. Now that you’ve created the user run the GRANT statement next to grant permissions. There are many MySQL privileges that you can assign to the user. But in this example, the command will grant the following privileges to the user 'ata'@'localhost’ to all (*.*) databases and tables.

  • ALTER – gives the user permission to alter the structures of tables.
  • SELECT – gives the user access to the SELECT statement, which allows reading the database contents.
  • CREATE – gives the user permission to create databases and tables.
  • INSERT – allows the user to insert rows into the table.
  • DELETE – allows the user to delete rows from the table.
  • UPDATE – the user can update rows on the table.
  • REFERENCES – enable foreign key creation.
  • RELOAD – the user can run the FLUSH statement and commands.
GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO ‘ata'@'localhost’;

Refer to the Privileges Provided by MySQL documentation for a complete list of all privileges. You may add, remove, combine permissions as you wish.

The result below shows granting permissions was successful.

Granting limited MySQL permissions to the new user
Granting limited MySQL permissions to the new user

4. Next, run the below command to reload the privilege tables. Doing so will ensure that all the changes you have made so far immediately take effect.

FLUSH PRIVILEGES;

5. Lastly, log out from MySQL.

exit

Testing Your MySQL Server

So you’ve installed and secured your MySQL server and configured a new limited user account. Now’s the time to test whether everything works as you expected.

1. Log in to MySQL as the ata user. This way, you can test whether this limited user account works as planned. The -u flag specifies the account to log in, while the -p flag invokes the password prompt.

If you do not append the -p flag, the mysql account will try to log in without a password — which will fail since the login account has a password.

mysql -u ata -p
Logging in to MySQL as the new user account
Logging in to MySQL as the new user account

2. Next, test the user privilege by running the below CREATE statement to create a new database named ata_db.

CREATE DATABASE ata_db;

You should see the Query OK message, indicating a successful database creation.

Creating a new database
Creating a new database

3. Run the show databases; statement to see a list of all the databases that are available to use.

show databases;

You should see the ata_db database you created earlier, as shown below.

Listing all MySQL databases
Listing all MySQL databases

Conclusion

In this article, you have learned how to install the MySQL Ubuntu server and configure the server security settings. You also learned how to create a new user account with limited privileges and test the new user’s permissions by creating a new database.

At this point, you should have a fully functional MySQL Ubuntu server that you can use for your development and production needs. And if you prefer managing MySQL with a GUI, perhaps you could install a web front-end next?

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks

Looks like you're offline!