作者：俊達(dá)

引言

當(dāng)你突然忘記了一個(gè)普通用戶的密碼，而又想著通過(guò)管理員賬號(hào)去改密碼時(shí)，卻猛的發(fā)現(xiàn)所有管理員賬號(hào)的密碼都離譜地被你忘了。嗨呀，這可真是個(gè)尷尬的大麻煩！root賬戶通常是MySQL中的大boss，你會(huì)發(fā)現(xiàn)自己掉進(jìn)了密碼的黑洞里?；蛟S這時(shí)你會(huì)想撞墻了，反復(fù)～

但別急！All is not lost（一切尚未失去）。
當(dāng)你忘記了root密碼，有一個(gè)神奇的辦法：用skip-grant-tables參數(shù)來(lái)啟動(dòng)數(shù)據(jù)庫(kù)，然后重新設(shè)置root密碼。搞定之后，你又會(huì)發(fā)現(xiàn)，所有人都可以無(wú)密碼進(jìn)入數(shù)據(jù)庫(kù)，簡(jiǎn)直就像個(gè)開(kāi)放式派對(duì)?。?！但別擔(dān)心，你大可以配合bind-address或skip-networking參數(shù)一起使用，只允許本機(jī)的小伙伴們參與這場(chǎng)數(shù)據(jù)庫(kù)的“派對(duì)”。下面，將手把手地教你如何重新設(shè)置MySQL root密碼。

1、停止數(shù)據(jù)庫(kù)實(shí)例。

停止mysql實(shí)例。可以使用多種方法停止實(shí)例。這里使用serivce停止mysql

service mysqld stop

2、配置文件中增加skip-grant-tables和skip-networking參數(shù)

# /etc/my.cnf
skip-grant-tables
skip-networking

3、重新啟動(dòng)實(shí)例

2021-04-06T15:53:17.250207Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.32'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server (GPL)

4、登陸數(shù)據(jù)庫(kù)

由于加了skip-networking參數(shù)，只能通過(guò)socket登陸數(shù)據(jù)庫(kù)

[root@box1 ~]# mysql -h 127.0.0.1
ERROR 2003 (HY000): Cant connect to MySQL server on '127.0.0.1' (111)

[root@box1 ~]# mysql  -S /var/lib/mysql/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

5、重新加載權(quán)限

實(shí)例使用skip-grant-tables參數(shù)啟動(dòng)，無(wú)法直接修改賬號(hào)密碼。需要執(zhí)行flush privileges后才能修改密碼。
使用alter user或set password命名修改密碼。

mysql> alter user 'root'@'localhost' identified by 'helloww';
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> alter user 'root'@'localhost' identified by 'helloww';
Query OK, 0 rows affected (0.00 sec)


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

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

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6、修改參數(shù)，重新啟動(dòng)數(shù)據(jù)庫(kù)

[root@box1 ~]# sed -i '/skip-networking/d' /etc/my.cnf
[root@box1 ~]# sed -i '/skip-grant-tables/d' /etc/my.cnf
[root@box1 ~]# grep skip /etc/my.cnf
[root@box1 ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service


[root@box1 ~]# tail -2 /var/log/mysqld.log
2021-04-06T16:02:20.527113Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

7、驗(yàn)證數(shù)據(jù)庫(kù)恢復(fù)正常

重啟后需要使用密碼才能登陸數(shù)據(jù)。

[root@box1 ~]# mysql -uroot -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@box1 ~]# mysql -uroot -h127.0.0.1 -phelloww
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

恭喜你！通過(guò)上述神奇的操作，成功地奪回?cái)?shù)據(jù)庫(kù)控制權(quán)！