共计 3206 个字符,预计需要花费 9 分钟才能阅读完成。
调整了 MySQL 安全,在删除用户的时候误删了 root@localhost,后来加进去了,但是在登录发现数据库很多库都没有了,顿时懵逼,考虑恢复数据,稍后我看到了,想起当年也出现过类似情况,主要是因为 root 用户没有权限,所以跳过授权表,经过查询权限,真的缺少,所以将其都由 N 改为 Y,在次测试,OK,以下是处理步骤。
第一步,跳过授权表登录 mysql
1、pkill 停止 实例进程
2、mysqld_safe --defaults-
file
=
/data/3306/my
.cnf --skip-grant-tables &
3、mysql -S
/data/3306/mysql
.sock
4、UPDATE mysql.user SET password=PASSWORD(
'oldboy123'
) WHERE user=
'root'
and host=
'localhost'
;
5、flush privileges;
6、
kill
掉 mysqld_safe 进程
7、
/etc/init
.d
/msyql
start 正常启动
登录即可 mysql -uroot -poldboy123
第二步、修改权限( 也可以初始化 root 密码 )
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>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy_gbk |
| performance_schema |
| qiuyuetao |
|
test
|
+--------------------+
6 rows
in
set
(0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows
in
set
(0.00 sec)
更新的 MYSQL.USER 表的所有字段中为N的为Y就可以了。
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'
;
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-04/143270.htm
正文完
星哥玩云-微信公众号