MySQL誤刪root用戶怎么恢復(fù)

本篇內(nèi)容介紹了“MySQL誤刪root用戶怎么恢復(fù)”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

一個(gè)朋友在領(lǐng)導(dǎo)要求他刪除root@127.0.0.1，root@'%'等用戶，只保留root@localhost時(shí)，
他寫(xiě)了一條類似delete from mysql.user where user='root'的命令……
注意，他并沒(méi)有寫(xiě) “and host=”的條件，導(dǎo)致悲劇發(fā)生，并且還flush了授權(quán)。

以下模擬誤刪操作，嘗試做恢復(fù)：

MySQL版本：
MySQL 5.5.49

模擬誤刪操作：

1. mysql> DELETE FROM mysql.user WHERE user='root';
   

2. Query OK, 1 row affected (0.01 sec)
   

3. 
   

4. mysql> FLUSH PRIVILEGES;
   

5. Query OK, 0 rows affected (0.01 sec)

解決思路：
新安裝或者初始化一個(gè)新的實(shí)例（與誤刪操作的MySQL版本最好一致）
初始化好后，啟動(dòng)實(shí)例，并以root@localhost用戶登錄，然后設(shè)置密碼：

新實(shí)例上：

1. mysql> SELECT current_user();
   

2. +----------------+
   

3. | current_user() |
   

4. +----------------+
   

5. | root@localhost |
   

6. +----------------+
   

7. 1 row in set (0.00 sec)
   

8. 
   

9. mysql> SET PASSWORD=password('123456');
   

10. Query OK, 0 rows affected (0.00 sec)
    

11. 
    

12. mysql> FLUSH PRIVILEGES;
    

13. Query OK, 0 rows affected (0.00 sec)

將存放在mysql.user里的root@localhost用戶信息查詢出：

1. mysql> SELECT * FROM mysql.user WHERE user='root' AND host='localhost' INTO OUTFILE '/tmp/root.txt';
   

2. Query OK, 1 row affected (0.00 sec)

對(duì)于誤刪操作的實(shí)例：
首先將之前查詢出的/tmp/root.txt文件傳到該機(jī)上，此處傳到同目錄下，操作略。

然后要停掉mysqld，并繞過(guò)授權(quán)表啟動(dòng)：
可能無(wú)法通過(guò)mysqladmin shutdown來(lái)停止，此處直接kill掉mysqld_safe與mysqld，操作略。

然后啟動(dòng)：

1. [root@vm02 ~]# mysqld_safe --skip-grant-tables &

2. [1] 2957

3. [root@vm02 ~]# 160819 17:00:30 mysqld_safe Logging to '/data/mysql_log/err-log.err'.

4. 160819 17:00:30 mysqld_safe Starting mysqld daemon with databases from /data/mysql

進(jìn)入mysql：

1. [root@vm02 ~]# mysql
   

2. Welcome to the MySQL monitor. Commands end with ; or \g.
   

3. Your MySQL connection id is 3
   

4. Server version: 5.5.49-log MySQL Community Server (GPL)

5. 
   

6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

7. 
   

8. Oracle is a registered trademark of Oracle Corporation and/or its
   

9. affiliates. Other names may be trademarks of their respective
   

10. owners.
    

11. 
    

12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    

13. 
    

14. mysql> SELECT user(),current_user();
    

15. +--------+----------------+
    

16. | user() | current_user()  |
    

17. +--------+----------------+
    

18. | root@  | @               |
    

19. +--------+----------------+
    

20. 1 row in set (0.00 sec)

可以查看一下mysql.user表，已經(jīng)沒(méi)有了誤刪的root用戶，只剩下xxx@'ip1'，yyy@'ip2'，這樣的業(yè)務(wù)用戶：

1. mysql> SELECT user,host FROM mysql.user;
   

2. +------+---------------+
   

3. | user  | host          |
   

4. +------+---------------+
   

5. | xxx  | 192.168.1.185 |
   

6. | yyy  | 192.168.1.187 |
   

7. +------+---------------+
   

8. 2 rows in set (0.00 sec)

將之前的新實(shí)例的mysql.user表中的root@localhost信息導(dǎo)入mysql.user：

1. mysql> LOAD DATA INFILE '/tmp/root.txt' INTO TABLE mysql.user;
   

2. Query OK, 1 row affected (0.04 sec)
   

3. Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
   

4. 
   

5. mysql> FLUSH PRIVILEGES;
   

6. Query OK, 0 rows affected (0.00 sec)
   

7. 
   

8. mysql> SELECT user,host FROM mysql.user WHERE user='root' AND host='localhost';
   

9. +------+---------------+
   

10. | user | host          |
    

11. +------+---------------+
    

12. | root | localhost     |
    

13. +------+---------------+
    

14. 1 rows in set (0.00 sec)

退出到shell環(huán)境，關(guān)閉以skip-grant-tables方式啟動(dòng)的mysqld：
此時(shí)已經(jīng)可以用mysqladmin來(lái)關(guān)閉mysqld了：

1. [root@vm02 tmp]# mysqladmin -uroot -p123456 shutdown

2. 160819 17:08:08 mysqld_safe mysqld from pid file /data/mysql/mysql-pid ended

3. [1]+  Done                    mysqld_safe --skip-grant-tables  (wd: ~)

4. (wd now: /tmp)

5. [root@vm02 tmp]# ps -ef|grep mysql

6. root       3938   1973  0 17:08 pts/0    00:00:00 grep mysql

再重新啟動(dòng)mysqld：

1. [root@vm02 tmp]# mysqld_safe &

2. [1] 3939

3. [root@vm02 tmp]# 160819 17:08:53 mysqld_safe Logging to '/data/mysql_log/err-log.err'.

4. 160819 17:08:53 mysqld_safe Starting mysqld daemon with databases from /data/mysql

已經(jīng)可以正常使用了，密碼是之前在初始化的新實(shí)例設(shè)置的：

1. [root@vm02 tmp]# mysql -uroot -p123456
   

2. Welcome to the MySQL monitor. Commands end with ; or \g.
   

3. Your MySQL connection id is 2
   

4. Server version: 5.5.49-log MySQL Community Server (GPL)
   

5. 
   

6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   

7. 
   

8. Oracle is a registered trademark of Oracle Corporation and/or its
   

9. affiliates. Other names may be trademarks of their respective
   

10. owners.
    

11. 
    

12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    

13. 
    

14. mysql> SELECT user(),current_user();
    

15. +----------------+----------------+
    

16. | user()            | current_user()  |
    

17. +----------------+----------------+
    

18. | root@localhost | root@localhost  |
    

19. +----------------+----------------+
    

20. 1 row in set (0.00 sec)

查看一下權(quán)限，可以對(duì)比一下，與之前的無(wú)異：

1. mysql> SHOW GRANTS;
   

2. +----------------------------------------------------------------------------------------------------------------------------------------+
   

3. | Grants for root@localhost                                                                                                              |
   

4. +----------------------------------------------------------------------------------------------------------------------------------------+
   

5. | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
   

6. | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
   

7. +----------------------------------------------------------------------------------------------------------------------------------------+
   

8. 2 rows in set (0.00 sec)

“MySQL誤刪root用戶怎么恢復(fù)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！