MYSQL学习笔记09:备份还原,用户权限,外键

整库数据备份与还原

整库数据备份也叫sql数据备份:备份的结果都是sql指令

在mysql 中专门提供了一个专门用于备份sql的客户端:msyqldump.exe

应用场景

sql备份是一种mysql非常常见的备份与还原方式,sql备份不只是备份数据,还备份对应的sql指令(表结构):即便是数据库遭到毁灭性的破坏(数据库被删),那么利用sql备份依然可以实现数据还原.

sql备份因为需要备份结构:因此产生的备份文件特别大,因此不适合特大型数据备份,也不适应数据变化频繁型数据库.

应用方案

sql备份

sql 备份用到的是专门的备份客户端,因此还没与数据库服务器进行连接.

基本语法: mysqldump -hPup 数据库名字 [表1 [表2...]]  > 备份文件地址

备份有三种形式:

1. 整库备份:备份整个数据库

2. 单表备份:数据库后跟一张表

3. 多表备份:数据库后跟多张表

查看备份sql文件中的具体内容:

数据还原:

mysql提供了多种方式来实现

mysqldump备份的数据中没有关于数据库本身的操作,都是针对表级别:当进行数据还原的时候(sql还原),必须指定数据库.

1. 利用msyql客户端:没有登录之前,可以直接用该客户端还原

mysql -hPup 数据库 < 文件位置

2. 在sql指令,提供了一种导入sql指令的方式

source sql文件位置; //必须先进入到对应的数据库中

3. 人为操作:打开备份文件,复制所有sql指令,然后到mysql.exe客户端中粘贴执行.(不推荐)

用户权限管理

用户权限管理:在不同的项目中给不同的角色(开发者)不同的操作权限,为了保证数据库数据的安全.

通常一个用户的密码不会长期不不变,所以需要经常性的变更数据库用户密码来确保用户本身安全(mysql客户端用户)

用户管理

mysql需要客户端连接认证才能进行服务器操作:需要用户信息.

mysql中所有的用户信息都是保存在mysql数据库下的user表中.

默认的，在安装mysql的时候i，如果不选择创建匿名用户，那么意味着所有的用户只有1给：root超级用户

在mysql中，对用户的管理中，是由对应的host和user共同组成主键来区分用户。

user：代表用户的用户名。

host：代表本质是允许访问的客户端（ip或者主机地址）如果host使用*代表所有的（客户端）都可以访问。

创建用户

理论上来讲用两种方式创建用户：

1. 用root用户权限在mysql.user表中插入记录（不推荐）；

2. 专门创建用户的sql指令：

基本语法： create user 用户名 identified by '明文密码';//identified: 英[aɪˈdentɪfaɪd] 美[aɪˈdentɪfaɪd]

用户: 用户名@主机地址;

主机地址: '' 或 '%' //''为空 '%'  二者均代表任何主机即*,不限制主机名称.而主机localhost只能本机用户访问.

如上示例创建了1个不限制主机名称的用户'user1',密码为123456.我们查看mysql.user表中是否存在新增的用户:

现在我们简化创建用户(不推荐,因为不安全:谁都可以访问且不需要密码):

格式为:create user 用户名;

我们用新用户身份登录mysql:

如上,不需要密码即可登录mysql

删除用户

注意:mysql中user是带着host本身的(具有唯一性)

基本语法:drop user 用户名@host;

如上,删除了刚才创建的user2用户,由于user2创建时候没有@host(即任何主机名称),因此此处user2后可以不必加@host.

修改用户密码

mysql中提供了多种修改方式:基本上都必须使用对应提供的一个系统函数,password()

需要靠该函数对密码进行加密处理.

1. 使用专门修改密码的指令:

基本语法: set password for 用户 = password('新的明文密码');

修改后测试,使用新密码登录该用户:

2. 使用更新语句update修改表:(不推荐)

基本语法: update mysql.user set password = password('新的明文密码') where user ='??' and host='??';

权限管理

在mysql中将权限管理分为三类:

1. 数据权限:增删改查(select查, update改, delete删, insert增)

2. 结构权限:结构操作(create, drop)

3. 管理权限:权限管理(create user, grant, revoke):通常是管理员的权限

授予权限:

将权限分配给指定的用户

基本语法:grant 权限列表 on 数据库.表名 to 用户;//数据库和表名可以用*号通配符,则代表所有

权限列表:使用逗号分隔,但是可以用 all privileges, 代表全部权限.

数据库.表名:可以是单表(数据库.表),可以是具体数据库(数据库.*),也可以是整库(*.*)

用户user1被分配权限后即时生效,如下使用该用户身份查看数据库和表:

如上,由于仅为该用户分配了my_student表权限,故只能看到这一张表.(实际上库里有多张表)

我们测试一下试着使用该用户身份删掉my_student表,失败.因为该用户只有对该表的查看权限.

取消权限:revoke

权限回收:将权限从用户手中收回来.

