mysql数据库中管理员用户(root@localhost)被误删除，怎么恢复

来源：互联网发布：mac如何移除桌面图标编辑：程序博客网时间：2024/05/21 14:50

正常状态：

[root@web01 data]# mysql-uroot -p123456

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

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 75

Server version: 5.6.34Source distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.

mysql> selectuser,host from mysql.user;

+-----------+--------------------------+

| user | host |

+-----------+--------------------------+

| root | 127.0.0.1 |

| wordpress |172.16.1.0/255.255.255.0 |

| root | ::1 |

| | localhost |

| root | localhost |

| | web01 |

| root | web01 |

+-----------+--------------------------+

7 rows in set (0.00 sec)

管理员用户被误删除后：

mysql> drop userroot@localhost;

Query OK, 0 rowsaffected (0.07 sec)

mysql> selectuser,host from mysql.user;

+-----------+--------------------------+

| user | host |

+-----------+--------------------------+

| root | 127.0.0.1 |

| wordpress |172.16.1.0/255.255.255.0 |

| root | ::1 |

| | localhost |

| | web01 |

| root | web01 |

+-----------+--------------------------+

6 rows in set (0.00 sec)

解决：

需要使用root@127.0.0.1用户登录系统(无密码)，再创建root@localhost

[root@web01 data]# mysql-h 127.0.0.1 -uroot -p

Enter password:

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 80

Server version: 5.6.34Source distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.

mysql>

mysql> selectuser,host,password from mysql.user;

+-----------+--------------------------+-------------------------------------------+

| user | host | password |

+-----------+--------------------------+-------------------------------------------+

| root | web01 | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | web01 | |

| wordpress |172.16.1.0/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-----------+--------------------------+-------------------------------------------+

6 rows in set (0.00 sec)

mysql> grant all on*.* to root@localhost identified by '123456' with grant option; #创建被删除的用户，并赋予超级权限

Query OK, 0 rowsaffected (0.00 sec)

mysql> flushprivileges;

Query OK, 0 rowsaffected (0.00 sec)

mysql> quit

Bye

恢复正常

[root@web01 data]# mysql-uroot -p123456

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

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 81

Server version: 5.6.34Source distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.

mysql> selectuser,host from mysql.user;

+-----------+--------------------------+

| user | host |

+-----------+--------------------------+

| root | 127.0.0.1 |

| wordpress |172.16.1.0/255.255.255.0 |

| root | ::1 |

| | localhost |

| root | localhost |

| | web01 |

| root | web01 |

+-----------+--------------------------+

7 rows in set (0.00 sec)

欢迎加入qq群：202932777，一起交流，一起进步！！！

阅读全文

0 0