How to install and configure MySQL

How to install and setup a MySQL server? This tutorial will teach you how to make a new database, create users and grand them permissions.

Prerequisites

For this tutorial, you will need:

• An active Fuga Cloud account. 

Step 1 - Install MySQL

First update and upgrade your server:

$ sudo apt update && sudo apt upgrade -y 

Install the MySQL server package:

$ sudo apt install mysql-server

Make the server running using:

$ sudo systemctl start mysql.service

After following these steps, you have installed and started the MySQL server. In the following steps you will  configure the MySQL server.

Step 2 - Configure MySQL

Now you have installed MySQL, it's time to configure and secure the server. Run the secure script: 

$ sudo mysql_secure_installation

You will get the following output to confirm a secure password: 

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: 

Type Y to confirm a new password. Next you can select one of the three levels of password validation:

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:

After selection, you will get the following output to enter the password: 

Please set the password for root here.

New password:

Re-enter new password:

After entering your password, you will receive feedback on the strength of your new password. The script will ask if you want to continue with the password you just entered, or if you want to enter a new password. Confirm your new password with Y:

Estimated strength of the password: 100

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

From here you can press Y and ENTER to accept the defaults for all questions. This will remove some anonymous users and the test database, disable remote root logins, and load new rules to the MySQL server. Once the script is complete, your MySQL installation will be secure.

Step 3 - create a MySQL database

After installation, MySQL creates a root user account which manages the database. This user has full control over the MySQL server and its databases. Go to your MySQL console:

$ sudo -u mysql -p

Once you are in the MySQL console, you are ready to create a new database: 

> create database fuga_database; 

You can view all databases by using the following command:

> show databases;
+--------------------+
| Database           |
+--------------------+
| fuga_database      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

5 rows in set (0.00 sec)

Step 4 - Add users and grant permissions

Once you have created a new database, it's time to create a new user and granting permissions to that user. 

To create a new user, use the following command in your MySQL console:

> create user 'fuga'@'localhost' identified by 'password';

To get an overview of all the MySQL users, use the following command;

> select user from mysql.user;
+------------------+
| user             |
+------------------+
| debian-sys-maint |
| fuga             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)

Now the user is created, you want to give that user permission to access the database you have created earlier:

> grant all on fuga_database.* to 'fuga'@'localhost';

After that, refresh your MySQL by using the following command: 

> flush privileges;

Step 5 - login with new user

Exit MySQL, since you are logged in as root:

> exit

Log back in with user fuga (the user you have created earlier):

$ mysql -u fuga -p

Now that you are connected as user fuga, you can use the following command to get an overview of the database you have created earlier as root:

> show databases; 
+--------------------+
| Database           |
+--------------------+
| fuga_database      |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Conclusion

In this tutorial you have learned how to setup a MySQL server. You have also learned how to create a new database with a new user.