在centOS上的mysql命令行中,修改了mysql库的user表,flush privileges命令之后,发现外网也访问不了,内网也没法访问了,解决办法如下(需要root权限登录Linux):
1、用安全模式访问mysql
#service mysql stop 停止mysql服务
#mysqld_safe –skip-grant-tables & 后台运行MySQL安全模式(可以无任何限制得操作MySQL)
#mysql -u root -p mysql 登录
2、修改user表
mysql>use mysql;
mysql>select user,password,host from user; 查看用户表的用户名,密码,还有主机地址
(注意使用select *from user查看用户的所有权限)
找到你需要修改的那一行的用户,用update语句来更新权限,比如我想更新localhost主机的权限:
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' where user='root' and host='localhost';
3、刷新MySQL权限
mysql>flush privileges;
mysql>exit;
#service mysqld restart