Resolve MySQL forgot root password

There are many articles on the Internet about forgetting the MySQL root password, and there are written how to solve it, but sometimes I feel that it is too disgusting, either copying others' words one by one, or it is not clear, well Do not vomit, the following is the entire process of solving.

First of all, we need to know whether we can restart mysql after forgetting the MySQL root password. What is the operation that can be restarted? What about operations that cannot be restarted?

 

Case 1: (when it can be restarted)

Modify the my.cnf configuration file, add skip-grant-tables option under the mysqld column, meaning that after the mysqld server starts, it does not use the privilege system (privilege system), it can also be understood as skipping the authorization table. For security reasons, skip-networking is usually added, and mysqld does not listen for any TCP / IP connection requests.

Restart mysqld and connect with a blank password:

[root ~] $ mysql -uroot -S /data/mysql-5.5/mysql.sock
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 3
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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>
You can see that you have successfully logged in, and then change the root password:

mysql> update mysql.user set password = password (‘123456’) where user = ‘root’;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
I have successfully changed the password, but there is still something to do, just delete or comment out the skip-grant-tables and skip-networking that I just added to my.cnf.

 

Case 2: (The case where mysql cannot be restarted)

If you can't restart, mysql.user happens to have a user with lower permissions. If not, please ask the fairy to help you, haha

1. For testing, I create a user myself, no permission

mysql> create user [email protected] ‘localhost‘ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)
2. Enter the data directory:

[root mysql-5.5] $ pwd
/data/mysql-5.5
[root mysql-5.5] $ cp mysql / user. * test /
[root mysql-5.5] $ chown mysql.mysql test / user. *
3. Log in with a user with relatively small permissions:

