F. Install MySQL

A. Installing MySQL

I'm not going to bore you with all the tedious details but please remember that mysql is an open-source database management system.

a. Update your Linux distribution package index if you’ve not done so recently:

$ sudo apt update

b. Install the mysql-server package:

$ sudo apt install mysql-server

B. Configure MySQL

• First command must be ran with sudo and it will take us through a series of prompts, this is the express and basic configuration

  $ sudo mysql_secure_installation

• Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

• The script will ask you to choose a password validation level, with the weakest being 0 and the strongest being 2:

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

• The next prompt will be to enter and confirm a password for the MySQL root user *:

Output

Please set the password for root here.

New password:

Re-enter new password:

*you will not see any characters when typing so be very careful!

• The script will ask if you want to continue with the current password you just entered or if you want to enter a new one. I chose Y (no use going through that ordeal again) and then ENTER to accept the defaults for all the subsequent questions.

C. Creating a new User and Granting Privileges

When we installed MySQL we created a root user (which has the highest privileges of them all).

The root can be used to manage the database but it is advised not to, that is why we will create a nominal user for which we will grant privileges.

• Connect to the database:

  $ sudo mysql

  mysql> CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

We will use mysql_native_password because there is a well known problem with PHP and caching_sha2_password authentication plugin.

• Granting privileges

  mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT on . TO 'username'@'localhost' WITH GRANT OPTION;

!! Do not grant ALL PRIVILEGES as it will make username as powerful as the root user, and we definitely do not want that.

• Last but not least:

  mysql> FLUSH PRIVILEGES;

To exit:

mysql> exit

D. Connecting with our dummy account

-u stands for user

-p stands for password <- after we run the below command it will be prompt you for the password

$ mysql -u username -p

---

                                            **Congrats, you're done!**

---

Conclusion

We have learned about the MySQL and finished implementing it.

We have also learned how to create users, give permissions and connecting to our database by CLI.

If you hit a problem or have feedback (which is highly welcomed) please feel free to get in touch, more details in the footer.