第七章 mysql 事务索引以及触发器，视图等等，很重要又难一点点的部分

【索引】

帮助快速查询

MyISAM ，InnoDB支持btree索引

Memory 支持 btree和hash索引

存储引擎支持 每个表至少16个索引   总索引长度至少256字节

创建索引的优点：

1， 加快查询速度

2， 创建唯一索引来保证数据表中数据的唯一性

3，  实现数据的完整性

4， 实现数据的完整性，，加速表和表之间的连接，

5， 减少分组和排序的时间

索引的分类

1，唯一索引和普通索引

2，单列索引和组合索引最左前缀

3，全文索引 fulltext 

在定义的索引的列上可以插重复值和空值，可以设置在char varchar text上

myISAM

6， 空间索引geometry   point  linstring  polygon  (not null)

空间索引的数据类型 GEOMETRY POINT  LINSTRING POLYGON

主键索引是一种特殊的索引，不允许有空值。

一个表可以有多个单列索引

创建索引的规则

(1) 创建索引并非是越多越好

(2) 数据量小的表最好不要创建索引

(3) 避免对经常更新的数据创建索引

(4) 在条件表达式中经常用到的不同值较多的列创建索引

(5) 当唯一性是某种数据本身的特征时，我们创建唯一性索引

(6) 在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建全文索引

Create index 创建索引  alter table 添加索引

Create table 表名[字段名 数据类型]  [unique唯一索引|fulltext全文索引|spatial空间索引]  [index|key]  [索引名]

(col_name [length]) [asc |disc]

创建普通索引

普通索引是最基础的索引类型，没有唯一性的限制，没有什么其他的要求，可以查重复的值，空值。作用是加快处理速度

 create table book ( bookid int not null, bookname varchar(255) not null, authors varchar(255) null,  comment varchar(255) null, year_publication year not null, index(year_publication) );

创建唯一索引原因是减少查询索引列操作的执行时间。尤其是对比比庞大的数据表。与普通索引类似。不同点在于：索引列的值必须唯一，但允许有控制。如果是组合索引，则劣质的组合必须唯一。

create table t1

    -> (

    -> id int not null,

    -> name char(30) not null,

    -> unique index Uniqldx(id)

    -> );

Query OK, 0 rows affected (0.05 sec)

单列索引：实在数据表中某个字段上创建的索引

创建

 create table t2

    -> (

    -> id int not null,

    -> name char(50) null,

    -> index singleIdx(name)

    -> );

Query OK, 0 rows affected (0.00 sec)

组合索引  ：在多个字段上创建一个索引

> create table t3

    -> (

    -> id int not null,

    -> name char(30) not null,

    -> age int not null,

    -> info varchar(255),

    -> index MultiIdx(id,name,age)

    -> );

组合索引可以起几个索引的作用但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行

查看创建的索引

用explain 判断索引是否正在被使用

mysql> explain select * from  book where year_publication=1999G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: book

         type: ref

possible_keys: year_publication

          key: year_publication

      key_len: 1

          ref: const

         rows: 1

        Extra:  额外

关联关系中另一个数据表里的数据列的名字

mysql 在执行这个查询时会从这个数据表里独处的数据行的个数

key_len  数字越小查询速度越快

取值范围

System   const    ref    eq_ref    index   all   range

【唯一索引】

创建唯一索引   唯一索引   的值不能重复，空值也只能有一个。

查看

【单列索引】

创建

查看

【组合索引】

创建

遵循原则

最左前缀 索引最左边的列来匹配行

查看

【全文索引】： FULLTEXT，可以用于全文收缩，只有MyISAM 存储引擎只吃，并且只为CHARVARCHAR和TEXT列。 索引总是对整个列进行。不支持局部索引

适合大型数据的表创建

必须在MyiSAM 类型中的表中创建，

创建，定义引擎=myisam

查看

【空间索引】

创建，定义引擎=myisam 字段必须为非空。

查看

【插入索引】

在已经存在的表上，创建索引

alter table 表名 add  [unique唯一索引|fulltext全文索引|spatial空间索引]  [index|key]  [默认索引名] (定义索引名[length])  [asc|desc]

 Index_type ：索引的类型

alter table

【单列索引】

【创建表，再创建索引】

【创建索引】

Create index 已存在的表上创建索引

Create  [unique唯一性索引|fulltext全文索引|spatial空间索引]  index 索引名  

On

表名（col_name[lenth]..）[asc|desc]

创建一个book1的表

普通索引

单列索引

create index BKcmtIdex on book1 (comment(50));

组合索引

添加全文索引

唯一性索引 

组合索引 2个或2个以上字段

用create table创建的索引可以不添加索引名（不添加索引名默认为字段名）

但是用create index 添加索引名必须加索引名

 添加全文索引

mysql> create table t6

    -> (

    -> id int not null,

    -> info char(255)

    -> )engine=MyISAM;

Query OK, 0 rows affected (0.04 sec)

mysql> alter table t6 add fulltext index InfoFULIdx(info);

查看

添加空间索引

mysql> alter table book add index bkAUthAndInFOiDX(authors(20),info(50));

ERROR 1072 (42000): Key column 'info' doesn't exist in table

