MySQL忘记密码怎么办？？

发布时间：2020-12-12 02:15:00 所属栏目：MySql教程来源：网络整理

导读：1、停掉mysql 1.1单实例停止方式 [root@qiuhom ~]# /etc/init.d/mysqld stopShutting down MySQL. [ OK ] 1.2多实例停止方式 [root@qiuhom ~]# /data/3306/mysql stopStopping MySQL ... 当然停止mysqld的方式有很多比如也可以用kill -9 +mysqld的pid号,kill

1、停掉mysql

1.1单实例停止方式

[root@qiuhom ~]# /etc/init.d/mysqld stop
Shutting down MySQL.                                       [  OK  ]

　　1.2多实例停止方式　

[root@qiuhom ~]# /data/3306/mysql stop
Stopping MySQL ...

当然停止mysqld的方式有很多比如也可以用kill -9 +mysqld的pid号,killall mysqld 或者pkill mysqld都可以将进程杀死，但是这种简单粗暴的方式我们不推荐。

2、用mysqld_safe 启动数据库

2.1单实例启动

[root@qiuhom ~]# mysqld_safe --skip-grant-tables --user=mysql &
[1] 1285
[root@qiuhom ~]# 181004 17:23:52 mysqld_safe Logging to '/application/mysql-5.5.32/data/qiuhom.err'.
181004 17:23:52 mysqld_safe Starting mysqld daemon with databases from /application/mysql-5.5.32/data

[root@qiuhom ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.32-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>

提示：用mysqld_safe启动数据库一定要加--skip-grant-tables 忽略授权表的方式启动，当然也要指定用户。

通过以上mysqld_safe启动后此时登录数据库就没有密码了我可以mysql直接进入也可以mysql -uroot -p 回车密码为空直接回车也可以进入数据库

2.2多实例启动

[root@qiuhom ~]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
[1] 798
[root@qiuhom ~]# 181003 10:16:53 mysqld_safe Logging to '/data/3306/mysql_lee3306.err'.
181003 10:16:53 mysqld_safe Starting mysqld daemon with databases from /data/3306/data

[root@qiuhom ~]# mysql -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.32-log Source distribution

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

提示：多实例的启动要指定启动配置文件的位置，--defaults-file 就是用来指定启动配置文件的位置。值得注意的是登录多实例mysql 一定要指定mysql的sock文件

-S 来指定登录mysql的sock文件位置

3、进入数据库后，我们用update对mysql库里的user表进行修改

mysql> update mysql.user set password=password("admin123.com") where user='test' and host='localhost';
Query OK,1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,password from mysql.user;
+------------+-----------+-------------------------------------------+
| user       | host      | password                                  |
+------------+-----------+-------------------------------------------+
| root       | localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| root       | 127.0.0.1 |                                           |
| qiuhom     | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| test       | localhost | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
| test       | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| qiuhom_db1 | localhost | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
| qiuhom_db2 | localhost | *2D9AC2437F9E59A51BE8BA89A3D59E76F32F55E8 |
+------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> flush privileges;
Query OK,0 rows affected (0.00 sec)

提示：设置的密码我不能直接写password=“xxx”,一定要用password函数将自己设置的密码加密，我们在数据库里看到的都是密文密码。用update 修改表里的内容，一定要注意后面一定要条件，条件越多越精确。最后不要忘记刷新权限哟！！！

4、停止掉mysqld_safe启动的数据库，重新以正常的方式启动数据库

4.1停止mysqld_safe启动的数据库实例（单实例）

[root@qiuhom ~]# mysqladmin -utest -padmin123.com shutdown
181004 17:53:37 mysqld_safe mysqld from pid file /application/mysql-5.5.32/data/qiuhom.pid ended
[1]+  Done                    mysqld_safe --skip-grant-tables --user=mysql
[root@qiuhom ~]# ps -ef |grep mysql|grep -v grep 
[root@qiuhom ~]#

　　4.2停止mysqld_safe启动的数据库实例（多实例）

[root@qiuhom ~]# mysqladmin -utest -padmin123.com -S /data/3306/mysql.sock shutdown
181003 10:46:59 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended
[1]+  Done                    mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables
[root@qiuhom ~]# ps -ef |grep 3306|grep -v grep 
[root@qiuhom ~]#

　　4.3正常启动mysql(单实例)

[root@qiuhom ~]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@qiuhom ~]# mysql -utest -padmin123.com
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.5.32-log Source distribution

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

　　4.4正常启动mysql（多实例）

[root@qiuhom ~]# /data/3306/mysql start
Starting MySQL...
[root@qiuhom ~]# mysql -utest -padmin123.com -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

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

提示：以上是忘记密码，重新设置密码的流程不要和mysqladmin混淆，mysqladmin用于空密码设置密码或者知道密码修改密码。多实例的启动一定要加--defaults-file来指定启动配置文件，登录多实例一定要用-S（大写）指定mysql的sock文件。。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!