root权限丢失解决方法

以root用户登录mysql，执行以下执行：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

没有mysql库、  performance_schema库的相关信息，再使用以下执行：

mysql> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

发现localhost没有匹配用户。

 

解决方法如下：

/etc/init.d/mysqld_3306 stop

打开mysql安装目录下的bin目录

cd  /usr/local/mysql3306/bin

 ./mysqld_safe --grant-skip-tables  //跳过mysql权限检查

 

再以root用户登录到mysql中，执行以下语句：

 

 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',Create_tablespace_priv='Y' where host='localhost';

 

关闭mysql:

/etc/init.d/mysqld_3306 restart

 

以root登录mysql执行以下语句：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

查看当前root用户权限：

mysql> show grants;
+-----------------------------------------------------------------+
| Grants for @localhost                                           |
+-----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ''@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------+