mysql> create table t7

    -> (

    -> g geometry not null

    -> )engine=MyiSAM;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table t7 add spatial index Spatidx(g);

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

【删除索引】

Alter table 表名 drop  index  索引名；

查看book中有多少索引，准备开始删除

删除

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。

【视图】

视图含有一系列带有名称等的行和列数据。视图是从一个或多个表中导出来的，我们可以通过insert，update，delete来操作视图

一张虚表，和真实的表一样，当通过视图看到的数据被修改时，相应的原表的数据也会变化。同时原表发生变化，则这种变化也可以自动反映到视图中。

视图的优点：

1， 简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解也可以简化操作。经常被使用的查询可以制作为视图。

2， 安全性：通过视图用户只能查询和修改所能见到的数据，数据库中其他的数据即看不见，提取不到。数据库授权命令可以让每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定的行，列上。

3， 逻辑数据独立性 ：视图可帮助用户屏蔽真实表结构变化带来的影响

创建视图

Create  [or replace替换]  [algroithm视图选择的算法={undefined|merge |temptable}] view 视图名[(column_list)] as select_statement  [with[cascaded|local]check option]

[algroithm视图选择的算法={undefined|merge |temptable}]

Undefined：不常用。 

merge    ：表示将使用的视图语句与视图定义合并起来，使视图定义的某一部分取代语句对应的部分

temptable ：表示将视图的结果存入临时表，然后用临时表来执行语句

with[cascaded|local]

Cascaded   ：默认为cascaded，表示更新视图时，满足所有相关视图和表的条件

Local       ：表示更新视图时，满足该视图本身定义的条件即可

创建单表视图

创建多表视图abc

Create view abc

(id,name,addr,glass)

as

Select student.s_id, student.name,stu_info.addr, stu_info.glass

From student,stu_info

Where stu_info.s_id=student.s_id;

查看视图

查看视图的基本信息

Describe 视图名

查看视图的基本信息

Show table status like ‘视图名’G;   

查看视图的详细信息

show create view view_tG;

Show create view 视图名；

或show create view 视图名G

查看视图当中所有的详细信息

select * from information_schema.viewsG

在mysql中，information schema 数据库下的views表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细信息。

Select * from information_schema.views;

【修改视图】

Create or replace view

Alter 修改视图

【更新视图】

1，update

设置视图中的某个字段同时修改原表的字段：

update view_t set price=5;

2，insert

修改原表，同时修改视图；

3，delete

 delete from view_t2 where price=5; 

删除视图的字段，同时删除其他试图和表中的字段

视图存在以下情况时，更新操作无法执行

1视图中不包含基表中被定义为非空的列

2 在定义视图的select语句后的字段列表中使用了数学表达式

3 在定义视图的select 语句后字段列表中使用了聚合函数时不接受更新操作

4 select中，使用了distinctunion  op group by 或having 无法接受

覆盖视图

 create or replace view view_t as select * from t;

用t表覆盖view_t的状态

修改视图  让t中的 一个字段覆盖原来所有的短

【删除视图】

Drop view 【if exists】视图名1，视图名2 ……

扩展

mysql中视图和表的区别，以及联系是什么。

1、俩者的区别：

（1）、视图是已经编译好的视图语句，是基于sql语句的结果集的可视化的表。而表不是

（2）、视图没有实际的物理记录，而表有

（3）、表示内容而视图是窗口

（4）、表和视图虽然都占用物理空间，但是视图只是逻辑概念存在，而表可以及时对数据进行修改，但是视图只能用创建语句来修改

（5）、视图是一种查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些sql语句的集合。从安全角度来说，视图可以防止用户接触数据表，因而不知道表结构

（6）、表属于全局模式中的表，是实表，而试图属于局部模式的表，是虚表

（7）、视图的简历和删除只影响试图本身，而不影响对应表的基本表

2、两者的联系

视图是在基本表之上建立的表，他的结构和内容都来自于基本表，他一览基本表存在而存在，一个试图可以应对一个基本表，也可以对应多个基本表。视图是基本的抽象和逻辑意义上建立的表。

【事务】

什么是事务

多条sql语句，要么全部成功，要么全部失败。mysql的 失误是在存储引擎层实现。

mysql事物有ACID

A 原子性(atomicity)：一个事务必须被视为一个不可分割的单元。

C 一致性(consistency)：数据库是从一种状态切换到另一种状态。

I  隔离性(isolation)：事务在提交之前，对于其他事务不可见。

D 持久性(durablity)：一旦事务提交，所修改的将永久保存到数据库。

--------【开启事务】

begin 或者是start transaction

---------Mingjun + 500

---------Wengang -500

---------【查看】

-----------【回滚】！！！不要随便使用！！！！！回到事务开启之前的状态

------------【在查看】

-----------【结束事务】

-----------【总结事务命令】

·事务开始： start transaction

·事务开始： begin

·事务提交： commit

·回    滚： rollback

-----------查看自动提交模式是自动还是非自动

------------1代表开启自动提交，0代表关闭自动提交

-------------事务有4种隔离级别

事务在提交之前对其他事务可不可见

1， read unaommitted(未提交度)

2， read committed(以提交读)

