mysql权限丢失

怼烎@ 2022-08-19 11:17 212阅读 0赞

ubuntu系统xampp环境下，mysql的root账号权限丢失，即root账号没有任何database的访问权限，mysql>show databases 只显示information\_schema和test。

解决方法：进入mysql安全模式，修改root权限。

首先关闭mysql服务 /opt/lampp/lampp stop

进入安全模式：

打开一个命令行终端：/opt/lampp/sbin/mysqld --skip-grant-tables  (该命令行终端不要关闭)

打开另一个命令行终端：mysql -u root

修改root权限：

use mysql;

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';

最好更新下密码，最后刷新下权限

update user set Password=password('newPassword') where user="root";
    
    flush privileges;

关闭第一个打开安全模式的终端，启动lampp：/opt/lampp/lampp start   。结束