-
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
自身提供的工具也好,亦或是第三方提供的工具也罢,因为本身就写死了逻辑,因此在有些场景下依旧存在局限性,因此有时咱们也需要写自动化脚本,以此来完成一些特殊的需求。