ubuntu系统mysql5.7 root 用户误删，该怎么恢复。

最新推荐文章于 2021-03-16 21:13:39 发布

。Flying Fish� ·

最新推荐文章于 2021-03-16 21:13:39 发布

阅读量754

点赞数 1

分类专栏： mysql数据库文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/weixin_44601149/article/details/104672086

版权

mysql数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql 的一些小命令，待会用的到

查询当前登陆用户以及对应的host：select user();
查询当前是哪一个用户登陆：select current_user();
展示所有数据库：show databases;
展示选定数据库中的所有表：show tables;
查询user表中的用户及用户远程连接权限：select user,host from mysql.user;
查询用户具有哪些权限：show grants for 'name'@'host';

现场演示，请一定要看到最后

我刚把root用户删了

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| smsyu            | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> drop user 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| smsyu            | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> quit;
Bye
root@flyingfish:/etc/mysql/mysql.conf.d# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

重新登陆提示Access denied for user ‘root’@‘localhost’ (using password: YES)

ubuntu MySQL57配置文件

目录地址
/etc/mysql/mysql.conf.d/mysqld.cnf
修改配置文件
- vim mysqld.cnf
  在配置文件中的mysqld下面加上一句skip-grant-tables（跳过密码验证），然后保存退出

[mysqld]
skip-grant-tables

重启mysql服务
sudo service mysql restart

root@flyingfish:/etc/mysql/mysql.conf.d# sudo service mysql restart

连接mysql
不用密码直接登陆，-p之后敲回车，然后不用输入东西，直接再敲回车。成功进入mysql

root@flyingfish:/etc/mysql/mysql.conf.d# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu)

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

给用户修改权限

报–skip-grant-tables 错误，则使用flush privileges;刷新权限即可。
此时用户表里是没有root用户的，使用grant all privileges on . to root@“localhost”;是不能给用户添加权限的，所以会报错。

mysql> grant all privileges on *.* to root@"localhost";
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> grant all privileges on *.* to root@"localhost";
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>

添加用户并给用户添加权限

可以使用grant all privileges on . to ‘用户’@‘远程权限’ identified by ‘mypassword’;
all privileges 代表数据库的所有权限
on 后面第一个 * 号代表所有数据库
on 后面第二个 * 号代表所有表
这里我创了两个root，一个是本地所有权限，另一个是所有ip均可连接的所有权限。怕以后误删了。

 grant all privileges on *.* to 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| smsyu            | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

试试成功了没有

退出
打开配置文件，把skip-grant-tables注释掉，然后保存退出配置文件

[mysqld]
# skip-grant-tables

重启mysql
sudo service mysql restart
连接mysql，密码输入刚才设置的，我是123456.

root@flyingfish:/etc/mysql/mysql.conf.d# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DjangoProject      |
| PLEASE_READ_ME_XMG |
| indeco             |
| mysql              |
| performance_schema |
| qiche              |
| sys                |
| wangshangyuan      |
+--------------------+
9 rows in set (0.00 sec)

mysql>

成功

下面的内容是扩展

创建用户

CREATE USER ‘user1’@’%’ IDENTIFIED BY ‘mypassword’;
此时该用户下只有对information_schema（mysql自带的数据库，不知道有什么用）的只读权限。

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'qwer';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
ERROR 1046 (3D000): No database selected
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| smsyu            | %         |
| user1            | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql>

登录user1用户并查看该用户下的数据库。

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

修改用户密码

重新登陆root用户，修改user1用户的密码
有两种修改方式
ALTER USER user1@’%’ IDENTIFIED BY ‘111111’;
update mysql.user set authentication_string=password(“222222”) where User=“user1” and Host="%";
亲测两种都能修改。

mysql> ALTER USER user1@'%' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.user set authentication_string=password("222222") where User="user1" and Host="%";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

给用户添加权限扩展

这里是没有采用 identified by这个参数的，所以下面都是对已经存在的用户进行权限修改，如果加上则是新建用户并添加权限。
创建用户上面已经说了，就不再写了。

grant select on 数据库名字.* to 'user1'@'localhost';  /*给予查询权限*/
grant insert on 数据库名字.* to 'user1'@'localhost'; /*添加插入权限*/
grant delete on 数据库名字.* to 'user1'@'localhost'; /*添加删除权限*/
grant update on 数据库名字.* to 'user1'@'localhost'; /*添加修改权限*/
flush privileges; /*刷新权限*/

报错

当我试图给某一个用户，只添加一个数据库的权限时。

mysql> grant all privileges on indeco.* to 'smsyu'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'indeco'

原因

root用户的Grant_priv和Super_priv，都必须为Y.

select user,host,Grant_priv,Super_priv from mysql.user;
+------------------+-----------+------------+------------+
| user             | host      | Grant_priv | Super_priv |
+------------------+-----------+------------+------------+
| mysql.session    | localhost | N          | Y          |
| mysql.sys        | localhost | N          | N          |
| root             | localhost | N          | Y          |
| debian-sys-maint | localhost | Y          | Y          |
| root             | %         | N          | Y          |
| smsyu            | %         | N          | N          |
+------------------+-----------+------------+------------+
6 rows in set (0.00 sec)

解决方法
将N修改为Y即可

mysql> update mysql.user set Grant_priv='Y',Super_priv='Y' where user = 'root' and host = '%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update mysql.user set Grant_priv='Y',Super_priv='Y' where user = 'root' and host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,Grant_priv,Super_priv from mysql.user;
+------------------+-----------+------------+------------+
| user             | host      | Grant_priv | Super_priv |
+------------------+-----------+------------+------------+
| mysql.session    | localhost | N          | Y          |
| mysql.sys        | localhost | N          | N          |
| root             | localhost | Y          | Y          |
| debian-sys-maint | localhost | Y          | Y          |
| root             | %         | Y          | Y          |
| smsyu            | %         | N          | N          |
+------------------+-----------+------------+------------+
6 rows in set (0.00 sec)

mysql> grant all privileges on indeco.* to 'smsyu'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'indeco'
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on indeco.* to 'smsyu'@'%';
Query OK, 0 rows affected (0.03 sec)

登陆smsyu用户发现indeco数据库已经可以操作了。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| indeco             |
+--------------------+
2 rows in set (0.00 sec)

文章可以引用，可以转载，但一定要注明出处
希望能帮到大家，最后提醒大家，关于数据库一定要小心小心再小心。

。Flying Fish� ·

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
ubuntu系统mysql5.7 root 用户误删，该怎么恢复。

mysql 的一些小命令，待会用的到查询当前登陆用户以及对应的host：select user();查询当前是哪一个用户登陆：select current_user();展示所有数据库：show databases;展示选定数据库中的所有表：show tables;查询user表中的用户及用户远程连接权限：select user,host from mysql.user;查询用户具有哪...
复制链接

扫一扫