十、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
