How to set up MySQL on Ubuntu 20.04 server

MySQL is a widely used relational database management system (RDBMS).

It is free and open-source. MySQL is ideal for both small and large applications

In this guide, we will go over how to set up MySQL on Ubuntu 20.04 server.

Prerequisites:

A server with ubuntu installed.

Step 1: Installing MySQL

You can install MySQL using the apt package repository.

You can update the package index of your server by typing:

$ sudo apt update

Then install the mysql-server package by typing:

$ sudo apt install mysql-server

This will install MySQL but will not ask you to set a password or make any configuration changes.

To make MySQL secure you will have to run a security script with sudo:

$ sudo mysql_secure_installation

This script will prompt you with some boolean choices and according to your responses, MySQL will make necessary changes to your installation’s security options.

Once the script completes, your MySQL installation will be secured.

Step 2: Creating a new MySQL user and Granting Privileges

MySQL installation will by default create 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 the server. Because of this, it is best to avoid using this root user account.

You can create a new user and grant required permissions to it.

To login into MySQL prompt you can type:

$ sudo mysql -u root -p

This command will ask for the password of the root user.

You will have to enter the password that you set up during the mysql_secure_installation script.

Once you are logged in, you can create a new user with CREATE USER statement.

Syntax: CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

The host is 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.

mysql> CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpass@123';

This will create a user with the username test_user and have the password testpass@123.

After creating your new user, you can grant them the appropriate privileges.

Syntax: GRANT PRIVILEGE ON database.table TO ‘username’@’host’;

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION

This will grant test_user CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT

Privileges on all tables in all databases with the ability to grant privileges to other users.

You can also grant all privileges by typing:

mysql>GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION;

However, this is not advisable.

You can exit the MySQL client by typing:

mysql> exit

To log in as new test_user you can type:

$ mysql -u test_user -p

MySQL client will prompt you for password and then you will be logged in.

Step 3: Testing MySQL

MySQL should have started running automatically after installation. To test this we can check its status by typing:

$ systemctl status mysql.service

If MySQL isn’t running, you can start it manually with the command:

$ sudo systemctl start mysql

Conclusion:

You now have a basic MySQL setup installed on your ubuntu server.

If you’d like to learn how to set up PostgreSQL in ubuntu 20.04, we encourage you to check out the following guide:

How to set up Postgres on an Ubuntu 20.04 server