背景：
长时间的对着电脑，造成了"神志不清"，一不小心在Mysql下误操作了“revoke all on *.* from 'root'@'localhost'; ”，这个命令的意思就是删除mysql中最高权限的用户root的所有权限。因为本机mysql的密码为空，虽然依旧可以进去mysql，但再也不法执行修改操作。
解决方法：
1、首先杀掉mysql进程
#pkill mysql
2、以--skip-grant-tables方式启动
#/usr/local/mysql/bin/mysqld_safe --user mysql --skip-grant-tables&
3、更新的MYSQL.USER表的所有字段中为N的为Y
# /usr/local/mysql/bin/mysql 进入mysql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
>use mysql;
update
user
set
Select_priv =
'Y'
where
user
=
'root'
;
update
user
set
Insert_priv =
'Y'
where
user
=
'root'
;
update
user
set
Update_priv =
'Y'
where
user
=
'root'
;
update
user
set
Delete_priv =
'Y'
where
user
=
'root'
;
update
user
set
Create_priv =
'Y'
where
user
=
'root'
;
update
user
set
Drop_priv =
'Y'
where
user
=
'root'
;
update
user
set
Reload_priv =
'Y'
where
user
=
'root'
;
update
user
set
Shutdown_priv =
'Y'
where
user
=
'root'
;
update
user
set
Process_priv =
'Y'
where
user
=
'root'
;
update
user
set
File_priv =
'Y'
where
user
=
'root'
;
update
user
set
Grant_priv =
'Y'
where
user
=
'root'
;
update
user
set
References_priv =
'Y'
where
user
=
'root'
;
update
user
set
Index_priv =
'Y'
where
user
=
'root'
;
update
user
set
Alter_priv =
'Y'
where
user
=
'root'
;
update
user
set
Show_db_priv =
'Y'
where
user
=
'root'
;
update
user
set
Super_priv =
'Y'
where
user
=
'root'
;
update
user
set
Create_tmp_table_priv =
'Y'
where
user
=
'root'
;
update
user
set
Lock_tables_priv =
'Y'
where
user
=
'root'
;
update
user
set
Execute_priv =
'Y'
where
user
=
'root'
;
update
user
set
Repl_slave_priv =
'Y'
where
user
=
'root'
;
update
user
set
Repl_client_priv =
'Y'
where
user
=
'root'
;
update
user
set
Create_view_priv =
'Y'
where
user
=
'root'
;
update
user
set
Show_view_priv =
'Y'
where
user
=
'root'
;
update
user
set
Create_routine_priv =
'Y'
where
user
=
'root'
;
update
user
set
Alter_routine_priv =
'Y'
where
user
=
'root'
;
update
user
set
Create_user_priv =
'Y'
where
user
=
'root'
;
update
user
set
Event_priv =
'Y'
where
user
=
'root'
;
update
user
set
Trigger_priv =
'Y'
where
user
=
'root'
;
|
总结：
权限问题最后要保证“mysql.user”表中 host为“localhost”和“%”都有root的所有权限。
host列是指定登录的ip，比如说 user=root host=192.168.1.1 ，这里的意思就是说，root用户只能通过 192.168.1.1的客户端去访问，而%则是个通配符， 如果host=192.168.1.%，那么就表示 只要是 ip为host=192.168.1.前缀的客户端都可以连接，那么host=%的话则表示所有ip都有权去连接，这也就是为什么在开启远程连接的时候，大部分人都直接把host改成%的缘故，因为省事。
本文转自Jacken_yang 51CTO博客，原文链接：http://blog.51cto.com/linuxnote/1641488，如需转载请自行联系原作者
