退出mysql root账户_解决“把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

# 查看可用账户usemysql;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