mysql root权限修改、恢复

最新推荐文章于 2024-08-21 17:48:26 发布

hijiankang

最新推荐文章于 2024-08-21 17:48:26 发布

阅读量1.3w

点赞数

分类专栏：数据库

数据库专栏收录该内容

27 篇文章 0 订阅

订阅专栏

原文地址：http://blog.csdn.net/kaly3845/article/details/8808529

当root权限失去某些权限后，如何恢复这个权限？

1. 可以进入数据库，但无update权限：

操作过程：在WINDOWS的DOS窗口下，进入MYSQL安装目录的BIN目录，
                    运行mysqld-nt --skip-grant-tables，当前窗口将会停止。
                    另外打开一个命令行窗口，执行mysql 如果提示没有这个命令，先进入MYSQL的安装位置下BIN目录再运行mysql
                    >use mysql
                     >update user set Update_priv ='Y' where user = 'root';

如果root 没有所有数据的权限

更新的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';

权限问题最后要保证“ mysql.user”表中 host为“ localhost”和“ %”都有 root的所有权限。

         host列是指定登录的ip，比如说 user=root host=192.168.1.1 ，这里的意思就是说，root用户只能通过 192.168.1.1的客户端去访问，而%则是个通配符， 如果host=192.168.1.%，那么就表示 只要是 ip为host=192.168.1.前缀的客户端都可以连接，那么host=%的话则表示所有ip都有权去连接，这也就是为什么在开启远程连接的时候，大部分人都直接把host改成%的缘故，因为省事

2. 忘记密码，无法连接数据库：

解决办法：

同样的，利用命令行，跳过mysql的服务认证

>cd ../bin //进入Mysql路径下的bin目录中

>mysqld-nt --skip-grant-tables //让mysql服务跳过认证运行,保持这个窗口不关闭

（|>bin/safe_mysqld --skip-grant-tables & //Linux下）

>mysql -uroot //另打开一个shell,执行Mysql

>use mysql //切换数据库为mysql

>update user set password=password("new_pass") where user="root"; //更新用户密码

>flush privileges; //更新Mysql权限表

>exit //退出Mysql

>exit //关闭shell