标签 mysql 下的文章

mysql日志

错误日志

查看错误代码

[root@jin ~]# perror 16
OS error code  16:  Device or resource busy

通用查询日志

通常不开启,当需要采样分析时开启,分析结束记得要关闭,不然对服务器的性能会有影响

在mysql中直接设置
set global general_log=1;
修改mysql配置文件
[mysqld]
general-log-file[=path]
general-log=1

慢查询日志

当查询结果超过一定的时间没有返回结果时才会记录
可以查找出执行效率低的sql语句,为数据库优化提供帮助
默认不开启,需要采样分析时开启
采样分析结束后关闭
1.使用配置文件开启慢查询日志

#是否开启慢查询日志,(mysql中 _ 和 - 意义相同,slow_query_log 和 slow-query-log 意义相同)
slow_query_log=on|off
#指定日志文件路径,默认路径为数据库文件所在目录下,名称为hostname-slow.log
slow_query_log_file=path
#指定多少秒未返回结果的查询语句数据慢查询
long_query_time=2-
#记录所有没有使用到索引的查询语句
long-queries-not-using-indexes
#有查找多于了1000次而引发的慢查询,例如全表扫描之类的
min_examined_row_limit=1000
#记录慢的 optimize table, analyze table, alter table 语句
log-slow-admin-statements
# 记录由slave所产生的慢查询
log-slow-slave-statements

2.直接在mysql中修改

set @@global.slow_query_log=1;
#set global slow_query_log=1;#与上一行意义相同
set @@global.long_query_time=3; 

其他参数可以通过如下命令查阅

show variables like "%slow%";

慢查询分析工具

# mysql官方安装包里面就有
mysqldumpslow
# 第三方工具
mysqlsla
#percona-toolkit中的pt-query-digest

二进制日志

记录对数据发生或潜在发生更改的sql语句
二进制格式保存
用途广泛,包括,查看数据库变更历史,数据库增量备份,数据库灾难恢复,mysql赋值
1.在mysql中直接开启

set @@global.log_bin=1;
set @@global.binlog_size=37268;#单位 bytes
show variables like "%bin%";#查看其他相关参数

2.在配置文件中开启

#开启并指定二进制日志保存路径及文件名,默认值为 hostname-bin.xxx
log-bin=
#设置单个二进制日志文件的最大值,默认为1GB,最大为1GB
max-binlog-size=500m
#记录sql语句的方式,有三种STATEMENT,ROW,MIXED,默认为STATEMENT,STATEMENT性能较高,但某些情况可能导致主从服务器数据不同步,ROW能保证数据的一致性,但是性能较低,日志文件较大,MIXED是两种方式的混合,详情可查看 http://jinblog.com/archives/704.html
binlog_format=STATEMENT
#指定二进制日志文件记录哪些数据库的操作
binlog-do-db=test# 指定记录哪些库
bingo-ignore-db=test1# 指定忽略那些库
#二进制日志缓存大小
binlog-cache-size=100m
#每隔n秒将缓存中的二进制日志记录写回硬盘,默认为0。不过,你经常会陷入group commit函数与I/O之间二选一的矛盾(commit是并发的,而binlog的记录是串行的)。如果在replication环境中,由于考虑到耐久性和一致性,则需要设置为1,同时还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-xa=1(默认开启)
sync-binlog=n
暂停日志
set sql_log_bin=(0|1);
查看二进制日志
mysqlbinlog hostname-bin.000002
使用二进制日志恢复数据
mysqlbinlog host-bin.000002 | mysql

mysqlbinlog host-bin.000001 > backup.sql
mysqlbinlog host-bin.000002 >> backup.sql
mysql dbname < backup.sql
删除二进制日志

二进制日志会不断增长,并产生多个文件,因此需要制定备份计划和管理策略,无用的二进制日志文件需要及时删除
删除二进制日志的三种方法
1.操作系统直接删除二进制日志文件
2.reset master
3.purge

purge {master|binary} logs to 'log_name';
purge {master|binary} logs before 'date';

