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?=?2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure_file_priv="/myload"
#?Disabling?symbolic-links?is?recommended?to?prevent?assorted?security?risks
symbolic-links=0
default-storage-engine=innodb
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip_grant_tables=1

• 重連mysql后通過修改mysql庫中user表中記錄，做到重設root用戶本機登錄密碼

[root@host50?~]#?systemctl?restart?mysqld
[root@host50?~]#?mysql?-uroot
mysql>?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:?1
mysql>?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?-uroot
ERROR?1045?(28000):?Access?denied?for?user?'root'@'localhost'?(using?password:?NO)

[root@host50?~]#?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?4
Server?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?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>

例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