Reset MySQL Database root Password by Editing Configuration Files

Sometimes, there is a need or emergency circumstance involved an action to reset MySQL Database Server’s root account. One of those days are accidentally forgot the password of root account so that the account cannot be used normally to connect, to login or to access MySQL Database Server. And there are also many things can trigger the cause for a quick decision to reset MySQL Database Server’s password for the root account.

One way or another, this article doesn’t have any malicious intent or any bad purposes presenting method or way to reset someone else’ root account of his or her MySQL Database Server. The intention is to help fellow database administrator whom trapped in a situation explained previously. This is the detailed steps :

1. Either locally or remotely located, first of all access the server where the MySQL Database Server is located.

2. After getting access, in order to be able to reset MySQL Database Server’s root password, a file which is responsible for MySQL Database configuration must be edited. It is located generally in /etc/my.cnf depends on the environment of the server and also the version of the MySQL itself. In Windows operating system, it is actually represented by file named my.ini.

3. Switch to root to be able to edit the specified file mentioned in the previous step by executing the following command :

user@hostname:~$ sudo su -
[sudo] password for user:
root@hostname:~#

4. After successfully switching to ‘root’ account of the operating system, edit the MySQL Database Server’s configuration file as it has already stated before by typing the command below :

vim /etc/my.ini
Description : 
vim : In this article, vim is a text-based editor which is used to manipulate text file. 
/etc/my.cnf : The file and also the specific location of MySQL configuration file specifically in Linux varian operating system distribution. 

5. Try to add the following content to MySQL Configuration file in the [mysqld] section as shown below :

[mysqld]
#
# * Basic Settings
#
skip-grant-tables

The content which is only one specific line :

skip-grant-tables

It is actually an attribute which is used to avoid completely for checking on MySQL Database Server’s grant tables on connection and also other further operations such as queries. Under that conditions it can be realized that without any check on grant tables, anyone can log in from anywhere, and do anything on any database exist in MySQL Database Server.

The change above can be performed by restarting MySQL Database service and it can be accomplished by executing the command below :

service mysql restart

The restart process on MySQL Database Server will let the login process executed without having to include username and password as the parameter to access MySQL Command Console which can be shown in the following output of command execution :

root@hostname:~# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, 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>

6. Having entered MySQL Command Console without any username and password to be inserted is a total advantage to reset root password further. After logging in, the root password can be reset by executing the following query on MySQL Command Console :

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string=PASSWORD('123456') where User='root';
Query OK, 4 rows affected, 1 warning (0,01 sec)
Rows matched: 4  Changed: 4  Warnings: 1
mysql>

Since MySQL Database Server 5.7 the field password has been changed into authentication_string. So, in order to reset root’s account on the earlier version of MySQL Database Server below is the query which can be executed in MySQL Command Console :

update user set password=PASSWORD('123456') where User='root';

7. After successfully reset root’s password by changing the password, try to re-edit MySQL Configuration file in /etc/my.cnf to put the file back to its original state so that MySQL Database Server can be properly functioned as usual. That means, deleting ‘skip-grant-tables’ in /etc/my.cnf and restart MySQL Databaser Server’s service in order for MySQL Database Server to authenticate any connection or further operations.

Since the root’s password has already been reset, logging back to MySQL Command Console after normally configured back so that any attempts for connection and further operation must be validated by providing valid username and password will be easy.

Try to log with root account and the corresponding password which has already been changed in the previous step.