Install MySQL In Ubuntu 20.04


04th October 2020 3 mins read
Share On        


In this article, let's see how we can install MySQL in Ubuntu 20.04 and manage it.


Prerequisites


Before moving ahead with this article I request you guys to setup up Ubuntu server in Digital OceanLinode or any other cloud platform. If you don't have an account then click on the link and get the free credits to play around :)


If your still using password logins or root SSH logins. Then I highly recommend you to check on How To Do Basic Server Security Setup For Ubuntu / Linux

Login to you server and follow along with the following steps.


Install MYSQL database & secure with password


MYSQL is a relational database which is used to store the dynamic data and fetch the data which will come from login, register and various other types of forms


sudo apt-get udpate
sudo apt-get install mysql-server


Now the MYSQL database is installed. As earlier now MYSQL wont prompt for password. So to securely install MYSQL server use the following commands


sudo mysql_secure_installation

You might be prompted with VALIDATE PASSWORD PLUGIN please select NO as per my experiences. It will create a lot of fuss for the passwords at later point.

By chance if you select YES option then you will get the following prompt


There are three levels of password validation policy. Select difficulty based on your requirement.


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: 


Next add the root user password and confirm the password.


New password:

Re-enter new password:


For best security practices its good idea to press YES for the subsequent prompts. This prompts include remove anonymous users, remove test database, disable remote root logins and flush privileges to save changes into affect.


By default you will be root user with auth_socket enabled connection. Which means while connecting to database no need of any passwords. At some point later this might cause security issues. So its better to add the password authentication for all requesting connections.


Let me show you the same. First lets login to MYSQL


sudo mysql


Next run the following to see the details of the root user


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


Once you run the above command you can see that root user doesn't have the authentication_string ie password.


Output
+------------------+-------------------------------------------+-----------------------+-----------+
| user       | authentication_string           | plugin        | host   |
+------------------+-------------------------------------------+-----------------------+-----------+
| root       |                      | auth_socket      | localhost |


To set the password run the following command, make sure to change the your_password with valid required password


ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';


Run the following command so that the changes make will take an affect.


FLUSH PRIVILEGES;


Now again if you re-run the above command to check if the password is set or not then it will set with mysql_native_password. No worries if your not able to see the authentication_string as it will be encrypted by MYSQL 


Output
+------------------+-------------------------------------------+-----------------------+-----------+
| user       | authentication_string           | plugin        | host   |
+------------------+-------------------------------------------+-----------------------+-----------+
| root       | *549966GJ45794839F92C1571D6D78F | mysql_native_password | localhost |


Now you can safely exit from MYSQL prompt. If you try to login as sudo mysql then it wont allow. You need to add your_password to do so.


Conclusion


Now you have installed MySQL, if you would like to access the it very securely then we can do it the following way.


Securely Connect Server MYSQL DB From Sequel Pro / MYSQL Workbench





AUTHOR

Channaveer Hakari

I am a full-stack developer working at WifiDabba India Pvt Ltd. I started this blog so that I can share my knowledge and enhance my skills with constant learning.

Never stop learning. If you stop learning, you stop growing