企业误删除数据库用户故障解决【显哥出品，必为精品】

1.误删除所有用户

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | db01      |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

mysql> delete from mysql.user where 1=1;
Query OK, 3 rows affected (0.00 sec)

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

2.用户删除后

#所有用户删除之后还是可以登录的，使用root登录，grant授权用户，恢复用户
[root@db01 ~]# mysql -uroot -p123

mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec

3.删除所有用户后重启

#删除所有用户后重启，无法登录
[root@db01 ~]# 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)

[root@db01 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

解决方法一：

1）重启过数据库

#1.停止数据库
[root@db01 ~]# systemctl stop mysql

#2.跳过授权表启动数据库
[root@db01 ~]# mysqld_safe --skip-grant-tables &

只跳过授权表，任意一台服务器都能连接到你的数据，不安全

#3.跳过授权表和网络启动数据库
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

2）创建用户

#1.切换到MySQL库
mysql> use mysql

#2.插入一条用户数据
mysql> insert into user(user,host,password) values('root','localhost',PASSWORD(123));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
#ssl_cipher该字段必须有默认值

#3.查看表结构
mysql> desc user;

#4.重新插入数据（这么插入数据的权限全都是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)

#5.应该这么插入数据
mysql> delete from mysql.user where 1=1;
Query OK, 1 row affected (0.01 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');

3）重启数据库测试

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

解决方法二

1）授权新用户

#1.跳过授权表启动并登录

#2.授权一个用户
mysql> grant all on *.* to root@'localhost' identified by '123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#刷新的是mysql系统权限相关的表
mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#使用该命令授权，该用户缺少授权的权限

#查看用户权限
mysql> select * from user\G

2）给用户授权权限

#方法一：修改授权权限为Y
mysql> update mysql.user set Grant_priv='Y' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#方法二：授权的同时，指定有授权权限
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)

3）重启数据库

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