How to reset the MySQL Server admin/root Password

MySQL
The following are the steps to restore/reset MySQL admin/root password:
Step-1: Stop your MySQL serverThere are many ways to do this:

  • service mysql stop  or
  • service mysqld stop or
  • systemctl stop mysqld

[[email protected]]# service mysqld stop

Step-2: Start your MySQL server in safe mode by using following command.

mysqld_safe –skip-grant-tables –autoclose

It will start the MySQL server with out asking any passwords.

[[email protected]]# mysqld_safe –skip-grant-tables –autoclose

Step-3: Connect to MySQL server.

[[email protected]]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

Step-4: Run the below commands, to setup new password for the root or admin.

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 mysql.user SET Password=PASSWORD(“secret”)
-> WHERE user=”root”;

Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

The flush statement tells the server to reload the grant tables into memory so that it notices the password change.

mysql> exit;

Now your MySQL Server password is successfully reseted.

Step-5: Restart MySQL server. This can be done in two ways.

1. Stop and Start the Server

[[email protected]]# service mysqld stop
[[email protected]]# service mysqld start

(or)

2. Directly Restart the Server

[[email protected]]# service mysqld restart

Step-6: Now login with your new password

[[email protected]]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.15

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>