mysql用户管理与备份
用户管理
我们知道在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表示导入成功
