Mysql恢复管理密码

恢复MySQL数据库密码步骤原理

• 停止Mysql服务程序

• 跳过授权表启动MySQL服务程序

• 重设root密码（更新user表记录）

• 以正常方式重启MySQL服务程序

密码恢复实例

例1：重置MySQL管理密码

• 首先停止已运行的MySQL服务程序

[root@host50 ~]# systemctl stop mysqld[root@host50 ~]# systemctl status mysqld● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: inactive (dead) since Tue 2019-07-02 03:54:56 CST; 6s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 1426 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)  Process: 1083 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1430 (code=exited, status=0/SUCCESS)Jul 02 03:31:22 host50 systemd[1]: Starting MySQL Server...Jul 02 03:31:36 host50 systemd[1]: Started MySQL Server.Jul 02 03:54:55 host50 systemd[1]: Stopping MySQL Server...Jul 02 03:54:56 host50 systemd[1]: Stopped MySQL Server.

• 跳过授权表启动MySQL服务程序（配置--skip-grant-tables选项）见文档最后一行

[root@host50 ~]# vim /etc/my.cnf[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksecure_file_priv="/myload"# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0default-storage-engine=innodblog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidskip_grant_tables=1

• 重连mysql后通过修改mysql库中user表中记录，做到重设root用户本机登录密码

[root@host50 ~]# systemctl restart mysqld[root@host50 ~]# mysql -urootmysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456')-> WHERE user='root' AND host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)mysql> exit

注：通过执行"FLUSH PRIVILEGES;"可使授权表立即生效，对于正常运行的MySQL服务，也可以用上述方法来修改密码，不用重启服务。本例中因为是恢复密码，最好重启MySQL服务程序，所以上述"FLUSH PRIVILEGES;"操作可跳过。

• 重新以正常方式启动Mysql服务程序，验证新密码（可注释skip_grant_tables选项）
  

[root@host50 ~]# vim /etc/my.cnf[mysqld]#skip_grant_tables=1...[root@host50 ~]# systemctl restart mysqld[root@host50 ~]# mysql -urootERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@host50 ~]# mysql -uroot -p123456mysql: [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 4Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, 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>

例2：重设Mysql管理用户密码（已知密码）

• 法一：使用mysqladmin管理工具，需要验证旧的密码

[root@host50 ~]# mysqladmin -u root -p password 'qaz123edc'                    Enter password:                                   mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

• 法二：以root登录mysql后，使用set password指令设置（须先配置validate_password_policy=0）

mysql> set password for root@localhost=password('123456');Query OK, 0 rows affected, 1 warning (0.00 sec)

• 法三：以root登录mysql后，使用grant授权工具设置

mysql> grant all on *.* to root@localhost identified by '123456';Query OK, 0 rows affected, 1 warning (0.00 sec)

• 法四：以root登录MySQL后，使用update更新相应的表记录

mysql> update mysql.user set authentication_string=password('123456')    -> where user='root' and host='localhost';Query OK, 0 rows affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 1