Linux下Mysql root用户失去特权怎么办
问题如下:
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> use mysql; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql' mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec) mysql>
解决办法:
1、编辑MySQL配置文件
# vi /etc/my.cnf
添加skip-grant-tables
2、重启MySQL服务
# systemctl restart mysqld
3、进入MySQL免认证模式
[root@localhost ~]# mysql -u root -p Enter password: 直接敲回车 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
4、输入以下代码恢复超级权限
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> update user set Update_priv='Y' where user='root'; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> update user set Grant_priv='Y' where user='root'; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> exit Bye
5、恢复
# vi /etc/my.cnf
去除skip-grant-tables
重启
# systemctl restart mysqld