What should I do if I forget my MySQL password ?, Forgot MySQL password,

What should I do if I forget my MySQL password ?, Forgot MySQL password,

What if you forget the root password of the online MySQL database one day?

You will often think of the skip-grant-tables parameter. The specific steps are as follows:

1. Shut down the MySQL database. mysqladmin cannot use the root password because it is forgotten. In this case, the program can only be closed using the kill pid.

Here, let's look at the difference between kill and kill-9.

By default, kill sends the SIGTERM signal to the process, telling the process that you need to be shut down. Please stop running and exit. Kill-9 sends the SIGKILL signal to the process and tells the process that you are terminated. Please exit immediately. Compared with SIGTERM, this signal cannot be captured or ignored. At the same time, the process receiving this signal cannot perform any cleaning when it receives this signal.

Therefore, do not kill the process through kill-9, which may cause the physical structure of the MySQL database to be damaged and the process cannot be restarted.

2. Add the skip-grant-tables parameter to the [mysqld] section of the my. cnf file.

3. log on to the database and change the password of the root account.

The following are three methods to change the root password:

1> mysql> set password for 'root' @ 'localhost' = password ('000000'); 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 & gt; # mysqladmin-u root password "123"

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

 

The above method is good, but there is a problem. You must restart the database. For the online environment, this may not be allowed.

Next, let's talk about another method. It's a bit dark technology.

This method uses the features of the mysql. user table or MyISAM engine.

1. copy the mysql. user table of the instance to the directory of another instance, for example, under the directory of the test database.

2. log on to another instance database, modify the permissions of the preceding three files, and change 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 preceding three files back to the source database.

4. Get the pid of mysqld and re-load the configuration file by kill-HUP 'pidof mysqld '.

[root@keepalived01 ~]# mysql -phelloWarning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@keepalived01 ~]# kill -HUP 4283[root@keepalived01 ~]# mysql -phelloWarning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2528Server version: 5.6.26 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

From the above output, we can see that, before kill-HUP, login with the password hello is rejected. After kill-HUP, you can log on directly.

Of course, the above methods are for reference only and should be used with caution in production. After all, security prevails over everything and tianyao knows where problems may occur.