mysql用户管理与备份

时间：2023-11-26 20:02:56

用户管理

我们知道在Mysql中root用户是最高权限的用户，其他用户的创建和权限授予都是通过root用户来操作的

查看用户

在root用户界面下

select user,host,password from mysql.user;

```sql
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
+------+-----------+-------------------------------------------+
```


## 创建用户
### 指定ip和用户名登录

指定ip为192.168.1.17的mm用户登录

create user 'mm'@'192.168.1.17' identified by '123';

identified by 'password'表示为创建的用户设置密码，可以省略

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

| user | host         | password                                  |

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

| root | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root | 127.0.0.1    |                                           |

| root | ::1          |                                           |

|      | localhost    |                                           |

| mm   | 192.168.1.17 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

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

### 指定ip地址段和用户名登录

指定ip地址段的mm用户登录

create user 'mm'@'192.168.1.%' identified by '123';

```sql
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| mm | 192.168.1.17 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mm | 192.168.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+--------------+-------------------------------------------+

<br>

### 如果不指定ip，则默认是所有的ip都可以登录

```sql

create user 'hh' identified by '123';

```sql
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| mm | 192.168.1.17 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mm | 192.168.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| hh | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+--------------+-------------------------------------------+



注意：mysql识别用户是通过用户名和ip联合识别的，其中<b><font color='#ff0000'>localhost和127.0.0.1被认为是两个不同的ip</font></b>，也就是说，mm@'localhost'和mm@'127.0.0.1'是两个不同的用户！

<br>

## 删除用户

删除用户不能直接对mysql.user表进行操作，而是使用drop语句

```sql

drop user 'mm'@'192.168.1.17';

drop user 'hh'; # host为%时，可以省略不写

## 修改用户（用户名和ip)
语法为
```sql
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

修改mm用户的登录ip为192.168.1.111

```sql

rename user 'mm'@'192.168.1.%' to 'gf'@'192.168.1.111';

```sql
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| gf | 192.168.1.111 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| sh | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ss | % | |
+------+---------------+-------------------------------------------+

<br>

## 修改密码

语法

```sql

SET PASSWORD FOR '用户名'@'IP地址' = PASSWORD('新密码');

修改gf用户的密码为456
```sql
set password for 'gf'@'192.168.1.111'=password('456');
```


```sql
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| gf | 192.168.1.111 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
| sh | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ss | % | |
+------+---------------+-------------------------------------------+
```

查看权限

语法

show grants for '用户'@'IP地址'

查看root用户的权限

show grants for 'root'@'localhost';


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

| Grants for root@localhost

                                                      |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

                                                      |

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

可以看到root用户拥有所有(最高)权限

查看gf用户权限

show grants for 'gf'@'192.168.1.111';


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

| Grants for gf@192.168.1.111

                             |

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

| GRANT USAGE ON *.* TO 'gf'@'192.168.1.111' IDENTIFIED BY PASSWORD '*531E182E2F72080AB0740FE2F2D689DBE0146E04' |

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

授权

语法

grant 权限1,权限2,权限3 on 数据库名.表名 to "用户"@'IP地址';

授予gf用户查询，插入，更新权限

grant select ,insert,update on *.*to "gf"@'192.168.1.111';

授予用户所有权限

grant all privileges  on 数据库名.表名 to "用户"@'IP地址';

给sh用户授予mysql库的所有权限

grant all privileges on mysql.* to 'sh';

注意：

1）授权后需要重新登录才生效

2）不能对同一个用户，多次授权不同的权限。否则会有多条授权规则，最终以最严格的权限为准。

取消权限

语法

revoke 权限1 on 数据库名.表名 from "用户"@'IP地址';

取消gf用户对mysql库的多有表的更新权限

revoke update on *.* from 'gf'@'192.168.1.111';

取消所有权限

revoke all privileges on *.* from 'gf'@'192.168.1.111';

注意：取消的权限必须与授予的权限完全一致，不能取消部分权限

## 备份
### 备份数据和表结构
在命令行下
```
mysqldump -u root -p db1 > db1.sql
```

仅备份表结构

mysqldump -u root -p -d db1 > db2.sql

恢复

登录mysql，创建数据库

create database db10;

退出mysql，在命令行下将备份导入db10数据库里

mysqldump -u root -p -d db10 < db1.sql

提示Dump completed表示导入成功

相关文章