总结：从上面的例子可以看出，提交读没有了未来提交读的问题，但是我们可以看到客户端的一个事务中执行了俩次同样的select语句，得到不同的结果，因为已提交读又被称为不可重复读。同样的筛选条件可能得到不同的结果。

3， epeatable read(可重复读)

总结：上面的例子我们得知可重复读俩次读取的内容不一样，数据库的幻读问题并没有得到解决

4， seaializable(可串行化)

详细解释

1、未提交度 ：实务中修改没有提交对其他事务也是可见的。俗称脏读

为提交读--脏读（不建议用）：事务中修改没有提交对其他事务也是可见的

总结：以上可以看出未提交读隔离级别非常危险，对于一个没有提交事务所做修改对另一个事务是可见状态

以提交读--不可重复读：多数数据库系统默认为此级别(MySQL不是)，以提交读级别即为一个事务只能已提交事务所做的修改，也就是解决了为提交读的问题，即脏读的问题。会出现问题：同样的两次select的结果不同，因此被称为不可重复读。

可重复读--解决了不可重复读的问题，数据库级别没有解决幻读的问题。

幻读只读锁定里面的数据，不能读锁定外的数据，解决幻读出了mvcc机制Mvcc机制

可串行化--是最高隔离级别，强制事务串行执行，执行串行了也就解决问题了，这个I别只有在对数据一致性要求非常严格并且没有并发的情况下使用

查看隔离级别

隔离级别脏读  不可重复读   幻读  加锁读

为提交读是   是是 否

提交读否是是 否

可重复读否否是 否

串行度否否否 是

【触发器】

触发器trigger 是一个特殊的存储过程，都是嵌入到mysql的一段程序。触发器是由事件触发某个操作。这些事件包括insert、update、delete。如果定义了触发程序，当数据库执行这些语句的时候就会触发触发器执行相应的操作。触发器可以查询其他的表，而且可以包含复杂的sql语句，不需要手动启动，只要当一个预定义事件发生的时候，就会被mysql自动调用

创建触发器的语法

Create trigger 触发器名 触发器时机（before 或 after） 触发的事件（3个insert update delete）

On 表名

For each row

End

创建触发器

创建有多个执行语句的触发器

Delimiter //

Create trigger testref

Before insert

On test1

For each row

Begin

Insert into test2 set a2 =new.a1;

Delete from test3 where a3 = new.a1;

Update test4 set b4 = b4 + 1 where a4 = new.a1;

End

//

查看触发器 show triggers; 或show triggersG;

删除触发器

Drop trigger 触发器名

创建触发器

1、创建一个表

 create table myevent

    -> (

    -> id int(11) default null,

    -> evt_name char(20) default null

-> );

2、创建触发

create tRIGGER tri_update after UPDATE on account for each row insert into myevent values(1,'after update');

3、查看

SHOW TRIGGERSG;

4、查看所有

select *from information_schema.triggers  where trigger_name='tri_update'G;

mysql创建用户与权限

createuser user_specification

【user_specification】.

user_specification :User:@host

[identified by [password]]’password’ | identified with auth_plugin [as ‘auth_string’]

user : 表示创建的 用户的名称 ；host ：表示允许登录的用户主机名称；

idenitifide by ： 表示用来设置用户的密码 ：【password】 ：表示使用哈希值设置密码：‘password'：表示用户登录时使用的普通文明密码；identified with ：表示用户指定一个身份验证插件；

auth_plugin : 是插件的形成  auth_string 是可选的字符串，解释插件的意义

密码加密

select password （）；

获取到加密的信息后

用 grant 创建新用户

GRANT privileeges ON db.table

to user@host'【identified by ‘password’】  【user@host'【identified by ‘password’】】   【with grant option】

privileges 表示赋予用户的权限类型；db.table ：表示拥护的权限所作用的数据库中的表；identified by 关键字用来设置密码；‘password’ 用户的密码；【with grant option】 可选项，表示对新建立的 用户赋予grant权限。

直接操作 mysql 用户表

、、直接添加的用户还无法使用账号和密码登录mysql 需要使用mysql告诉服务器重新加载授权；

删除普通用户

1、使用drop user 语句删除

2、使用delete语句删除用户

root 修改自己的密码

1、使用mysqladmin命令在命令指定新密码

2、mysqladmin -uuwername -h localhost -p password‘newpassword’

username ：要修改的用户名

-h ：需要修改哪个主机

-p ：输入当前的密码

newpassword  ：新的 用户密码

mysqladmin -u root -p password"rootpassword"

登录时需要输入旧密码

3、修改 mysql 数据库的 user 表

UPDATE mysql .user set password=passwprd

需要刷新

使用 updata用户修改普通用户密码

使用grant语句修改普通用户密码

grant usage on *.* to ‘testuser

root 用户密码丢失的 解决办法

mysqld_safe --safe-skip-grant-tables user=mysql

/etc/init.d/mysqld restart --skip-grant-tables

【MySQL日志】

分为4大类

1 错误日志：记录mysql服务的启动，运行或停止mysql服务时出现的问题

2 查询日志：记录建立的客户端的连接和执行的语句

3 二进制日志：记录所有更改数据的语句，可以用于数据的复制

4 慢查询日志：记录所有执行的时间超过long_query_time的所有查询或不使用索引的查询

