MySQL 数据库管理员密码忘记，如何重置密码？
在企业中，数据库密码复杂度极高。偶尔会忘记数据库管理员的密码，在这种情况下如何保证数据不丢失情况下，并且重置数据库管理员的密码呢？下文将会以MySQL 5.7.26版本为例进行介绍。
1. 关闭数据库
[root@templates ~]# systemctl stop mysqld [root@templates ~]# netstat -lntup|grep 3306
【注意】关闭数据库和启动数据库的方式要一致，即 sys -v 方式启动和关闭数据库或者 systemd 方式启动和关闭数据库。不能交叉使用。
2. 启动数据库到维护模式
--skip-grant-tables 跳过授权表，避开密码验证功能
--skip-networking 跳过远程登录
[root@templates ~]# mysqld_safe --skip-grant-tables --skip-networking & #<==== 以命令的方式启动数据库 [1] 3207 [root@templates ~]# 2021-03-02T16:00:18.536024Z mysqld_safe Logging to '/data/mysql/data/templates.err'. 2021-03-02T16:00:18.627434Z mysqld_safe A mysqld process already exists [1]+ Exit 1 mysqld_safe --skip-grant-tables --skip-networking
3. 登录数据库，并修改密码
方式一：
[root@templates ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> alter user root@'localhost' identified by '123.com'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> alter user root@'localhost' identified by '123.com'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@templates ~]#
方法二：(包括更改完进行登录验证)
[root@templates ~]# mysqld_safe --skip-grant-tables --skip-networking & [1] 3784 [root@templates ~]# 2021-03-02T16:11:22.739112Z mysqld_safe Logging to '/data/mysql/data/templates.err'. 2021-03-02T16:11:22.837697Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data [root@templates ~]# ps -ef|grep mysqld root 3784 2341 0 00:11 pts/0 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking mysql 3959 3784 3 00:11 pts/0 00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/data/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=templates.err --pid-file=templates.pid --socket=/tmp/mysql.sock --port=3306 root 3989 2341 0 00:11 pts/0 00:00:00 grep --color=auto mysqld [root@templates ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> grant all on *.* to root@'localhost' identified by '123456'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'localhost' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> exit Bye [root@templates ~]#
[root@templates ~]# ps -ef|grep mysqld root 3784 2341 0 00:11 pts/0 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking mysql 3959 3784 0 00:11 pts/0 00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/data/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=templates.err --pid-file=templates.pid --socket=/tmp/mysql.sock --port=3306 root 3996 2341 0 00:15 pts/0 00:00:00 grep --color=auto mysqld [root@templates ~]# kill -9 3784 [root@templates ~]# ps -ef|grep mysqld mysql 3959 1 0 00:11 pts/0 00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/data/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=templates.err --pid-file=templates.pid --socket=/tmp/mysql.sock --port=3306 root 3999 2341 0 00:15 pts/0 00:00:00 grep --color=auto mysqld [1]+ Killed mysqld_safe --skip-grant-tables --skip-networking [root@templates ~]# kill -9 3959 [root@templates ~]# ps -ef|grep mysqld root 4001 2341 0 00:15 pts/0 00:00:00 grep --color=auto mysqld [root@templates ~]# systemctl start mysqld [root@templates ~]# netstat -lntup|grep 3306 tcp6 0 0 :::3306 :::* LISTEN 4008/mysqld [root@templates ~]# mysql -uroot -p123456 mysql: [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 2 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 3 rows in set (0.00 sec) mysql>
4. 关闭数据库，重新启动进行验证(方式一登录验证)
[root@templates ~]# ps -ef|grep mysqld_safe root 3383 2341 0 00:03 pts/0 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking root 3595 2341 0 00:06 pts/0 00:00:00 grep --color=auto mysqld_safe [root@templates ~]# kill -9 3383 [root@templates ~]# ps -ef|grep mysqld_safe root 3599 2341 0 00:06 pts/0 00:00:00 grep --color=auto mysqld_safe [root@templates ~]# systemctl start mysqld [root@templates ~]# mysql -uroot -p123.com mysql: [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 4 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 3 rows in set (0.00 sec) mysql> exit Bye