例子

# 删除000003之前的二进制日志文件(000001和000002)
purge master logs to 'host-bin.000003';

mysql权限管理

mysql权限

权限介绍

用户权限(user表)->数据库权限(db数据表)->主机权限(host表)->执行权限(procs_priv表)
用户权限(user表)->数据库权限(db数据表)->主机权限(host表)->表权限(tables_pri表)->列权限(columns_pri表)
和上面一样,从上到下,只要有一条符合就通过权限验证

用户(user)权限

记录允许连接到服务器的连接
全局权限控制

数据库(db表)权限

记录用户对数据库的操作权限

主机(host表)权限

记录某个主机对数据库的操作权限
配合db表可以做更加细致的控制

表(tables_priv)权限

用来设置用户对表的权限

列(columns_priv)权限

设置用户对列的权限

账户管理

grant与revoke

创建用户

创建方式1

create user 'username'@'host' [identified by 'PASSWORD'];#创建的用户不具有任何权限

创建方式2

grant <all|priv1,priv2,...privn> on [object]
[identified by 'password']
[with grant option];# 是否允许权限下放
object:table|function|procedure
grant option:
max_queries_per_hour count
max_updates_per_hour count
max_connections_per_hour count
max_user_connections count
创建用户并赋予权限
grant all on test.* 'jin'@'192.168.1.62' identified by '123456';#给予用户所有的权限
grant alter select,insert,update,delete,create,drop on test.* from 'jin'@'192.168.1.99';# 给予用户单个权限

删除用户

drop user 'jin'@'192.168.1.99';

查看用户权限

show grants for jin@192.168.1.99\G;#查看指定用户的权限
show grants;#查看自己的权限

刷新权限

在修改完用户的权限信息之后,需要刷新缓存才能生效

flush privileges;

添加用户权限

grant insert on test.t4 to jin@192.168.1.99;

移除用户权限

revoke insert on test.* from 'jin'@'192.168.1.99';
flush privileges;#刷新权限

设置用户密码

set password=password('123456');# 给用户自己设置密码
set password for jin@192.168.1.99=password('123456');#给指定用户设置密码
update mysql.user set password=password('12345') where User='jin' and Host='192.168.1.99'; #直接修改用户表,这个需要刷新权限菜能生效
mysqladmin -h 192.168.1.99 -ujin -p12345 password "123456"#使用mysqladmin命令工具改变密码

mysql存储过程

存储过程

存储过程优点

1.存储过程只在创建时编译,以后每次执行都不需要重新编译,而一般得sql语句每次执行都需要重新编译,所以使用存储过程可以提高数据库执行效率。
2.当对表进行复杂操作时(如对多个表进行update,insert,query,delete时),可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合使用,这些操作,如果用程序来完成就变成了一条条的sql语句,可能需要多次连接数据库,而使用存储过程,只需要连接一次数据库就可以了。
3.存储过程可以重复使用,可以减少数据库开发人员的工作量
4.安全性高,可以设定只有某用户才具有对指定存储过程的使用权

存储过程缺点

1.可移植性很差,不同的数据库不兼容
2.对于很简单的sql语句,存储过程没什么优势
3.使用存储过程不一定会减少网络传输,(存储过程的sql语句并不多,而且sql语句执行比较快,sql语句执行的频率也不大)
4.如果只有一个用户使用数据库,那么存储过程对于安全也没有什么影响
5.团队开发需要统一标准,否则后期维护是个麻烦
6.在大并发访问量的情况下,不宜写过多的设计运算的存储过程
7.业务逻辑复杂时,特别是涉及到对很大的表进行操作时,不如在前端先简化业务逻辑

互联网企业并发较大,不合适使用存储过程,内部使用的项目,并发较小,可以使用存储过程

创建存储过程

存储过程的参数

in参数的例子

in参数的特点;读取外部变量值,且有效范围仅限于存储过程内部