Flush logs刷新日志

二进制日志：

包含 所有更新数据，或者已经潜在更新了的数据的语句

My.ini 或my.cnf

49行

【格式】： 

在26行添加

log-bin [=path/[filename]]二进制日志[路径[指定日志文件的名字]]

expire_logs_days = 10清除日志的天数

max_binlog_size = 100M单个日志文件的大小限制，超出会新建一个

【重启mysql】

Show variables 或show variables like 'log_%';

语句来查询日志设置

【查看二进制日志】

Show binary logs;

【查看二进制日志的内容】

退出mysql在命令行

Cd /bin

ls

mysqlbinlog

【删除二进制日志】

reset master;删除所有二进制日志

purge master logs to ‘二进制名’ 删除单个二进制日志之前的

Purge binary logs before ‘date’  删除指定日期之前的日志

 3564554445445455543444444444444444443444343444443333333333343333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333335

存储了所有变更信息，经常用

开启二进制日志 以文件名.index为后缀名

再创建会以文件名.000001

Mysql服务器重启一次后缀名+1，变成000002

Max_binlog_size 默认1G

【还原】

使用二进制日志还原数据库

使用最后一次备份还原，或指定一个时间恢复数据

Mysqlbinlog [option]日志文件名filename  mysql –uuser -ppass

[option]里面的选项 --start-date开始的时间--stop-position结束的位置

mysqlbin

【暂时停止二进制日志的功能】

Set sql_log_bin = 0或1  0暂停 1恢复

错误日志

启动和设置错误日志

主机名.err 存放日志的文件名   

Flush logs

[mysqld]

退出mysql在配置文件

在/etc/my.cnf下

添加

log-error=路径/文件名

默认路径在   /usr/local/mysql/data/

文本形式存储

【查看错误日志的存储路径】 Show variables like ‘log_error’   

【删除错误日志】

在路径下

rm –rf

修改完配置文件之后，需要在自己的端口使用

【刷新所有日志】

通用查询日志 

记录所有用户操作

日志名.Pid

【开启通用查询日志】

Vim /etc/my.cnf

添加

log-bin

expire_logs_days = 0          //默认删除日志的时间

max_binlog_size = 100M       //设置单个二进制文件 的最大限度(最小) 默认值 为 1 g

Log 路径/文件名

默认路径在安装目录下

【刷新日志来删除通用查询日志】

mysqladmin -u root -p123.com flush-logs

软件优化

解决瓶颈：做索引

分析查询语句 

Explain select * from studentG;

Select_type:

其中simple 是简单查询(不包括连接查询和子查询)

Primary 主查询

Union 连接查询

Type:连接类型

System仅有系统表一行

Const 数据表中最多只有一行匹配，将在查询开始时被读取，并在余下的查询优化中，作为常量

Eq_ref 用于使用 = 操作符比较带索引的列

ref 对于来自前面的表的任意行的组合，从该表中读取所有匹配的行

ref_or_null 同上，添加可以专门搜索包含null值的行

index_merge 将连接类型表示使用了索引并优化方法

range 只检索给定范围的行

index 与all的连接类型相同，除了只扫描索引树

all 前面的表的任意行的组合，进行完整的表的扫描

describe select 语句和分析查询语句 

Explain select * from studentG; 结果是一样的

索引提高了查询效率

Create index 索引名 on 表名(字段)

在/usr/local/mysql/data/   //查看二进制文件

 mysqlbinlog mysql-bin.00003

查看二进制文件的具体内容

show binary logs;

删除二进制文件的方式  

1、 reset master；删除所有

2、purge master logs to  '二进制文件名'

purge binary logs before 'date'     //date 指时间 日期 如：（20170813）

想要产生，日志，可以通过多次刷新，来生成新的日志

优化查询速度

注意：

1做索引了之后，用  like ‘xx%’ %不在第一位查询效率最高

2多字段索引，除了第一字段查询最快，其余不按索引来，索引不生效

3若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值

查询最快

优化数据库表结构

将字段很多的表分解成多个表

查看表的表结构

优化表结构

1 将字段很多的表分解成多个表

2 增加中间表

3 合理增加冗余字段

4 优化插入记录的速度

（1），禁用索引(在插入数据之前禁用索引，会让创建索引不会生效，命令：alter table 表名 disable keys  ，注意表的创建表后加引擎 engine=myisam，可以禁用成功)

（2），禁用唯一性检查(插入记录之前禁用唯一性检查，命令：set unique_checks=0 关闭set unique_checks=1)                   

（3），使用批量插入(多条插入命令整合成一条命令)

（4），使用load data infile批量插入

对于innnodb表来说

(1) 禁用唯一性检查

(2) 禁用外键检查(命令: set foreign_key_checks=0，开启=1)

(3) 禁用自动提交(命令: set autocommit=0，开启=1)

5 分析表，检查表和优化表

分析表：分析关键字的分布

检查表：检查是否存在错误

优化表：消除删除或更新造成的间浪费

分析表语句：analyze [local |no_wirte_to_binlog] table tb1_name[tb2_name]…..

Local的关键字不写入二进制日志 后跟1个表或多个表

在分析期间只能读，不能进行插入和更新的操作。

