mysql数据库root权限丢失（密码丢失）

今天同事，调整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.sock4、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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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表的所有字段中为Ｎ的为Ｙ就可以了。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';