delimiter //
create procedure `p1`(in p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

执行结果

mysql> set @p=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> call p1(@p);
+------+
| p    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
out参数

out参数的特点在于,不读取外部变量值,在存储过程中执行完毕后保留新值

delimiter //
create procedure `p2`(out p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

执行结果

mysql> set @p=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p2(@p);
+------+
| p    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
inout参数

inout参数特点:读取外部变量值,在存储过程执行完毕之后保留新值

delimiter //
create procedure p3(inout p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

例子

mysql> set @p=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p3(@p);
+------+
| p    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
不带参数类型
delimiter //
create procedure `p5`(p int)
begin
    set @x=0;
    repeat
        insert into `t5` value (@x);
        set @x=@x+1;
        until @x>p
    end repeat;
end//
delimiter ;

变量定义

mysql中使用declare进行变量定义,语法

declare variable_name[,variable_name....] datetype [default value];

其中datetype为mysql的数据类型,例如int,float,date,varchar(lenght),例如

declare i int unsigned default 1000;
declare n number(8,2) default 10.11;
declare d date default '2016-10-27';
declare t datetime default '2016-10-27 13:12:08';
declare c varchar(88) default 'default string';

变量赋值

mysql中使用set命令对变量赋值,语法
set variable_name=value[,variable_name=value....]
注意,变量赋值是可以在不同存储过程中继承的

mysql> create procedure p6() set @last='last';
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p7() select concat('Last string is',@last);
Query OK, 0 rows affected (0.00 sec)

mysql> call p7;
+--------------------------------+
| concat('Last string is',@last) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p6;
Query OK, 0 rows affected (0.00 sec)

mysql> call p7;
+--------------------------------+
| concat('Last string is',@last) |
+--------------------------------+
| Last string islast             |
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

变量作用域

内部变量在其作用域内享有更高的优先权,当执行到end,内部变量消失时,此时已在其作用域外,变量不在可见了。因为在存储过程外再也找不到这个声明的变量,但是你可以通过out参数或者将其值指派给会话变量来保存其值

注释

"--" 单行注释
"/

...

/" 用于多行注释

循环语句以及流程控制语句

与触发器中的语法一致,可参考mysql触发器

查看存储过程状态

三种方式

mysql> show procedure status where Name='p7'\G;
*************************** 1. row ***************************
                  Db: test
                Name: p7
                Type: PROCEDURE
             Definer: root@%
            Modified: 2016-10-27 13:22:11
             Created: 2016-10-27 13:22:11
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create procedure p7\G;
*************************** 1. row ***************************
           Procedure: p7
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `p7`()
select concat('Last string is',@last)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> select * from information_schema.Routines where ROUTINE_NAME='p1' and ROUTINE_TYPE='PROCEDURE'\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: p1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: p1
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
    select p;
    set p=2;
    select p;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2016-10-27 11:34:14
            LAST_ALTERED: 2016-10-27 11:34:14
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

修改存储过程

mysql不支持修改存储过程中的代码,就不记录了

删除存储

drop procedure `p1`;

mysql自定义函数

自定义函数

创建函数

delimiter //
create function `myfunc`(num1 int,num2 int)
returns varchar(20)
begin
    if num1>num2 then
            return "num1 > num2";
        elseif num1<num2 then
            return "num1 < num2";
        else
            return "num1 = num2";
    end if;
    end//
delimiter ;

查看函数信息

mysql> show create function myfunc\G;
*************************** 1. row ***************************
            Function: myfunc
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`%` FUNCTION `myfunc`(num1 int,num2 int) RETURNS varchar(10) CHARSET latin1
begin
    if num1>num2 then
            return "num1 > num2";
        elseif num1<num2 then
            return "num1 < num2";
        else
            return "num1 = num2";
    end if;
    end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

mysql> show function status\G;
*************************** 1. row ***************************
                  Db: test
                Name: myfunc
                Type: FUNCTION
             Definer: root@%
            Modified: 2016-10-27 10:19:46
             Created: 2016-10-27 10:19:46
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

删除函数

drop function `myfunc`;