解决“把Linux系统mysql唯一root用户的所有权限都给撤销了，导致登录了root账户无法进行任何操作”的问题

写这篇博客记录一下，以免下次犯蠢~~~

起因：在Linux安装了mysql，因为嫌账户太多，把mysql库user表中的其他用户全部删除，只剩下root@%用户，结果还手贱去运行了下面这条指令，撤销了root的所有权限，导致登录了root账户后无法对数据库进行任何操作，已运行指令就报错（root@%用户没有操作权限），也没办法新建其他账户，悔得肠子都青了，千万别手贱！！！

revoke all on *.* from ‘root’@’%’;　

 

 

解决方法：

在网上看到有大神的解决方法，亲试有效！

1、停止mysql服务（service mysqld stop）；

2、在mysql安装目录下找到my.cnf；在mysqld.cnf中找到以下片段[mysqld]；另起一行加入代码：skip-grant-tables 并保存

具体方法：寻找my.cnf位置（ locate my.cnf ），比如我的在：/etc/my.cnf，直接 vim /etc/my.cnf 进入，找到[mysqld]片段后，按i进入插入模式，另起一行加入代码：skip-grant-tables ，完成后按【esc】键，:wq退出并保存

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
skip-grant-tables

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log

3、启动mysql服务（service mysqld start），并登录

3.1 有用户的情况：用用户名密码登录

3.2 无用户的情况：直接可登录(无用户名和密码)，然后加入root用户

INSERT INTO user (Host,User,Password) VALUES( 'localhost', 'root ',password( '123456 '));

4、root用户设置权限

update user set Host='%',select_priv='y', insert_priv='y',update_priv='y',Alter_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',create_user_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' where user='root';commit;

5、退出mysql，把my.conf刚才加入的那行删除并重启服务

6、最后用root用户登录就可以了

 

PS：其他相关指令

# 查看账户权限
show grants; --自己
show grants for dba@localhost； --指定用户指定host
# 查看可用账户
use mysql;
select user,host,password from user;
# 生成用户，并给用户授权
grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option; --授予所有权限给root@%账户，密码为password，并且有给其他用户授权的权限
flush privileges;--生效
# 删除用户
delete from mysql.user where user='' and host='';
# 更新用户密码
update mysql.user set password=PASSWORD('111111') where user='root';

 

参考大神：https://www.cnblogs.com/756623607-zhang/p/11461548.html

https://blog.csdn.net/qq_24571549/article/details/74439474