Resolve MySQL forgot root password

There are a lot of articles on the internet to forget the root password of the MySQL, there are written how to solve, but sometimes feel too disgusting, or a word does not leak copy others, or say not clear, good, do not spit groove, the following is the whole process of settlement.

First of all we need to know to forget the MySQL root password, can restart MySQL, how to restart the operation? What happens if I don't restart the operation?

Situation One: (can restart the case)

Modify the MY.CNF configuration file and add the Skip-grant-tables option under the Mysqld bar, meaning that the MYSQLD server does not use the permission system (privilege systems) after startup, or it can be understood as skipping the authorization table. For security reasons, usually plus skip-networking,mysqld does not listen on any TCP/IP connection requests.

Restart Mysqld, and then empty the password connection:

[Root ~] $mysql-uroot-s/data/mysql-5.5/Mysql.sockwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 3Server Version:5.5. +-log MySQL Community Server (GPL) Copyright (c) -, the, Oracle and/or its affiliates. All rights reserved. Oracle isA registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'To clear the current input statement.mysql>

You can see that you have successfully logged in, and then modify the root password:

set password=password ('123456'where user='root ' ;     4 rows affected (0.004  4  0
Query OK, 0 rows affected (0.01 sec)

The password has been successfully changed, but there are still things to do, just add to the my.cnf skip-grant-tables and skip-networking deleted or commented out.

Scenario Two: (Unable to restart MySQL)

If not restart, Mysql.user just have permissions than the lower user, if not, you ask the fairy to help you, haha

1, in order to test, I create a user, can have no permissions

mysql> create user [email protected]'localhost'123456'  0 rows affected (0.00 sec)

2, into the data directory:

[Root mysql-5.5pwd/data/mysql-5.5[root mysql-5.5cp mysql/ User.* test/[root mysql-5.5chown mysql.mysql test/user.*

3, with a relatively small user access to login:

[Root mysql-5.5] $mysql-uxuanzhi-p123456-s/data/mysql-5.5/Mysql.sock Welcome to the MySQL monitor. Commands End With; or \g.your MySQL connectionIDIs3Server Version:5.5. +-log MySQL Community Server (GPL) Copyright (c) -, the, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Use testdatabase changedmysql> Update user Set Password=password ('123123') Where user='Root'; Query OK,4Rows Affected (0.00sec) Rows matched:4Changed:4Warnings:0

4, the user after the change. MyD and user.myi Copy to the MySQL directory, remember to back up the previous files.

[Root mysql-5.5pwd/data/mysql-5.5[root mysql-5.5mv mysql/ User. MYD mysql/user. Myd.bak[root MySQL-5.5mv Mysql/user. MYI mysql/user. Myi.bak[root MySQL-5.5cp Test/user. my* mysql/[root mysql-5.5chown mysql:mysql  mysql/user.*

5. Find the MySQL process number and send the sighup signal to reload the permission table. (sometimes load once not, then load more than once.) @）

[Root mysql]$ Pgrep-n MySQL23166[Root mysql]$Kill-sighup23166[Root mysql]$/usr/local/mysql-5.5. +/bin/mysql-uroot-p123123-s/data/mysql-5.5/Mysql.sock ERROR1045(28000): Access denied forUser'Root'@'localhost'(using password:yes) [Root mysql]$Kill-sighup23166[Root mysql]$/usr/local/mysql-5.5. +/bin/mysql-uroot-p123123-s/data/mysql-5.5/Mysql.sock Welcome to the MySQL monitor. Commands End With; or \g.your MySQL connectionIDIs5Server Version:5.5. +-log MySQL Community Server (GPL) Copyright (c) -, the, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>

OK, have successfully logged in, if there are more good ways, we can discuss the next

PS: I also refer to other people's blog to do, but I did not copy other people's things, too disgusting, I hope we have their own style. ^.^

Resolve MySQL forgot root password