mysql中root使用者誤刪的處理辦法

hunterjoy發表於2015-09-29
MySql
    在mysql安裝後，清理無用的許可權，使用如下語句：
  delete from user where (user,host) not in (select 'root','localhost');
   由於上述語句寫錯為：
   delete from user where (user,host) not in (select 'root','local');導致root使用者被刪除。

  補救措施：
  1、由於沒有root使用者，無法使用mysqladmin關閉mysql,所以在作業系統層直接殺掉mysql程式：
[root@linfytest3 ~]# ps -ef|grep mysql
root     29356     1  0 15:55 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf
mysql    30506 29356  8 17:00 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root     30531 29955  0 17:00 pts/3    00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 29356
[root@linfytest3 ~]# ps -ef|grep mysql
mysql    30506     1  2 17:00 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root     30534 29955  0 17:00 pts/3    00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 30506
[root@linfytest3 ~]# ps -ef|grep mysql
root     30555 29955  0 17:01 pts/3    00:00:00 grep mysql
 注意，先殺主程式29356，再殺子程式30506，如果先殺子程式30506，那麼mysql程式會自動重啟。

2、使用引數--skip-grant-tables啟動資料庫，啟動資料庫時不進行許可權驗證
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables &
[1] 30677
[root@linfytest3 ~]# 150928 17:04:09 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:04:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307

3、不用root使用者也可以登入mysql伺服器了
[root@linfytest3 ~]#  /usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock

4、使用SQL語句新增root使用者


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)


mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> INSERT INTO user SET User='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';
Query OK, 1 row affected (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y', Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',Create_tablespace_priv='Y',authentication_string='' WHERE User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select host,user,password from users;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql>  select host,user,password from user;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
+-----------+------+----------+

1 row in set (0.00 sec)


5、修改root密碼：
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
mysqladmin: 
You cannot use 'password' command as mysqld runs
 with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
提示：使用--skip-grant-tables引數啟動資料庫，不允許修改root密碼


6、重啟mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqladmin -uroot -S /tmp/mysql3307.sock shutdown
150928 17:14:39 mysqld_safe mysqld from pid file /var/lib/mysql3307/linfytest3.pid ended
[1]+  Done                    /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables
[root@linfytest3 ~]# ps -ef|grep mysql
root     30931 29955  0 17:14 pts/3    00:00:00 grep mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf &
[1] 30934
[root@linfytest3 ~]# 150928 17:15:08 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:15:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307

7、再次修改root密碼:
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
修改成功！
  修改密碼的第二種方法：
 使用SQL語句修改:
 mysql> update user set password=password('111111') where user='root';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

 
 
  

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

相關文章