基本语法: revoke 权限列表/all privileges on 数据库.表 from 用户; //数据库和表可用*通配符

 

以上示例收回该用户权限.

现在我们试着再以user1该用户身份查看,结果失败.如下:

刷新权限:flush

管理员更改对用户的操作权限后,虽然用户权限效果即时生效,但此时可能并没有立即同步(存储)到mysql系统表中...

flush:刷新.将当前对用户的权限操作,进行刷新.即将操作具体内容同步(存储/更新)到对应的mysql系统表中.

基本语法:flush privileges;

root密码丢失的解决方案:

1. 停止mysql服务;

2. 重新启动服务

mysqld --skip-grant-tables//启动服务器,跳过权限

3. 此时服务器没有权限概念:非常危险,任何客户端不需要任何用户信息都可以直接登录,而且是root权限.

4. 修改root用户的密码:指定root@localhost的密码

5. 赶紧关闭服务器,重启服务:

外键

外键概念:

如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键.由此可见,外键表示了两个关系之间的相关联系.以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表.外键又称作外关键字.

外键: foreign key 

一张表A中有一个字段,保存的值,指向另外一张表B的主键

B:主表 //本表主键

A:从表 //本表的外键不是本表主键,但却是B表的主键

外键的操作

增加外键

mysql提供了2种方式增加外键.

1.在创建表的时候增加外键

基本语法:在字段之后增加1条语句:

[constraint `外键名`] foreign key(外键字段) references 主表(主键);

constraint 限制,约束 英[kənˈstreɪnt] 美[kənˈstreɪnt]

references 参考,引用   英[ˈrefrənsɪz] 美[ˈrefrənsɪz]

MUL:多索引,外键本身是一个索引,外键要求外键字段本身也是一种普通索引.

如上,观察表结构.系统自动补全外键名等.

2.在创建表后增加外键

alter table 从表 add [constraint `外键名`] foreign key (外键字段) reference 主表(主键);

例如:现在我们想给学生表的class_id增加外键指向班级表中的class_id

设置外键生效,我们使用show create table命令查看表结构.如下:

修改外键(先删除再增加)

注意:外键不能修改!所以只能先删除,后增加!

删除外键

语法: alter table 从表 drop foreign key 外键名字;

如上,已删除外键,为什么class_id,为什么的key还是mul(多索引)?

删除外键时只会删除外键本身,不会删除创建外键时自动产生的索引.

如果想删除对应的索引: alter table 表名 drop index 索引名字;

外键基本要求

1. 外键字段需要保证与关联的主表的主键字段类型完全一致

2. 基本属性也要相同.

3. 如果是在表后增加外键,对数据还有一定的要求.(从表数据与主表的关联关系)

4. 外键只能使用innodb存储引擎,myisam存储引擎不支持外键.

外键约束

外键约束:通过建立外键关系后,对主表和从表都会有一定的数据约束效率.

约束的基本概念

1. 当一个外键产生时:外键所在的表(从表)会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作(不能插入主表不存在的数据)

2. 如果一张表被其他表外键引用,那么该表的数据操作就不能随意,必须保证从表数据的有效性(不能随便删除一个被从表引用的记录)

如上表1为从表,班级表为主表.我们打算对比插入正确/错误的两条数据.

因为主表(班级表)不存在4班,因此从表插入4班的数据错误.

现在我们想删除刚才插入从表的数据:

如上,删除主表class班级表数据时无法删除.这就是约束.

外键约束的概念

我们可以在创建外键的时候,对外键约束进行选择性的操作.

基本语法: add foreign key(外键字段) references 主表(主键) on 约束模式;

约束模式:

1. district: 严格模式.默认  --  不允许操作

2. cascade: 级联模式,一起操作  --  主表变化,从表数据跟着变化

3. set null: 置空模式  --  主表变化(删除) ,从表对应记录设置为空:前提是从表的外键字段字段允许为空

外键约束主要约束的是主表操作:从表不能插入主表不能存在的数据.

通常在进行约束的时候,需要指定操作: update和delete

常用的约束模式:on update cascade 更新级联,on delete set null 删除置空.

示例:我们给学生表添加指向班级表的外键,请仔细留意如下的约束命令

现在我们来查看约束关系,例如把主表班级表中的班级ID2改为4,看看会有什么样的效果:

如上,当我们把主表数据中班级ID从2班改成4班的时候,学生表中所有的2班均自动改为4班.这是因为外键约束on update cascade 更新级联,

删除模式:我们现在试着删除班级表中班级id为4的数据,如下:

如上,学生表中原班级ID为4的数据自动更新为null,这是因为外键约束on delete set null 删除置空.

约束的作用:

保证数据的完整性:主表与从表的数据要一致.

注意:正是因为外键具有非常强大的数据约束作用,而且可能导致数据在后台变化的不可控.导致程序在进行设计开发逻辑的时候,没有办法很好的把握数据(业务),所以外键比较少使用.