mysql中root用户误删的处理办法

    在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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/543979/viewspace-1811280/