mysql用户被删除_Mysql用户误删除导致无法进入数据库

Mysql用户误删除

管理员误删除mysql的所有用户，数据库并且重启导致无法进入数据库

恢复步骤如下

查看数据库是否存活,并尝试登陆

[root@db02 data]# ps -ef |grep mysqld

mysql 7706 1 2 04:56 ? 00:00:00 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf

root 7733 6954 0 04:57 pts/0 00:00:00 grep --color=auto mysqld

[root@db02 data]#

[root@db02 data]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@db02 data]#

找到/etc/my.cnf 添加skip-grant-tables保存 让数据库跳过验证

[root@db02 mysql]# vim /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[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

从新启动mysql再进入数据库

[root@db02 mysql]# systemctl restart mysqld

[root@db02 mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> select user,host from mysql.user;

Empty set (0.00 sec)

mysql>

新建一个管理员用户

mysql> grant all on *.* to root@localhost identified by '1' with grant option;

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql>

#这里需要刷新一下权限才可以执行

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@localhost identified by '1' with grant option;

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)

mysql>

删除/etc/my.cnf文件中的skip-grant-tables并重启mysqld服务测试是否可以连接

[root@db02 mysql]# sed -ri 's#skip-grant-tables##g' /etc/my.cnf

[root@db02 mysql]# systemctl restart mysqld

[root@db02 mysql]# mysql -uroot -p1

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

以上是本次故障解决流程