前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

一、用户权限管理作用： 

1、可以限制用户访问哪些库、哪些表

2、可以限制用户对哪些表执行 CREATE、DELETE、ALTER、UPDATE、SELECT 等操作 

3、可以限制用户登录的IP或域名 

4、可以限制用户自己的权限是否可以授权给别的用户

1、权限相关表

1）user 表

mysql 中所有的用户都是存放在 user 表中的，user 表有 39 个字段。这些字段可以分为 4 类：用户列、权限列、安全列、资源控制列

a、用户列

b、权限列

　user 表的权限列是以 priv 结尾的字段。这些字段的值只有 Y 和 N。Y 表示该权限可以用到所有数据库上；N 表示该权限不能用到所有数据库上；

| 列 | 说明 | | ———————– | ———————————————————— | | Select_priv | 确定用户是否可以通过 SELEC T命令选择数据 | | Insert_priv | 确定用户是否可以通过 INSER T命令插入数据 | | ` Update_priv | 确定用户是否可以通过 UPDAT E命令修改现有数据 | | Delete_priv | 确定用户是否可以通过 DELETE命令删除现有数据 | | Create_priv | 确定用户是否可以创建新的数据库和表 | | Drop_priv | 确定用户是否可以删除现有数据库和表 | | Reload_priv | 确定用户是否可以执行刷新和重新加载 MySQL 所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表 | | Shutdown_priv | 确定用户是否可以关闭 MySQL 服务器。在将此权限提供给 root 账户之外的任何用户时，都应当非常谨慎 | | Process_priv | 确定用户是否可以通过 SHOW PROCESSLIST 命令查看其他用户的进程 | | File_priv | 确定用户是否可以执行SELECT INTO OUTFILE 和 LOAD DATA INFILE命令 | | Grant_priv | 确定用户是否可以将已经授予给该用户自己的权限再授予其他用户 | | References_priv | 目前只是某些未来功能的占位符；现在没有作用 | | Index_priv | 确定用户是否可以创建和删除表索引 | | Alter_priv | 确定用户是否可以重命名和修改表结构 | | Show_db_priv | 确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库 | | Super_priv | 确定用户是否可以执行某些强大的管理功能，例如通过 KILL 命令删除用户进程，使用 SET GLOBAL 修改全局 MySQL变量，执行关于复制和日志的各种命令 | | Create_tmp_table_priv | 确定用户是否可以创建临时表 | | Lock_tables_priv | 确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改 | | Execute_priv | 确定用户是否可以执行存储过程 | | Repl_slave_priv | 确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信 | | Repl_client_priv | 确定用户是否可以确定复制从服务器和主服务器的位置 | | Create_view_priv | 确定用户是否可以创建视图 | | Show_view_priv | 确定用户是否可以查看视图或了解视图如何执行 | | Create_routine_priv | 确定用户是否可以更改或放弃存储过程和函数 | | Alter_routine_priv` | 确定用户是否可以修改或删除存储函数及函数 |

c、安全列

SHOW VARIABLES LIKE 'have_openssl'

d.、资源控制列

　　user表的4个资源控制列是：

2）db 表

　　db 表存储了某个用户对一个数据库的权限。主要分为2个，用户列和权限列

| Field | Type | Collation | Null | Key | Default | Extra | Privileges | | ———————– | ————— | —————— | —— | —– | ——— | ——- | ——————————- | | Host | char(255) | ascii_general_ci | NO | PRI | | | select,insert,update,references | | Db | char(64) | utf8mb3_bin | NO | PRI | | | select,insert,update,references | | User | char(32) | utf8mb3_bin | NO | PRI | | | select,insert,update,references | | Select_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Insert_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Update_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Delete_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Create_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Drop_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Grant_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | References_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Index_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Alter_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Create_tmp_table_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Lock_tables_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Create_view_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Show_view_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Create_routine_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Alter_routine_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Execute_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Event_priv | enum(‘N’,’Y’) | utf8mb3_general_ci | NO | | N | | select,insert,update,references | | Trigger_priv | enum(‘N’,’Y’)` | utf8mb3_general_ci | NO | | N | | select,insert,update,references |

a、用户列

b、权限列

3）tables_priv

tables_priv ：可以对单个表进行权限设置，tables_priv 表包含 8 个字段：

| Field | Type | Collation | Null | Key | Default | Extra | Privileges | | | ————- | ———————————————————— | —————— | —— | —– | —————– | ——————————————— | ——————————- | —- | | Host | char(255) | ascii_general_ci | NO | PRI | | | select,insert,update,references | | | Db | char(64) | utf8mb3_bin | NO | PRI | | | select,insert,update,references | | | User | char(32) | utf8mb3_bin | NO | PRI | | | select,insert,update,references | | | Table_name | char(64) | utf8mb3_bin | NO | PRI | | | select,insert,update,references | | | Grantor | varchar(288) | utf8mb3_bin | NO | MUL | | | select,insert,update,references | | | Timestamp | timestamp | | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | | | Table_priv | set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’) | utf8mb3_general_ci | NO | | | | select,insert,update,references | | | Column_priv | set(‘Select’,’Insert’,’Update’,’References’)` | utf8mb3_general_ci | NO | | | | select,insert,update,references | | | | | | | | | | | |

