java 数据库 日志管理_数据库学习之十：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