Reset root password in MySQL 8

MySql

Reset MySQL Root Password

we have  to start the MySQL service with the --skip-grant-tables option. This is less secure as while the service is started, all users can connect without password.

If the server is started --skip-grant-tables, the option for --skip-networking is automatically activated so remote connections will not be available.

step 1: checking database current version

[root@lampblogs ~]# mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

step 2: stop service

# systemctl stop mysqld.service     # for distros using systemd (centos7)
# /etc/init.d/mysqld stop           # for distros using init (centos6, ubuntu14 etc)

step 3: start service

start the service with  below option.

# mysqld --skip-grant-tables --user=mysql &

Then we can connect mysql server 

# mysql

step 4: Flush privileges and change password:

once loggedin without password we will have to reload the grants

# FLUSH PRIVILEGES;

Run the following query to update the password. Make sure to change “newpassword” with the actual password.

# ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

[root@lampblogs ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Me40@B7ALjy%l';
mysql> quit
Bye

step5: stop and Reload service:

Now stop the MySQL server and start it normally.

# systemctl stop mysqld.service        # for distros using systemd 
# systemctl restart mysqld.service     # for distros using systemd 
# /etc/init.d/mysqld stop              # for distros using init
# /etc/init.d/mysqld restart           # for distros using init

If you get any error while starting mysql service then gracefully stop current mysql process

[root@lampblogs mysql]# ps aux | grep mysql
mysql     4008  1.3 20.2 1373744 381520 pts/0  Sl   16:15   0:06 mysqld --skip-grant-tables --user=mysql
root      4707  0.0  0.0 112708   980 pts/1    S+   16:23   0:00 grep --color=auto mysql
[root@lampblogs mysql]# kill -9 4008

Replace PID with your id while using kill. Then start mysql service

# systemctl start mysqld

step 6: Login with New password:

You should be able to connect with your new password.

# mysql -u root -p

[root@lampblogs ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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>