Mysql's root password forget how to modify the root password way _mysql

If one day you forget the online MySQL database root password, how to do?

People tend to think of skip-grant-tables parameters, the following steps:

1. Close the MySQL database, because the root password is forgotten, mysqladmin can not be used, at this time, only through the kill PID shutdown program.

Here, the science of the difference between kill and kill-9

By default, kill sends a sigterm signal to the process, telling the process that you need to be shut down, stop running and quit.
Kill-9 send Sigkill signal to process, tell process, you are terminated, please exit immediately. This signal cannot be captured or ignored compared to sigterm, and the process receiving this signal cannot perform any cleanup while receiving the signal.
Therefore, the last resort, do not kill the process through the kill-9, which may cause the MySQL database physical structure damage, can not restart.

2. Add skip-grant-tables parameters in the my.cnf file [mysqld] section

3. Log in to the database and modify the password for the root account

Here are three ways to modify the root password :

1> mysql> set password for ' root ' @ ' localhost ' =password (' 123 '); No need to refresh the permission table

2> mysql> Update mysql.user set Password=password ("456") where user= "root" and host= "localhost";

mysql> flush Privileges;

3> # mysqladmin-u root password "123"

4. Close the database, comment out the skip-grant-tables parameter, and restart the database.

This is a good way, but there is a problem, you have to restart the database, for the online environment, this may not be allowed.

Here's another way to talk about the smell of dark technology.

This method utilizes the Mysql.user table or the characteristics of the MyISAM engine.

1. Copy the instance's Mysql.user table to the directory of another instance, for example, under the directory of the test database

2. Log in to another instance database, modify the permissions of the three files above, and modify the root password

Mysql> select User,host,password from Test.user;
+------+-----------+-------------------------------------------+
| user | host | password     |
+------+-----------+-------------------------------------------+
| root | localhost | 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+------+-----------+-------------------------------------------+
1 row in Set (0.00 sec)

mysql> Update Test.user set Password=password ("Hello") where user= "root" and host= "localhost";
Query OK, 1 row affected (0.15 sec)
Rows matched:1 changed:1 warnings:0

3. Copy the above three files back to the source database

4. Get mysqld pid, kill-hup ' Pidof mysqld ' way to let the mysqld process reload the configuration file

[Root@keepalived01 ~]# Mysql-phello
warning:using a password on the command line interface can is insecure.
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)
[root@keepalived01 ~]# kill-hup 4 283
[root@keepalived01 ~]# mysql-phello
warning:using a password on the command line interface can is insecure.< C6/>welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL connection ID is 2528
Server version:5.6.26 mysql Community server (GPL)

Copyright (c), 2015, Ora CLE and/or its affiliates. All rights reserved.

Oracle is a registered trademark to Oracle Corporation and/or its
affiliates. The other names may is trademarks of their respective
owners.

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

Mysql> 

Through the above output can be seen, kill-hup, directly with the password Hello login is rejected, after kill-hup, you can directly log in.

Of course, the above methods for reference only, in the production of caution, after all, security overriding, God knows where there will be problems.

Above is the entire content of this article, hope can help you solve the root password forget the trouble, thank you for your reading.