root 权限 databases mysql 权限丢失
今天同事,调整 mysql 安全,在删除用户的时候误删了 [email protected],后来加进去了,但是在登录发现数据库很多库都没有了,顿时懵逼,考虑恢复数据,稍后我看到了,想起当年也出现过类似情况,主要是因为 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.bubuko.com/infodetail-2047053.html