How To Install MySQL On Ubuntu 20.04

A Comprehensive Guide On How To Install MySQL On Ubuntu 20.04

MySQL is a freely available open source Relational Database Management System(RDBMS).

The SQL in MySQL is the query language. A query language is used to manipulate the data stored in the database.

MySQL

To use MySQL within your installed Ubuntu 20.04 system, we have to install the server and configure it.

In this article I will take you through the process.

Step 1 : Update the system and install the server

sudo apt update && sudo apt install mysql-server

Step 2 : Run the security script

sudo mysql_secure_installation

The script sets up a secure configuration for your MySQL server.

You set up a password, delete the test database and disallow remote login to your server.

Step 3 : Create a user

From the bash enter the following command to log in as the root user.

sudo mysql

This takes you to the shell of the MySQL server, type in the following to create a new user.

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

This creates a user with the caching_2 authentication policy. However there are known issues with the caching_2 authentication policy especially if working with PHP.

Alter the user to use the mysql_native_password policy with the command below:

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';

The user we have created does not have any privileges on the server. We want to grant all privileges to the user so that they can also act as the root user.

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

The ‘WITH GRANT OPTION’ means that the user can also grant the privileges to any other user in the server.

Once we grant the privileges to the user we can refresh the privileges with the command below, still within the MySQL server.

FLUSH PRIVILEGES;

We are done setting up our user, we can now exit. Within the MySQL server type:

exit

We are back to the bash now, we can try login our new user with the authentication credentials we provided for them. In the bash type the following:

mysql -u username -p

This pops up a password input, type in the password you set for the new user.

If the credentials match you’re routed to the MySQL server, if they are wrong you get the error message below:

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

We are done setting up the MySQL server.

Assignment : Logged in as the user you created, change the root user’s password and lets discuss it in the comments section.