How to Install MySQL Database on Ubuntu 20.04

MySQL is an open-source relational database management system (RDBMS), it’s widely used and part of the popular LAMP/LEMP stacks. The data is organized in one or more tables in which the data types may be related to each other and MySQL uses SQL Structured Query Language to manage its data.

Considering its part of the LAMP/LEMP stack it is used by many database-driven web applications such as WordPress, Magento, Drupal, and Joomla.
Today we will install MySQL on our server and create a database and user with chosen permissions on this database, let’s get started!

Prerequisites

• For the purposes of this tutorial, we will use an Ubuntu20.04 VPS.
• Access to the root user account (or a user with sudo privileges)

Step 1: Log in to the Server & Update the Server OS Packages

As always, first log in to your VPS via SSH:

ssh user@hostname

Once you are in, run the following commands to update the package index and upgrade all the installed packages to the latest available version.

sudo apt-get update
sudo apt-get upgrade

Step 2: Installing MySQL

You can simply start the MySQL installation with the command:

sudo apt-get install mysql-server

To verify if the installation has been successful and if MySQL is running on your server,  you can execute the following command:

sudo systemctl status mysql

We will configure MySQL and enhance its security with the mysql_secure_installation script.

Step 3: Configuring MySQL

To run the security script, execute this command on your terminal:

sudo mysql_secure_installation

This will start the script which will prompt a series of questions to configure the MySQL passwords and security on that instance.

The first prompt is about the Validate Password Component, which we will install to ensure that all MySQL users follow the policy for complex passwords.

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

The next prompt will ask for the policy level that you would like to use. You can choose the level of policy you like to use, for the purpose of this tutorial we will choose option “1”. Which means the password must consist of numeric, mixed case, and special characters and the password length must be equal or greater than 8.

There are three levels of 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: 1
1

After choosing the policy level, you can now set your new root password:

Please set the password for root here.


New password: 

Re-enter new password:

Insert ‘Y’ in the following prompts in order to finish the installation:

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

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

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

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y

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

Step 4: Creating MySQL User and Database

After the installation MySQL creates root user for which we created the password in the previous step. The root user has full privileges over the MySQL server, it can access every database, table and create/drop or manage permission on the other MySQL users.

You can log in to your MySQL server with the following command using the password you created previously:

mysql -u root -p

Now you can create a MySQL user which in our case we will call ‘dev’, please don’t forget to change ‘StrongPassword‘ with your own strong password:

mysql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'StrongPassword';

Run the following command to create a database ‘development‘:

mysql> CREATE DATABASE development;

To grant all permissions on the new database ‘development‘ to the user ‘dev‘ use the following command:

mysql> GRANT ALL PRIVILEGES ON development.* TO 'dev'@'localhost';

The ‘*’ sign in the command is a wildcard for all the tables under the database ‘development’.

The command GRANT ALL will grant all privileges on the MySQL user ‘dev‘, but only for the connections made from the ‘localhost’, if you want to grant the same permissions for the user ‘dev’ for the remote connections also, you can use the following command:

mysql> CREATE USER 'dev'@'%' IDENTIFIED BY 'StrongPassword';

mysql> GRANT ALL PRIVILEGES ON development.* TO 'dev'@'%';

When you finish with setting up your MySQL permissions, make sure to reload all the privileges with:

mysql> FLUSH PRIVILEGES;

That’s it! The creation of MySQL database and user on Ubuntu 20.04 has been completed.

Of course, you don’t need to install MySQL and create databases on Ubuntu 20.04 yourself if you use one of our fully managed VPS Hosting services, in which case you can simply ask our expert Linux admins to install and secure it for you. They are available 24×7 and will take care of your request immediately.

P.S. If you liked this post on how to install MySQL and create a database on Ubuntu 20.04 please share it with your friends on the social networks by using the share shortcuts below, or simply leave a comment in the comments section. Thanks.