Table是表名op执行的操作时什么  msg_type 信息级别（atus是正常状态，info是信息，note注意，warning警告，error错误） msg_text 是显示信息

检查表：检查是否存在错误,关键字统计，检查视图是否有错误

Check table 表1 option ={quick |fast | medium |extended |changed}

Quick 不扫描行，不检查错误连接

Fast 只检查没有被正确关闭的表

Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。

Extended 对每行所有关键字进行全面的关键字查找

Changed 只检查上次检查后被更改的表和没有被正确关闭的表

Option只对syisam 有效  对innodb表无效

优化表：

Optimize table

Optimize [local |no_warite_to_binlog] table tb1_name ….

只能优化myisam的表和innodb的表都有效

但是只能优化表中的varchar extlob

执行过程中上只读锁

索引缓冲区  

可以提高查询速度 适合查询的时候使用

在mysql配置文件里写

Vim /etc/my.cnf编辑下

Query_cache_size=512M   缓冲区的大小

Query_cache_type=1      开启缓冲区

用一个语句刷新缓冲区

在mysql软件里写

flush query cache

注意：看到

Sql_no_cache 不能建索引缓冲区

提示4开头的错误找开发

提示5开头的错误找运维

增删改查（表，库）

事务

索引

备份，还原

mysql权限系统的主要功能是证实连接到一台给定主机的用户，并赋予该用户在数据库上

SELECTINSERTUPDATEDELETE账户权限信息被存储在mysql数据库的user、db、

权限  权限范围

CREATE数据库、表和索引

DROP数据库、表和视图

CRANT OPTION数据库、表、

REFERENCES数据库、表

EVENT数据库

ALTER数据库

DELETE表

INDEX表

INSERT表

SELECT表或列

UPDATE表或列

CREATE TEMPORARY TABLES      表

LOCK TABLES(锁表)表

TRIGGER表

CREATE VIEW视图

SHOWVIEW视图

FILE 访问服务器上的文件

RELOADD访问服务器上的文件

SHUTDOWN服务器管理

PROCESS 函数

CREATE USER   服务器管理

SHOPW DATABASES  服务器管理

REPLICATTONSLAVE    服务器管理

REPLICATTONNCLIENT   服务器管理

1、如果将mysql数据库中的drop权限授予某用户，用户可以删掉mysql访问权限保存的数据库

2、SELECT、INSERTUPDATE和DELECT 权限允许在一个数据库现有的表上实施操作

3、SELECT 权限只有在他们真正从一个表中检索时才被使用

4、INDEX 权限允许创建或删除索引，INDEX适用已有表。如果具有某个表的create权限，可以再CREATE TABLE 语句中包括索引定义

5、ALTER权限，可以使用ALTER  TABLE来更改表的结构和重命名表

6、CREATE ROUTINE 权限来创建保存的程序（函数和程序），ALTER ROUTINE 权限用来更改和删除保存的程序，EXECUTE权限用来执行保护的程序。

7、GRANT 权限 允许授权给其他用户。可用于数据库、表和保存的程序

8、FILE 权限基于用户使用 LOAD DATA INFILE 和 SELECT ...INTO OUTFILE语句读或写服务器上的文件、任何被授予FILE权限的用户都能读或写mysql服务器上的任何文件

FILE权限允许用户在mysql服务器具有写权限的目录下创建新文件，但不能覆盖已有文件

授权

授权就是为了某个用户权限

1、全局层级适用于一个给定服务器中的所有数据库

2、数据库层级  数据库权限适用于一个给定数据库中的所有目标

3、表层级  表权限适用于一个给定表中的所有列

4、列层级了权限适用于一个给定表中的单一列

5、子程序层级  CREATE ROUTINE 、ALTER ROUTINE 、 EXCUTE 和 GRANT 权限适用于已存储的子程序。这写权限可以被授予为全局层级和数据库层级。而且 除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在MYSQL.PROCS_PRIV表中

在mysql中，必须拥有GRANT权限的用户才可以执行CRANT语句。要使用GRANT或REVOKE必须拥有GRANT OPTION权限，并且必须用于正在授权或撤销的权限

GRANT的语法如下

GRANT priv_type【（columns）】 【priv_type[（columns）]】

ON 【object_type】  table1，table2。。。。。。

TO user【IDENTIFIED BY  [password]‘password’】

【with grant option】

object_type=TABLE | FUNCTION | PROCEDURE

GRANT OPTION 取值 ：

| MAX_QUERIES_PER_HOUR count

|MAX_UPDATE_PER_HOUR count

|MAX_CONNECTIONS_PER_HOUR count

|MAX_UESR_PER_HOUR count

GRANT OPTION 的 取值有5个 意义

1、GRANT OPTION 将自己的权限可以赋予其他的用户

2、| MAX_QUERIES_PER_HOUR count   设置每个小时可移植性count次查询

3、|MAX_UPDATE_PER_HOUR count  设置每个小时可以执行新count次更新

4、|MAX_CONNECTIONS_PER_HOUR count 设置没课小时可以建立count个连接

5、|MAX_UESR_PER_HOUR count 设置单个用户可以同时建立count个连接