4）columns_priv

2、权限分配

1）user 表和 db 表的关联以及级别关系

　user 表中的权限是针对所有数据库的，如果 user 表中的 Select_priv 字段取值为 Y，正常情况下该用户可以查询所有数据库中的表； 假如最终结果是为某个用户值设置了查询一个库中表的权限，但是 user 表的 Select_priv 字段的取值为N。那么 db 表中的 Select_priv 字段的取值将会是 Y。

　原因：用户先根据 user 表的内容获取权限，然后再根据 db 表的内容获取权限。

2）权限分配顺序

user 表(所有数据库) -> db 表 (某个数据库) -> table_priv 表(某个表) -> columns_priv 表(某表的某列)的顺序进行分配的。

在数据库系统中，先判断 user 表中的值是否为 Y，如果 user 表中的值是 Y，就不需要检查后面的表。如果 user 表为 N，则一次检查后面的表。

3、管理账户

1）新建普通用户

正常来说一共有三种方式

1、使用`create user`语句来创建新的用户；    

2、直接在`mysql.user`表中INSERT用户；       

3、使用`grant`语句来新建用户；

a、create user （建议使用）

使用 create user 语句创建用户，必须要拥有 create user权限。

格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0 IP段登录，执行之后 user 表会增加一行记录，但权限暂时全部为 N。

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'

2） 删除用户

a、drop user语句删除普通用户

需要拥有、drop user 权限。语法如下：user 是需要删除的用户，由用户名( User )和主机名( Host )构成。

DROP USER 'admin'@'%'

b、delete语句删除普通用户

可以使用 delete 语句直接将用户的信息从 mysql.user表中删除。但必须拥有对 mysql.user 表的 delete 权限。删除完成后，FLUSH PRIVILEGES 进行生效

DELETE FROM mysql.user WHERE Host = '%' AND User = 'admin'

3） 查看数据库中所有的用户

muysql> select u.host,u.user from user u;

4）创建用户并授权

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

grant select,insert,update,delete ON talbename.* to 'hlj_sql'@'127.0.0.%' IDENTIFIED BY '123456';

grant select ON *.* to netUser@'%'

5）刷新权限 flush

对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

 mysql> flush privileges;

6）回收权限 revoke

删除 yangxin 这个用户的 create 权限，该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'yangxin@localhost';

7）删除用户

mysql> select host,user from user;
+---------------+---------+
| host          | user    |
+---------------+---------+
localhost	HealerJean
localhost	mysql.session
localhost	mysql.sys
localhost	root

8 rows in set (0.00 sec)
mysql> drop user 'HealerJean'@'localhost';

8、用户重命名

mysql> rename user 'test3'@'%' to 'test1'@'%';

9、修改密码

mysql> set password for 'root'@'localhost'=password('123456')    

​

二、Mysql 的变量

1、系统变量

注意：不能随便定义名字，否则会报错

系统变量中全局变量和会话变量其实是使用一套变量，不同的是会话变量仅当次会话生效

1、会话变量（会话变量仅当次会话生效）

2、全局变量（一直生效的变量）

1）会话变量的赋值：set @@ 变量名=值

set 变量名 = 值;  【比如常用的set names ="utf8";】

set @@变量名=值 //建议使用

2）全局变量的赋值：set global 变量名 = 值;

set global 变量名 = 值;

3）查看所有系统变量：show variables;

show variables;

4）查询系统变量 select @@ 变量名;

select @@变量名;

2、用户变量

用户变量就是用户自己定义的变量。 用户变量都是会话级的变量，仅在当次连接中生效。

1）定义用户变量：

系统为了区别系统变量跟用户变量，规定用户变量必须使用一个@符号，可以不定义直接使用，默认为null，比如存储过程的地方使用过

set @变量名=1 //建议使用
select @变量名 := 值;
select 值 into @变量名;


测试
set @setName = 'HealerJean' ;
select @setName := 'HealerJean 2' ;
select 1 into @setName ;

3）查询用户变量：select @setName ;

select @setName ;

3）局部变量：

由于局部变量是用户自定义的，可以认为局部变量也是用户变量【但有所不同，局部中不需要使用@】

用法：局部变量一般用在sql语句块中，比如存储过程块、触发器块等

4）局部变量的定义方法：

先使用 declare 声明局部变量,其中可选项 default 后面可以跟默认值：【非常重要的一步，不然会设置成用户变量】

不写 declare 会报错

示例：declare myq int;
示例：declare myq int default 666;

设置变量的值：

set 变量名= 值；

获取变量的值：

select 变量名;

举例：

函数
create function funParam(dept_name varchar(20)) returns int
  begin
    declare c int;
    set c = 1 ;
    return c ;
  end ;



  
存储过程
create procedure myset()
  begin
    declare mya int;
    declare myq int default 777;
    set myq=6;
    set mya=666;
    select mya,myq;
  end;
  
call myset() ;
 

​

三、存储过程和函数

区别：

1、调用方面：函数在 sql 语句中就可以使用（比如可以作为查询语句的一部分来调用），存储过程一般是独立执行的语句

