Tutorial installing MySQL on Ubuntu 20

As you know, MySQL is an open-source database management system. In this article, let’s know more about the tutorial installing MySQL on Ubuntu 20.04. Which is installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack

Tutorial installing MySQL on Ubuntu 20.04

1- Installing MySQL

As we start most of the installations, first update your server’s package index.

sudo apt update  

Install the mysql-server package:

sudo apt install mysql-server

Buy Linux Virtual Private Server

2- Configuring MySQL

To run the security script of MySQL with sudo:

sudo mysql_secure_installation  

Then, you may face some prompts. the first is if you would like to set up the Validate Password Plugin. To do that, the script will ask you to choose a password validation level, with the weakest being 0 and the strongest being 2:

Output

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: Y    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:   2    

You should enter and confirm a password for the MySQL root user for the next prompt.

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) : Y

To accept the defaults for all the subsequent questions, you can press Y and then ENTER.

3- Adjusting User Authentication and Privileges

To connect to MySQL as root with a password, you need to switch the authentication method from the default authentication plugin auth_socket to another one like caching_sha2_password or mysql_native_password. You can open the MySQL prompt from your terminal:

sudo mysql  

And also to change which authentication plugin it uses and set a new password, run an ALTER USER. You should choose a new and strong password

This command will change the root password you set in Step 2:

mysql>  ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';  

To tell the server to reload the grant tables and put your new changes into effect, you can run FLUSH PRIVILEGES

MySQL> FLUSH PRIVILEGES;

Now you need to check the authentication methods employed by each of your users again to confirm that root no longer authenticates. To do this use the auth_socket plugin:

MySQL> SELECT user,authentication_string,plugin,host FROM mysql.user;  

+------------------+------------------------------------------------------------------------+-----------------------+-----------+  | user             | authentication_string                                                  | plugin                | host      |  +------------------+------------------------------------------------------------------------+-----------------------+-----------+  | debian-sys-maint | $A$005$lS|M#3K #XslZ.xXUq.crEqTjMvhgOIX7B/zki5DeLA3JB9nh0KwENtwQ4 | caching_sha2_password | localhost |  | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |  | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |  | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |  | root             | *3636DACC8616D997782ADD0839F92C1571D6D78F                              | caching_sha2_password | localhost |  +------------------+------------------------------------------------------------------------+-----------------------+-----------+  5 rows in set (0.00 sec)

After confirming this on your server, you will be able to exit the MySQL shell:

MySQL> exit

Create a new user and give it a strong password:

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

Now, it’s time to grant your new user the appropriate privileges. Run the following command to add, change, and remove user privileges.

MySQL> GRANT ALL PRIVILEGES ON *.* TO 'noodi'@'localhost' WITH GRANT OPTION;  

To exit the MySQL shell:

MySQL> exit

You have a basic MySQL setup installed on your server till here.

Dear user, we wish this tutorial installing MySQL on Ubuntu 20 would be helpful for you, to ask any question or review the conversation of our users about this article, please visit Ask page. Also to improve your knowledge, there are so many useful tutorials ready for Eldernode training.