mysql> CREATE USER 'ljx'@'localhost' IDENTIFIED BY '123.com';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ,INSERT ON *.* TO 'ljx'@'localhost'

    -> IDENTIFIED BY '123.com';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user='ljx';

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

| Host      | User | Select_priv | Insert_priv | Grant_priv |

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

| localhost | ljx  | Y           | Y           | N          |

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

1 row in set (0.00 sec)

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的前线可以在一定程度上保证系统的安全性质，mysql中使用revoke 语句来取消用户的某些权限，使用revoke收回权限之后，用户账号的记录将从db ，host ，user，table_priv表中删除，但是用户账号记录仍然在user表中保存（删除user表中的账户记录，用DROP USER 语句）

REVOKE 语句有俩种用法

1、第一种语法是收回所有用户的所有权限，此语法用于取消对已命名的用户的所有全局层级，数据库层级，表层级，和列层级权限。

REVOKE  ALL  PRIVILEGES GRANT OPTION

FROM  ‘USER’ @’LOCALHOST’

2、第二种语法是是长格式的REVOKE 语句

REVOKE priv_type [(columns)],priv_type[(columns)]..

ON table1,table2....

FROM ‘user’@’localhost’

查看权限

show grants 语句可以显示指定用户的权限信息

SHOW GRANTS FOR ‘user' @ ‘host’

show grants FOR 'ljx'@'localhost'G

*************************** 1. row ***************************

Grants for ljx@localhost: GRANT SELECT ON *.* TO 'ljx'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA'

1 row in set (0.00 sec)

SELECT语句查看user表中的各个权限字段以确定用户的权限信息

SELECT privileges_list FROM userWHERE user=‘username’，host=‘hostname’

删除用户的账号密码

delete from mysql.user where host='localhost' and user='lujunxiong';

 flush privileges;

 drop user 'ljx'@'localhost';

修改密码  三种方式

1、mysqladmin -u root -p123456 password "123.com"

-p  后面是旧密码

password 后面 是新密码

2、update mysql.user set Password=PASSWORD('123.com')

-> where User='root' and Host='localhost';

3、set password=password('123456');

谁登陆就设置谁的密码。需要在 root 用户下刷新

无论对用户活着权限进行哪种操作  都需要进行舒心

flush privileges；

【备份】

企业使用 pkn

备份  ：热，温，冷

   全量增量差异

原始备份有3种方式

第三方的有sqlyog   navicat  

[root@localhost ~]# mysqldump -uxxxx -pxxxx test > /data/test.sql                # 备份 test 库

[root@localhost ~]# mysql -uxxxx -pxxxx test < /data/test.sql                    # 恢复 test 库

[root@localhost ~]# mysqldump -uxxxx -pxxxx test abc > /data/table_abc.sql       # 备份 test 库下的 abc 表

[root@localhost ~]# mysql -uxxxx -pxxxx test < /data/table_abc.sql               # 恢复 test 库下的 abc 表

[root@localhost ~]# mysqldump -uxxxx -pxxxx -B test abc > /data/table_abc.sql    # 同时备份多个库，这里表示备份 test、abc 库

[root@localhost ~]# mysqldump -uxxxx -pxxxx test abc def ghi > test.sql          # 同时备份多个表，这里表示备份 test 库下的 abc、def、ghi 三张表

-u                         # 指定用户名

-p                         # 指定密码

-R                         # 导出存储过程和自定义函数

-d                         # 只备份表结构而不备份数据

-t                         # 只备份数据而不备份表结构

-A                         # 用于备份所有库，如 mysqldump -uxxx -pxxx -A > /date/all_db.sql

-B                         # 用于备份多个库，如 mysqldump -uxxx -pxxx -B test abc > /data/two.sql 表示备份 test、abc 两个库

--opt                      # 如果带上这个选项代表加上 --add-drop-table，--add-locks，--extended-insert，--lock-tables 参数

--add-drop-table           # 在每个 CREATE TABEL 命令之前增加 DROP-TABLE IF EXISTS 语句，防止数据表重名

--add-locks                # 表示在 INSERT 数据之前和之后进行锁表

--extended-insert          # 表示可以多行插入

--lock-tables              # 在备份的时候进行锁表

--default-character-set    # 指定字符集

--single-transaction       # 备份期间不会锁表也不会阻止任何的事务

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;   ：把当前的系统变量，定义为新的系统变量

【备份】

1， mysqldump 有引擎限制，冷备份(关机备份)

mysqldump –u root –p  库 表名 > 路径/文件名.sql (以 .sql为结尾)

mysqldump是可执行文件，可执行文件就是二进制文件

set语句将当前系统变量 CHARACTER_SET_CLIENT的值赋给用户定义变量@@OLD_CHARACTER_SET_CLIENT

   mysqldump –u root –p –all --databases [库名库名] > 路径/文件名.sql

2， 直接复制整个据据库目录  简单快速直接有效

Lock tables  锁表

Flush tables  刷新表

不适用innoDB存储引擎

3， 使用mysqlhotcopy

用 perl语言写的脚本

只能备份是myisam引擎的表

Mysqlhotcopy    db_name…. db_name  /path/new_dir

mysqlhotcopy -u root  -p db_name /usr/local/mysql

使用 source 在mysql中恢复数据

在mysql 中选择库   