[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 connection id is 3
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> use test
Database changed
mysql> update user set password = password (‘123123’) where user = ‘root’;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
4. Copy the modified user.MYD and user.MYI to the mysql directory, and remember to back up the previous files.

[root mysql-5.5] $ pwd
/data/mysql-5.5
[root mysql-5.5] $ mv mysql / user.MYD mysql / user.MYD.bak
[root mysql-5.5] $ mv mysql / user.MYI mysql / user.MYI.bak
[root mysql-5.5] $ cp test / user.MY * mysql /
[root mysql-5.5] $ chown mysql: mysql mysql / user. *
5. Find the mysql process number and send SIGHUP signal to reload the permission table. (Sometimes when it doesn't work once, load another time @. @)

[root mysql] $ pgrep -n mysql
23166
[root mysql] $ kill -SIGHUP 23166
[root mysql] $ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock
ERROR 1045 (28000): Access denied for user ‘root‘ @ ‘localhost’ (using password: YES)
[root mysql] $ kill -SIGHUP 23166
[root mysql] $ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 5
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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>
 

The second method without restarting:

1. Create a new data directory and give the corresponding permissions to the original user

[root data] $ mkdir -pv / dbdata / datadir /
mkdir: created directory "/ dbdata"
mkdir: created directory "/ dbdata / datadir /"
[root data] $ chown -R mysql: mysql / dbdata / datadir /
2. Perform the initialization operation: (error reported)

[root scripts] $ pwd
/usr/local/mysql-5.5.40/scripts
[root scripts] $ ./mysql_install_db --datadir = / dbdata / datadir / --user = mysql2

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to run ‘make install’ to
copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.
Solution:

[root scripts] $ /usr/local/mysql-5.6.10/scripts/mysql_install_db --datadir = / dbdata / datadir / --user = mysql --datadir = / dbdata / datadir / --basedir = / usr / local /mysql-5.6.10/
Installing MySQL system tables ...
141210 16:09:24 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
OK
Filling help tables ...
141210 16:09:24 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
OK
3. Start a new process. Here we should pay attention to the port, sock file, and pid file, which are all new. The user still forgets the password instance user, not the database instance corresponding to the password. Need to use InnoDB engine, set the default engine to MyISAM:

[root ~] $ /usr/local/mysql-5.6.10/bin/mysqld_safe --datadir = / dbdata / datadir --plugin-dir = / usr / local / mysql-5.6.10 / lib / plugin /- skip-innodb \
> --default-storage-engine = myisam --socket = / dbdata / datadir / mysql2.sock --user = mysql --port = 3305 --log-error = / dbdata / datadir / error2.log --pid- file = / data / mysql-5.6 / mysql.pid &
[1] 21204
[root ~] $ 141210 16:
56:11 mysqld_safe Logging to ‘/dbdata/datadir/error2.log’.
141210 16:56:11 mysqld_safe Starting mysqld daemon with databases from / dbdata / datadir
4. Log in to the newly started mysql instance, at this time the password is blank:

root datadir] $ /usr/local/mysql-5.6.10/bin/mysql -S /dbdata/datadir/mysql2.sock
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.5.30-log Source distribution

Copyright (c) 2000, 2013, 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> flush tables;
Query OK, 0 rows affected (0.00 sec)
Change the root password:

mysql> select user, host, password from user where user like ‘root’;
+ ------ + ----------------------- + ---------- +
| user | host | password |
+ ------ + ----------------------- + ---------- +
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | :: 1 | |
+ ------ + ----------------------- + ---------- +
4 rows in set (0.02 sec)

mysql> update mysql.user set password = password (‘654321‘) where user = ‘root’;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5. Copy the new user table to the mysql directory of the forgotten password instance database

[root mysql] $ pwd
/ dbdata / datadir / mysql
[root mysql] $ cp user. * /data/mysql-5.6/mysql/
cp: Do you overwrite "/data/mysql-5.6/mysql/user.frm"? y
cp: Do you overwrite "/data/mysql-5.6/mysql/user.MYD"? y
cp: Do you overwrite "/data/mysql-5.6/mysql/user.MYI"? y
[root mysql] $ chown -R mysql5.6: mysql5.6 /data/mysql-5.6/
[root mysql] $ chmod 660 /data/mysql-5.6/mysql/user.*
6. We need to send a sighup signal to mysqld. MySQL responds to this signal to load the authorization table, refresh the table, log, and thread cache.
If it is a single MySQL instance, you can use this method to reload

[root ~] $ kill -1 $ (/ sbin / pidof mysqld)
If it is multiple MySQL instances on a server, you should pay attention to it. You can find the pid by this method. The port of my old instance is 3306:

[root mysql-5.6.10] $ netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID / Program name
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 8414 / mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 6430 / mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1144 / sshd
      
tcp 0 0 ::: 3310 ::: * LISTEN 17151 / mysqld
tcp 0 0 ::: 22 ::: * LISTEN 1144 / sshd
tcp 0 0 :: 1: 631 :::: * LISTEN 1109 / cupsd
tcp 0 0 ::: 3306 ::: * LISTEN 2091 / mysqld
[root mysql-5.6.10] $ kill -1 2091
Sometimes kill -1 doesn't work once, just execute it again:

[root mysql-5.6.10] $ kill -1 2091
[root mysql-5.6.10] $ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘root‘ @ ‘localhost’ (using password: YES)
[root mysql-5.6.10] $ kill -1 2091
[root mysql-5.6.10] $ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock
Warning: 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 13
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>
OK, you have successfully logged in, if there are more good methods, we can discuss together

 

 

to sum up:

1) The first method is simple, but MySQL needs to be restarted. Restarting will affect online services, and it is generally not recommended to restart

2) The second method is better, do not need to restart the MySQL instance, change the password, only modify the root user, and the other remains unchanged

3) The third method does not need to be restarted, but in the new user table, there is only one root user. If there are other users and permissions on the server before, this is more troublesome

 

 

 Reference materials: http://www.percona.com/blog/2014/12/10/recover-mysql-root-password-without-restarting-mysql-no-downtime/

PS: I also made reference to other people's blog, but I did not copy other people's things, it is disgusting, I hope everyone has their own style. ^. ^

 

Solve MySQL forgot root password