MySQL用户与权限管理

• create user 用户名@'IP' identified by 密码;：创建一个新用户。

• drop user 用户名@'IP';：删除某个用户。

• set password = password(新密码);：为当前用户设置新密码。

• set password for 用户名 = password(新密码);：为指定用户设置新密码（需要权限）。

• alter user 用户名@'IP' identified by 新密码;：使用root账号修改密码。

• mysqladmin -u用户名 -p旧密码 password 新密码;：使用mysqladmin工具更改用户密码。

• rename user 原用户名 to 新用户名;：对某个用户重命名。

• show grants;：查看当前用户拥有的权限。

• show grants for 用户名;：查看指定用户拥有的权限。

• grant 权限1,权限2... on 库名.表名 to 用户名;：为指定用户授予权限。

• *.*：全局权限，表示该用户可对所有库、所有表进行增删改查操作。

• 库名.*：单库权限，表示该用户可对指定库下的所有表进行增删改查操作。

• 库名.表名：单表权限，表示该用户可对指定表进行增删改查操作。

• insert：插入表数据的权限。

• delete：删除表数据的权限。

• update：修改表数据的权限。

• select：查询表数据的权限。

• alter：修改表结构的alter权限。

• alter routine：修改子程序（存储过程、函数、触发器）的alter权限。

• create：创建表的create权限。

• create routine：创建存储过程、存储函数、触发器的权限。

• create temporary tables：创建临时表的权限。

• create user：创建/删除/重命名/授权用户的权限。

• create view：创建视图的权限。

• drop：删除表的权限。

• execute：执行存储过程的权限。

• file：导出、导入表数据的权限。

• index：创建和删除索引的权限。

• lock tables：获取表锁的权限。

• process：查询工作线程的权限。

• references：这个在MySQL中没有。

• reload：请空表的权限。

• replication clinet：获取主节点、从节点地址的权限。

• replication slave：复制主节点数据的权限。

• show databases：查看所有数据库的权限。

• show view：查看所有视图的权限。

• shutdown：关闭数据库服务的权限。

• super：修改主节点信息的权限。

• all privileges：所有权限。

• 权限可选项：

• usage：不授予这些权限。其他权限全部授予。

• grant option：授予这些权限，其他权限全部不授予。

• 权限范围可选项：

• revoke 权限1,权限2... on 库名.表名 from 用户名;：撤销指定用户的指定权限。

• revoke all privileges from 用户名 with grant option;：撤销一个用户的所有权限。

• flush privileges;：刷新权限。

• select user,password,host from mysql.user;：查询当前库中的所有用户信息。

• MySQL8.0版本后推出的密码管理机制：

• set persist default_password_lifetime=90;：设置所有用户的密码在90天后失效。

• create user 用户@IP password expire interval 90 day;：创建用户时设置失效时间。

• alter user 用户名@IP password expire interval 90 day;：设置指定用户密码失效。

• alter user 用户名@IP password expire never;：设置指定用户的密码永不失效。

• alter user 用户名@IP password expire default;：使用默认的密码失效策略。上述给出了一系列的用户管理和权限管理的命令，最后稍微提一下创建用户时的注意事项：```sql

-- 创建一个名为 zhuzi 的用户 create user 'zhuzi'@'196.xxx.xxx.xxx' identified by "123456";

在创建用户时需要在用户名称后面跟一个`IP`地址，这个`IP`的作用是用来限制登录用户的机器，如果指定为具体`IP`，则表示只能由该`IP`的机器登录该用户，如果写`%`表示任意设备都能使用该用户名登录连接。
>同时也最后提一嘴，`MySQL`对于所有的用户信息，都会放在自带的`mysql`库的`user`表中存储，因此也可以对表执行`insert、delete、update、select`操作，来实现管理用户的功能。

## 九、MySQL视图与临时表
-`create view 视图名 as select ...;`：对查询出的结果集建立一个指定名称的视图。
-`select * from 视图名;`：基于某个已经创建的视图查询数据。
-`show create view 视图名;`：查看某个已存在的视图其详细信息。
-`desc 视图名;`：查看某个视图的字段结构。
-`alter view 视图名(字段1,...) as select 字段1...;`：修改某个视图的字段为查询字段。
-`drop view 视图名;`：删除某个视图。
-`create temporary table 表名(....);`：创建一张临时表（方式1）。
-`create temporary view 表名 as select ...;`：创建一张临时表（方式2）。
-`truncate table 临时表名;`：清空某张临时表的数据。

`MySQL`的临时表本质上是一种特殊的视图，被称为不可更新的视图，也就是临时表只支持查询数据，不支持增删改操作，因此也可以通过创建视图的方式创建临时表，在创建语句中加入`temporary`关键字即可，不指定默认为`undedined`，意思是自动选择视图结构，一般为`merge`结构，表示创建一个支持增删改查的视图。

## 十、数据的导出、导入与备份、还原
数据库的备份其实本质上就是指通过导出数据的形式，或者拷贝表文件的方式来制作数据的副本，数据恢复/还原即是指在数据库故障、异常、错误的情况下，通过导入原本的数据副本，将数据恢复到正常状态，下面来介绍`MySQL`中提供的相关命令。
```sql
-- --------使用 mysqldump 工具做数据的逻辑备份（导出的是sql语句）-----------
-- 导出MySQL中全部的库数据（使用--all-databases 或者 -A 参数）
mysqldump -uroot -p密码 --all-databases > 备份文件名.sql