执行  source /usr/local/mysql/ljx.sql

【还原】

1，Mysql –u root –p 库名 < 路径/文件名

2，直接复制到数据库目录

3，Mysqlhotcopy快速恢复

给权限chown –R mysql:mysql  /var/lib/mysql/dbname

复制默认路径cp  –R  /usr/backup/test  /usr/local/mysql/data

重启服务器！

数据库迁移(备份还原)

服务器坏，数据库更新，数据库压力过大

percona   

percona  Xtraback 备份工具去官网下载

创建新的库和表  存储引擎为 myisam

插入数值

mysqldump -h localhost -u root -p 123.com ljxdb | mysql -h localhost -u root -p123.com

mysql日志记录了 mysql数据库日常操作和错误信息，mysql 有 不同类型的日志文件（各自存储了 不同类型的日志），从日志当中可以查询到mysql数据库的运行情况、用户的操作、错误的信息等，

默认情况下所有日志创建与 mysql数据 目录 中 ，用过刷新日志可以强制mysql 关闭 和 重新 打开 日志文件，flush logs刷新日志或者执行 mysqladmin flush-logs

如果使用 mysql复制功能 ，在复制服务器上 可以维护更多日志文件， 这种日志我们成为阶梯文件 。启动日志功能 会降低mysql数据库的性能

使用二进制日志的主要目的是最大可能的恢复数据库。

二进制日志：主要记录 mysql数据库的 变化 ，二进制日志以 一种有效的 格式 ，并且是 失误 安全的方式包含更新日志中 可用的 信息。二进制日志包含了所有更新了数据或者 已经潜在更新的 数据的 语句 。

启动二进制日志，默认情况下二进制文件时关闭的

my.ini

my.cnf

在 mysqld下 添加

log-bin

expire_logs_days = 10

max_binlog_size = 100M

log-bin' 【=path/【filename】】  二进制日志  路径   指定日志文件的 名字

Expire_logs_days = 10 //清楚日志的天数

Max_binlog_size = 100M //单个日志文件的大小限制，超出会新建 一个 默认是 1 G

重启 mysql

查看日志的设置

show variables;

查看二进制日志

mysql 二进制日志 存储 了 所有 的 变更 信息 ，mysql二进制日志 经常 使用 ，当 mysql创建二进制文件时 首先创建一个 以‘filename’为名称，以‘.index’为 后缀 的 文件 再创建 一个 以 ‘filename’为名称 以‘.000001’ 为后缀的文件 。当 mysql服务重启一次

以‘.000001’ 为后缀的文件 会 增加一次，并且 后缀 名加一递增。如过日志长度超过 max_binlog_size的 上限，也会创建 一个 新的 日志 。

show  binary logs； 可以查看当前 的 二进制日志文件个数 以及 其文件名。二进制日志并不能直接查看 ，如果想要 查看日志内容，可以通过 mysqlbinlog 命令查看

show binary logs;

删除二进制日志  mysql的 二进制文件 可以配置自动删除，同时mysql 提供 了 手动删除二进制文件的 方法 ： RESET MASTER 删除所有的 二进制日志文件：

PURGE MASTER LOOOGS 只删除部分二进制日志文件

reset  master  删除所有的 二进制文件

purge master logs to ‘二进制名’ 删除单个二进制文件 之前的

二进制日志还原

如果 mysql 服务器 启用 了二进制日志  我们可以使用 二进制日志 来进行还原数据库

使用最后一次备份还原或指定一个时间恢复数据

mysqlbinlog 【option】 日志文件名 filename  mysql-uuser-ppas

【option】 里面的 熏香 -start-date 开始的 时间 --stop-position结束的 位置

 mysqlbinlog --stop-date 结束 的 时间

暂时展厅二进制日志的功能如果 mysql的 配置文件 已经启动 了 二进制日志 ，mysql 会 一直 记录二进制日志 修改 配置文件 ，可以停止 二进制日志 ，但是 需要重启 mysql数据库 。

mysql 提供了 暂时停止二进制的 功能 通过 SET  sql_log_bin = 0 或 1 ；

可以使mysql暂时 停止 二进制

c

错误日志

错误日志包含了当 mysqld启动和 停止 时 以及服务器的 运行过程中 发生 任何严重错误时的 相关信息。设置错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，文件名默认为 host name。err 。 执行FLUSH LOGS 错误日志文件 会 重新加载。

【mysqld】

主机名，err 存放日志的 文件名

查看·错误日志的 存储路径

通过错误日志可以监视系统的运行状态，便于及时发现故障、修复故障。mysql 错误日志 是以文本文件形式存储的 ，可以使用

show variables like ‘log_error’

删除错误日志

mysql 的 错误日志 是 以文本 文件的形式 存储在文件 系统中 可以直接删除

语法

刷新所有日志

mysqladmin -uroot -p123.com flush-logs

或者

flush logs；

通用日志查询

通过查询日志记录所有用户的操作，包括启动和关闭服务、执行查询和更新语句等。

mysql 默认情况下 并没有 开启通用查询 日志 。如果需要 ，可以通过 修改 my.cnf来 开启 。通用查询 日志是 以文本 文件的 形式 存储在文件系统中 ，日志 名 .pid

开启通用查询日志

