Introduction

MySQL is the most popular open-source database. It is a relational database management system that is used worldwide for a variety of purposes. It is a combination of “My” which is the name of the daughter of the co-founder of MySQL and “SQL” i.e., Structured Query Language.

Here we will install and configure MySQL on an Ubuntu server.

MySQL Installation

Step 1. Install MySQL on Ubuntu.

 $ sudo apt update
 $ sudo apt-get install mysql-server 

This will just install MySQL on the server without prompting for any password for root. This makes the MySQL unsecure here.

Step 2. Configure MySQL

$ sudo mysql_secure_installation

This is the security script that we need to run after MySQL installation. Running this script will show some security prompts like installing a password plugin by asking the strength of the password asking to set a root password and ask some other questions that you can provide an answer to based on your requirements.

Note: Even though you have set the password for the root user of MySQL in this step. It’s not configured yet to authenticate with the password. For that follow the next step.

Step 3. Check the existing MySQL root user authentication and privileges Login in MySQL

$ sudo mysql

Check the authentication plugin used by root user.

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

From the output, it’s clear that root has no password configured yet and it’s using the auth_socket plugin for authentication.

Step 4. Set MySQL root user authentication and privileges

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

Replace your password with password above in query.

Then Flush privileges which will apply your changes.

mysql> FLUSH PRIVILEGES;

Now, Check again the authentication plugins.

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

root user has also a password and it is using mysql_native-password plugin.

mysql> exit;

Try again to login with earlier method.

$ sudo mysql

You will get his output:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Now you will login by this command:

$ mysql -u root -p

Enter the password and you will be logged in secure MySQL database.