-- 导出MySQL中一部分的库数据（使用--databases 或者 -B 参数）
mysqldump -uroot -p密码 --databases > 备份文件名.sql

-- 导出MySQL单库中的一部分表数据
mysqldump –u 用户名 –h主机名 –p密码 库名[表名1,表名2...]> 备份文件名.sql

-- 导出MySQL单表的部分数据（使用 --where 参数）
mysqldump -u用户名 -p 库名 表名 --where="条件" > 备份文件名.sql

-- 排除某些表，导出库中其他的所有数据（使用 --ignore-table 参数）
mysqldump -u用户名 -p 库名 --ignore-table=表名1,表名2... > 备份文件名.sql

-- 只导出表的结构（使用 --no-data 或者 -d 选项）
mysqldump -u用户名 -p 库名 --no-data > 备份文件名.sql

-- 只导出表的数据（使用 --no-create-info 或者 -t 选项）
mysqldump -u用户名 -p 库名 --no-create-info > 备份文件名.sql

-- 导出包含存储过程、函数的库数据（使用--routines 或者 -R选项）
mysqldump -u用户名 -p -R --databases 库名 > 备份文件名.sql

-- 导出包含事件（触发器）的库数据（使用 --events 或者 -E选项）
mysqldump -u用户名 -p -E --databases 库名 > 备份文件名.sql


-- --------使用 mysql 工具来恢复备份的数据（导入xx.sql文件执行）-----------
-- 恢复库级别的数据（包含了建库语句的情况下使用）
mysql -u用户名 -p < xxx.sql

-- 恢复库中表级别的数据
mysql -u用户名 -p 库名 < xxx.sql


-- ----------以物理形式备份数据（导出的是表数据） ------------
-- 查看数据库导出数据的路径（如果没有则需在`my.ini/my.conf`中配置）
show variables like '%secure_file_priv%';

-- 导出一张表的数据为txt文件（使用 select ... into outfile 语句）
select * from 表名 into outfile "备份文件名.txt";

-- 导出一张表的数据为txt文件（使用 mysql 工具）
mysql -u用户名 -p --execute="select ...;" 库名 > "数据存放目录/xxx.txt"

-- 导出一张表的结构和数据为sql、txt文件（使用 mysqldump -T 的方式）
mysqldump -u用户名 -p -T "数据存放目录" 库名 文件名

-- 导出一张表的数据为txt文件，以竖排形式存储（使用 mysql –veritcal 的方式）
mysql -u用户名 -p -veritcal --execute="select ...;" 库名 > "数据存放目录/xxx.txt"

-- 导出一张表的数据为xml文件（使用 mysql -xml 的方式）
mysql -u用户名 -p -xml --execute="select ...;" 库名 > "数据存放目录/xxx.xml"


-- -----------通过物理数据文件恢复数据----------------
-- 使用load data infile 的方式导入.txt 物理数据
load data infile "数据目录/xxx.txt" into table 库名.表名;

-- 使用 mysqlimport 工具导入xxx.txt物理数据
mysqlimport -u用户名 -p 库名 '数据存放目录/xxx.txt' 
    --fields-terminatedby=',' 
    --fields-optionally-enclosed-by='\"'

-- 使用 mysqldump 工具迁移数据
mysqldump –h 地址1 –u用户名 –p密码 –-all-databases | mysql –h地址2 –u用户名 –p密码

上述列出了一系列数据导出导入、备份恢复、迁移等命令，这些都是MySQL自身就支持的方式，但这些自带的命令或工具，在一些情况下往往没有那么灵活、方便，因此在实际情况下，可以适当结合第三方工具来完成，比如：

• 较大的数据需要做物理备份时，可以通过xtrabackup备份工具来完成。

• MySQL5.5版本之前的MyISAM表，可以通过MySQLhotcopy工具做逻辑备份（速度最快）。

• 不同版本的MySQL可以使用XtraBackup备份工具来做数据迁移。

• MySQL、Oracle之间可以通过MySQL Migration Toolkit工具来做数据迁移。

• MySQL、SQL Server之间可以通过MyODBC工具来做数据迁移。

当然，无论是MySQL自身提供的工具也好，亦或是第三方提供的工具也罢，因为本身就写死了逻辑，因此在有些场景下依旧存在局限性，因此有时咱们也需要写自动化脚本，以此来完成一些特殊的需求。