MySQL誤刪root使用者恢復一例

神諭丶發表於2016-08-22
這是一個真實又蛋疼的事情:
一個朋友在領導要求他刪除root@127.0.0.1,root@'%'等使用者,只保留root@localhost時,
他寫了一條類似delete from mysql.user where user='root'的命令……
注意,他並沒有寫 “and host=”的條件,導致悲劇發生,並且還flush了授權。

以下模擬誤刪操作,嘗試做恢復:

MySQL版本:
MySQL 5.5.49

模擬誤刪操作:
  1. mysql> DELETE FROM mysql.user WHERE user='root';
  2. Query OK, 1 row affected (0.01 sec)

  3. mysql> FLUSH PRIVILEGES;
  4. Query OK, 0 rows affected (0.01 sec)

解決思路:
新安裝或者初始化一個新的例項(與誤刪操作的MySQL版本最好一致)
初始化好後,啟動例項,並以root@localhost使用者登入,然後設定密碼:

新例項上:
  1. mysql> SELECT current_user();
  2. +----------------+
  3. | current_user() |
  4. +----------------+
  5. | root@localhost |
  6. +----------------+
  7. 1 row in set (0.00 sec)

  8. mysql> SET PASSWORD=password('123456');
  9. Query OK, 0 rows affected (0.00 sec)

  10. mysql> FLUSH PRIVILEGES;
  11. 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)


對於誤刪操作的例項:
首先將之前查詢出的/tmp/root.txt檔案傳到該機上,此處傳到同目錄下,操作略。

然後要停掉mysqld,並繞過授權表啟動:
可能無法透過mysqladmin shutdown來停止,此處直接kill掉mysqld_safe與mysqld,操作略。

然後啟動:
  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

進入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. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  6. Oracle is a registered trademark of Oracle Corporation and/or its
  7. affiliates. Other names may be trademarks of their respective
  8. owners.

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

  10. mysql> SELECT user(),current_user();
  11. +--------+----------------+
  12. | user() | current_user( |
  13. +--------+----------------+
  14. | root | @               |
  15. +--------+----------------+
  16. 1 row in set (0.00 sec)

可以檢視一下mysql.user表,已經沒有了誤刪的root使用者,只剩下xxx@'ip1',yyy@'ip2',這樣的業務使用者:
  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)

將之前的新例項的mysql.user表中的root@localhost資訊匯入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. mysql> FLUSH PRIVILEGES;
  5. Query OK, 0 rows affected (0.00 sec)

  6. mysql> SELECT user,host FROM mysql.user WHERE user='root' AND host='localhost';
  7. +------+---------------+
  8. | user | host          |
  9. +------+---------------+
  10. | root | localhost     |
  11. +------+---------------+
  12. 1 rows in set (0.00 sec)

退出到shell環境,關閉以skip-grant-tables方式啟動的mysqld:
此時已經可以用mysqladmin來關閉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

再重新啟動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


已經可以正常使用了,密碼是之前在初始化的新例項設定的:
  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. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  6. Oracle is a registered trademark of Oracle Corporation and/or its
  7. affiliates. Other names may be trademarks of their respective
  8. owners.

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

  10. mysql> SELECT user(),current_user();
  11. +----------------+----------------+
  12. | user()            | current_user()  |
  13. +----------------+----------------+
  14. | root@localhost | root@localhost  |
  15. +----------------+----------------+
  16. 1 row in set (0.00 sec)


檢視一下許可權,可以對比一下,與之前的無異:
  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)


當然該方式缺點很明顯,需要停機操作,如果真的遇到這種蛋疼問題,可能只有在停機維護的時候做操作了。


可能還一種思路是新初始化例項之後(或者根據同業務的其他例項),將datadir/mysql中的
user.frm、user.MYD、user.MYI
覆蓋掉誤刪的例項上,當然侷限性就是需要將其他的使用者資訊做好備份,再恢復到新的mysql.user表裡


P.S. 
〇 如果在執行了DELETE FROM mysql.user WHERE user='root';之後,沒有flush privileges,就可以不需要停mysqld並繞過授權表了。
〇 如果在重要的環境中透過--skip-grant-tables方式啟動mysqld,建議加上--skip-networking的引數。

作者微信公眾號(持續更新)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29773961/viewspace-2123772/,如需轉載,請註明出處,否則將追究法律責任。

相關文章