MySQL 5.7.x reset 'root' password

In this post I'll describe how can we set MySQL root password that was blank during the initial installation process. I'll describe end-to-end installation of MySQL database on a linux machine followed by resetting the root password.

Environment

OS	Ubuntu Linux 18.04
MySQL	5.7.x
JDK	1.8.x

A. MySQL Installation Process
1. First, update the apt package index by typing (with sudo privileges):
root@srv1:~# sudo apt update

2. Then install the MySQL package with the following command:
root@srv1:~# sudo apt install mysql-server

3. Once the installation is completed, the MySQL service will start automatically. To check whether the MySQL server is running, type:

root@srv1:~# sudo systemctl status mysql

● mysql.service - MySQL Community Server

   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

   Active: active (running) since Fri 2020-03-20 07:29:27 UTC; 17min ago

  Process: 1649 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCE

  Process: 1191 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

 Main PID: 1651 (mysqld)

    Tasks: 29 (limit: 19147)

   CGroup: /system.slice/mysql.service

           └─1651 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Mar 20 07:29:15 srv1 systemd[1]: Starting MySQL Community Server...

Mar 20 07:29:27 srv1 systemd[1]: Started MySQL Community Server.

4. Login as root (it will not ask for the password):
root@srv1:~# mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

B. Resetting 'root' user password

Stop the MySQL service

root@srv1:~# sudo systemctl stop mysql

Start MySQL Service

root@srv1:~# sudo systemctl start mysql

Set a new MySQL root password

mysql> use mysql;

mysql>update user set authentication_string=PASSWORD('NEW_PWD') where User='root';

mysql>flush privileges;

Here NEW_PWD refers to your new password

Update the authentication plugin

Previously, the plugin was 'auth_socket' for root user that should be changed to 'mysql_native_password' in order to allow password based authentication for root user, otherwise still login wouldn't require password-

mysql>UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND host = 'localhost';

mysql>flush privileges;

Stop and start the MySQL service

root@srv1:~# sudo systemctl stop mysql

root@srv1:~# sudo systemctl start mysql

Log in to the database
root@srv1:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

This completes the tutorial.