不小心删除了用户

1.查看用户

mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db02 |
| root | db02 |
| | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

#2.误删除用户
mysql> delete from mysql.user where 1=1;
Query OK, 6 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
Empty set (0.00 sec)

#3.删除所有用户后，仍然可以登录，重启后就不能登录了
[root@db02 ~]# systemctl restart mysqld
[root@db02 ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决方案一：

1）停止数据库

[root@db02 ~]# systemctl stop mysqld

2）跳过授权表和网络启动数据库

#1.只跳过授权表启动 #危险随便一个都可以连接数据库
[root@db02 ~]# mysqld_safe --skip-grant-tables &

#2.跳过授权表和网络启动
[root@db02 ~]# mysqladmin shutdown
[root@db02 ~]# mysqld_safe --skip-grant-tables --skip-networking &

3）插入一个用户

#插入用户可以成功，但是用户权限全是N，没有任何权限的用户没有用

mysql> insert into user(user,host,password,ssl_cipher,x509_issuer,x509_subject) values('root','localhost',PASSWORD('123'),'','','');
Query OK, 1 row affected (0.00 sec)

#正确方法

mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');

#查看插入用户的权限

mysql> select * from mysql.user\G

4）正常启动数据库

[root@db02 ~]# mysqladmin shutdown
[root@db02 ~]# systemctl start mysqld
[root@db02 ~]# mysql -uroot -p123

解决方案2：

1）停止数据库

[root@db02 ~]# systemctl stop mysqld

2）跳过授权表和网络启动数据库

#1.只跳过授权表启动
[root@db02 ~]# mysqld_safe --skip-grant-tables &

#2.跳过授权表和网络启动
[root@db02 ~]# mysqladmin shutdown
[root@db02 ~]# mysqld_safe --skip-grant-tables --skip-networking &

3）插入一个用户

#在授权时直接加上参数包含 grant 权限
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)

4）正常启动数据库