数据库学习之十：mysql日志管理

十、mysql日志管理

课程大纲

1、MySQL日志类型简介

2、MySQL错误日志配置及查看

3、MySQL binlog介绍及管理实战

4、MySQL 慢查询日志设置及管理实战

1、日志的类型简介

mysql> show variables like '%log_error%'；在配置文件中指定错误日志位置。

mysql> show variables like '%gen%'; 一般日志查询

二进制日志，记录修改记录。

日志文件	选项	文件名	程序
表名称
错误	--log-error	host_name.err	N/A
常规	--general_log	host_name.log，general_log	N/A
慢速查询	--slow_query_log --long_query_time	host_name-slow. log，slow_log	mysqldumpslow
二进制	--log-bin --expire-logs-days	host_name-bin.000001	mysqlbinlog
审计	--audit_log--audit_log_file...	audit.log	N/A

2、MySQL错误日志配置及查看

错误日志：
配置方法:
[mysqld]
log-error=/data/mysql/mysql.log
查看配置方式：
mysql> show variables like '%log%error%';
作用：
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
一般查询日志：
配置方法:
[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log
查看配置方式：
show variables like '%gen%';
作用：
记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

3、MySQL binlog介绍及管理实战

1、二进制日志都记录了什么？
已提交的数据记录，以event的形式记录到二进制文件中
2、二进制记录格式有哪些？
row：行模式，即数据行的变化过程，如将Age=19修改成Age=20的过程事件。(一般都是应用这个模式)
statement：语句模式，如将update语句进行记录。
mixed：以上两者的混合模式。
3、三总模式有什么优缺点？
4、binlog的作用
备份恢复、复制

二进制日志管理：

1、开启二进制日志
set sql_log_bin=0    -->在会话级别修改为临时关闭
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  -->在全局打开binlog

2、设置二进制日志记录格式（建议是ROW）：
配置文件中修改：
binlog-format=ROW
命令行修改
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';    
3、查看binlog设置
show variables like '%binlog%';

查询二进制日志方法：
mysql> show variables like 'binlog';
mysql> show binary logs;
mysql> show master status; 最近一次的，当前正在使用的二进制日志
mysql> show binlog events in 'mysql-bin.000014';
可以通过linux命令行中进程查询：
[root@centos6-kvm3 mysql]# pwd
/application/mysql/data/mysql
[root@centos6-kvm3 data]# mysqlbinlog mysql-bin.000014 #真正二进制日志内容
详细的查询：日志内容分析
[root@centos6-kvm3 data]# mysqlbinlog --help | more
[root@centos6-kvm3 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000014
根据需求截取二进制日志：
[root@centos6-kvm3 data]# mysqlbinlog --start-position=1077  --stop-position=1119 mysql-bin.000014
----
二进制日志
-----
mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog

vim /etc/my.cnf
log-bin=/data/binlog
binlog_format=row
sync_binlog=1

show binary logs;
show binlog events in 'my-bin.000002'
show master status;
截取获取二进制日志，进行恢复：
mysqlbinlog  --base64-output=decode-rows -v my-bin.000002  #日志内容分析
mysqlbinlog --start-position=120 --stop-position=721   my-bin.000002 #截取二进制日志
mysqlbinlog --start-position=340 --stop-position=721   my-bin.000002 >/tmp/binlog.sql
source /tmp/binlog.sql
二进制日志管理：
刷新二进制日志
flush logs
截取二进制日志
mysqlbinlog --start-position=  --stop-position=    >a.sql 
问题：
1、什么是事件？
2、什么是position	?	

1、通过截取binlog恢复损坏数据
mysqbinlog --start-position=340 --stop-position=721 my-bin.000002 >/tmp/binlog.sql
2、二进制日志翻转实现闪回数据（扩展）
source /tmp/binlog.sql

删除二进制：
默认情况下，不会删除旧的日志文件。
根据存在时间删除日志：
SET GLOBAL expire_logs_days = 7;
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
reset master;#重启启用一个二进制文件。

4、慢日志管理：

功能：
slow-log，记录所有条件内的慢的SQL语句
优化的一种工具日志。帮我们定位问题。
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

慢日志设置：
long_query_time    ：   设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log      ：  指定是否开启慢查询日志
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

配置例子，添加在配置文件中:
slow_query_log=1 
slow_query_log_file=/data/slow/slow.log
long_query_time=0.5
log_queries_not_using_indexes
	
处理慢日志：
mysqldumpslow命令

mysqldumpslow -s c -t 10 /data/slow/slow.log
mysqldumpslow -s at -t 10 /data/slow/slow.log

这会输出记录次数最多的10条SQL语句，
其中：
-s
是表示按照何种方式排序
c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar，表示相应的倒叙。
-t
是top n的意思，即为返回前面多少条的数据；

----------自己扩展工具
pt-query-diagest    percona-toolkit
mysqlsla