在配置文件

 mysqld下 添加

log 路径 文件名

刷新日志文件来删除通用查询日志

慢查询日志

是指记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句。通过慢查询日志可以找出执行时间，可以找出执行时间较长效率较低的语句，然后头花。慢查询日志默认是关闭的，可以通过配置文件 log-slow-queries

long_query_time

进行开启

使用xtrabackup在线增量备份及恢复数据库

一、Percona Xtrabackup 简介

1、Xtrabackup bin目录文件 介绍

1）innobackupex

innobackupex 是xtrabackup的一个符号链接 . innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.

2）xtrabackup

一个由C编译而来的二进制文件，可以整备MySQL database instance with MyISAM, InnoDB, and XtraDB tables

3）xbcrypt

用来加密或解密备份的数据

4）xbstream

用来解压或压缩xbstream格式的压缩文件

5）xbcloud

utility used for downloading and uploading full or part of xbstream archive from/to cloud.

2、Percona XtraBackup info

是开源免费的MySQL数据库热备份软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）

you can achieve the following benefits: （https://www.percona.com/doc/percona-xtrabackup/2.3/intro.html）

• Backups that complete quickly and reliably
• Uninterrupted transaction processing during backups
• Savings on disk space and network bandwidth
• Automatic backup verification
• Higher uptime due to faster restore time

features

• Create hot InnoDB backups without pausing your database
• Make incremental backups of MySQL
• Stream compressed MySQL backups to another server
• Move tables between MySQL servers on-line
• Create new MySQL replication slaves easily
• Backup MySQL without adding load to the server
• 无需停止数据库进行InnoDB热备
• 增量备份MySQL
• 流压缩到传输到其它服务器
• 在线移动表
• 能比较容易地创建主从同步
• 备份MySQL时不会增大服务器负载

3、Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下

1、在InnoDB内部会维护一个redo/undo日志文件，也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

2、Xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

3、上面就是xtrabackup的备份过程。接下来是准备（prepare）过程，在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像mysql刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了，这个过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。

4、备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以myisam表数据与InnoDB表数据是同步的。类似oracle的，InnoDB的prepare过程可以称为recover（恢复），myisam的数据复制过程可以称为restore（还原）。

5、Xtrabackup 和 innobackupex这两个工具都提供了许多前文没有提到的功能特点。手册上有对各个功能都有详细的介绍。简单介绍下，这些工具提供了如流（streaming）备份，增量（incremental）备份等，通过复制数据文件，复制日志文件和提交日志到数据文件（前滚）实现了各种复合备份方式。

二、安装xtrabackup

1、安装

2、修改my.cnf

三、全部数据库备份与还原

1、备份

2、还原

四、增量备份与还原

1、创建测试数据库和表

2、增量备份

3、查看增量备份记录文件

增量备份做完后，把backup_test这个数据库删除掉，drop database backup_test;这样可以对比还原后

4、增量还原

分为两个步骤

a.prepare

此时数据可以被程序访问使用；可使用—use-memory选项指定所用内存以加快进度，默认100M；

b.recover

从my.cnf读取datadir/innodb_data_home_dir/innodb_data_file_path等变量

先复制MyISAM表，然后是innodb表，最后为logfile；--data-dir目录必须为空

开始合并

恢复数据

五、innobackup 常用参数说明

--defaults-file

同xtrabackup的--defaults-file参数

--apply-log

对xtrabackup的--prepare参数的封装

--copy-back

做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir ；

--remote-host=HOSTNAME

通过ssh将备份数据存储到进程服务器上；

--stream=[tar]

备份文件输出格式, tar时使用tar4ibd , 该文件可在XtarBackup binary文件中获得.如果备份时有指定--stream=tar, 则tar4ibd文件所处目录一定要在$PATH中(因为使用的是tar4ibd去压缩, 在XtraBackup的binary包中可获得该文件)。

在使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。

--tmpdir=DIRECTORY

当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir

--redo-only --apply-log组,

强制备份日志时只redo ,跳过rollback。这在做增量备份时非常必要。

--use-memory=#

该参数在prepare的时候使用，控制prepare时innodb实例使用的内存量

--throttle=IOS

同xtrabackup的--throttle参数

--sleep=是给ibbackup使用的，指定每备份1M数据，过程停止拷贝多少毫秒，也是为了在备份时尽量减小对正常业务的影响，具体可以查看ibbackup的手册 ；

--compress[=LEVEL]

对备份数据迚行压缩，仅支持ibbackup，xtrabackup还没有实现；

--include=REGEXP

对 xtrabackup参数--tables的封装，也支持ibbackup。备份包含的库表，例如：--include="test.*"，意思是要备份 test库中所有的表。如果需要全备份，则省略这个参数；如果需要备份test库下的2个表：test1和test2,则写 成：--include="test.test1|test.test2"。也可以使用通配符，如：--include="test.test*"。

--databases=LIST

列出需要备份的databases，如果没有指定该参数，所有包含MyISAM和InnoDB表的database都会被备份；

--uncompress

解压备份的数据文件，支持ibbackup，xtrabackup还没有实现该功能；

--slave-info,

备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

--socket=SOCKET

指定mysql.sock所在位置，以便备份进程登录mysql.