2、返回结果方面：函数只能返回一个变量，存储过程可以返回多个变量、结果集

3、复杂性方面，存储过程的实现比较复杂一些，

1、函数

1）测试表

create table department (
  dept_id int(11) default 0 comment '部门id',
  dept_name varchar(20) default '' comment '部门名称'
)comment ='部门' ;

INSERT INTO department (dept_id, dept_name) VALUES (1, '广告部');
INSERT INTO department (dept_id, dept_name) VALUES (2, '媒体部');
INSERT INTO department (dept_id, dept_name) VALUES (3, '管理部');

2）创建函数

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

3）删除函数

drop function myselect2 ;

4）函数创建和使用

a、最简单的

create function myselect2() returns int return 666;


create function funSelectBySql() returns int
  begin
    declare c int;
    select dept_id from department where dept_id= 1 into c;
    return c;
  end;
  

mysql> create function myselect2() returns int return 666;

mysql> select  myselect2() ;
+-------------+
| myselect2() |
+-------------+
| 666         |
+-------------+
1 rows in set (0.01 sec)

sql> select funSelectBySql()

b、传入参数

create function funParam(dept_name varchar(20)) returns int
  begin
    declare c int;
    select d.dept_id from department  d where d.dept_name =dept_name into c;
    return c ;
  end ;

select funParam('广告部');

2、存储过程

1、返回数值得的存储过程，其执行完后返回一个值，例如数据库中执行一个有返回值的函数或命令。

2、返回记录集的存储过程:执行结果是一个记录集，例如，从数据库中检索出符合某一个或几个条件的记录。

3、行为存储过程，用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

1）in

限定这个参数是传值给存储过程,既然是传值，所以可以是变量或常量数据

注意：in 修饰的参数一般是传入存储过程中作为某些条件的，不会被存储过程修改

2）out

out：限定这个参数是存储过程传出的一个值，因为有值的返回，所以这个参数必须是一个变量

注意：存储过程中会给 out 修饰的变量赋值，使得过程外部可以获取这个更改的值

3）inout:inout

是上面两者的叠加,既可以被存储过程内部使用，又可以修改后被外部使用,因为有值的返回，所以这个参数必须是一个变量

-- 最简单的例子
create procedure myselect()
begin 
    select @@version;
end;

call myselect();


create procedure getInfo(in mname varchar(15))
begin 
select mname;
end;

call getInfo("lilie");



-- 能通过传参来获取指定内容的
create procedure getInfo2(in mname varchar(15))
begin 
select * from student where name =mname;
end;

call getInfo2("lilei");



-- 将结果赋值给一个变量传到外部
select * from coupon_adzone ;

create procedure getInfo3(in mname varchar(15),out oname varchar(15))
  begin
    select c.adzoneName from coupon_adzone c where c.adzoneName =mname into oname;
 #  select * from coupon_adzone ;
  end;

call getInfo3('藏宝阁',@oname );     
#不会在muysql执行器中返回数据，除非加上后面的 select * from coupon_adzone，可以返回数据
select @oname  ;

三、Mysql 慢查询

MySQL的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的 SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行 10S 以上的语句。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

1、慢查询日志相关参数

2、慢查询日志配置

1）查看慢查询是否开启以及日志目录

使用 set global slow_query_log = 1 开启了慢查询日志只对当前数据库生效，如果 MySQL重启后则会失效。

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。例如如下所示：

mysql>  show variables  like '%slow_query_log%';
+------------------------------------+-------------------------------+
| Variable_name                      | Value                         |
+------------------------------------+-------------------------------+
| slow_query_log                     | ON                            |
| slow_query_log_always_write_time   | 10.000000                     |
| slow_query_log_file                | /home/work/mysql/log/slow.log |
| slow_query_log_timestamp_always    | OFF                           |
| slow_query_log_timestamp_precision | second                        |
| slow_query_log_use_global_control  |                               |
+------------------------------------+-------------------------------+
6 rows in set (0.02 sec)

mysql> 

2）查看慢查询时间

默认情况 下 long_query_time的值为 10 秒，可以使用命令修改，也可以在 my.cnf 参数里面修改。关于运行时间正好等于 long_query_time 的情况，并不会被记录下来。也就是说，在 mysql源码里是判断大于long_query_time，而非大于等于。

从MySQL 5.1开始，long_query_time开始以微秒记录 SQL语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

mysql> show variables like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.200000 |
+-----------------+----------+
1 row in set (0.02 sec)

mysql> 

问题1：修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？

答案：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。

3、日志分析工具: mysqldumpslow

1）常用命令

a、得到返回记录集最多的 10个 SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

b、得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

c、得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

d、另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

四、常用解决问题的命令

1、information_schema

1)、查看正在执行的 sql, show full processlist

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc ;


show full processlist;

2）查看事务表 information_schema.innodb_trx;

innodb_trx         ## 当前运行的所有事务
innodb_locks       ## 当前出现的锁
innodb_lock_waits  ## 锁等待的对应关系

select * from information_schema.innodb_trx;

3）杀死线程Id

kill 29832；