How to Reset the MySQL root account password on CentOS7?

Introduction

Forgetting the password is our biggest responsibility. If you forget or lose the root password of the MySQL or MariaDB database, you can still access and reset the password if you have access to the server and a user account with sudo enabled. This tutorial will show how to reset the root password for the old and new versions of MySQL and MariaDB.

Prerequisites

To recover your MySQL/MariaDB root password, you will need: Use the sudo user to access a Linux server running MySQL or MariaDB.

So to reset the root password, you still start mySQL with --skip-grant-tables options and update the user table, but how you do it has changed.

Stop MySQL Server

systemctl stop mysqld

Set the mySQL environment option

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

Start mysql usig the options you just set

systemctl start mysqld

Login as root

mysql -u root

Update the root user password with these mysql commands

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') WHERE User = 'root' AND Host = 'localhost';

For version 5.7.6 or later, you should use

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

mysql> FLUSH PRIVILEGES;

mysql> quit

Stop mysql

systemctl stop mysqld

Unset the mySQL environment option so it starts normally next time

systemctl unset-environment MYSQLD_OPTS

start mySQL normally

systemctl start mysqld

Try to login using your new password

mysql -u root -p