MySQL日常管理 Part 3：日志篇

MySQL提供了诸多种类的日志，帮助DBA获得数据库运行状况。

MySQL日志类型	说明
error log	数据库启动、运行、停止时产生该日志
general query log	client执行语句时产生该日志
binary log	数据库内容发生变更时产生该日志，主从复制也通过binarylog来实现
relay log	从库上收到主库的数据更新时产生该日志
slow query log	SQL语句在数据库查询超过指定时间时产生该日志
metadata log	执行DDL语句操作metadata时产生该日志

默认情况，上述日志都处于非激活状态。激活日志时，所有日志的目录都在数据文件目录下。

1 Error Log

        MySQL的error log 记录服务进程mysqld启动、关闭、运行过程中的错误信息

1.1 Error Log 配置

        MySQL的error log是由mysqld和mysqld_safe产生

--- my.cnf中配置mysqld_safe模块

[mysqld_safe]

log-error=/app/mysql5.7/logs/ocean.err

--- 启动mysql服务时使用"--log-error"参数

mysqld_safe --log-error=/app/mysql5.7/logs/ocean.err &

--- 查看errorlog配置情况

SHOW VARIABLES LIKE 'log_error%';

--- 查看errorlog内容

tail -50f /app/mysql5.7/logs/ocean.err

1.2 日志轮询

        通过flush-logs刷新日志

--- 归档历史日志

mv ocean.err ocean_$(date +%F).err

--- 刷新日志

mysqladmin flush-logs

2 General Query Log

        MySQL的general query log记录client连接信息、执行语句信息

--- my.cnf中配置mysqld_safe模块

[mysqld_safe]

general_log_file=/app/mysql5.7/logs/ocean.log

--- 查看general query log配置

SHOW VARIABLES LIKE 'general_log%';

--- 临时激活general query log

SET GLOBAL general_log = on;

注：general query log建议关闭，日志信息量大，易造成磁盘I/O性能问题。

3 Binary Log

        MySQL的binarylog记录DML和DDL语句

3.1 Binary Log配置

--- my.cnf中配置mysqld_safe模块

[mysqld_safe]

log_bin = /app/mysql5.7/logs/ocean-bin
log_bin_index = /app/mysql5.7/logs/node1-bin.index
binlog_format = 'ROW'

--- 查看bin-log配置

SHOW VARIABLES LIKE '%log_bin';

--- session级别，临时停止记录bin_log

SET SESSION sql_log_bin = OFF;

--- 查看bin-log位置点

SHOW BINARY LOGS;

SHOW MASTER STATUS;

--- 筛选binlog文件，查看binlog记录

SYSTEM MYSQLBINLOG ocean-bin.000006 | grep "ocean"

--- session级别，临时开启记录bin_log

SET SESSION sql_log_bin= ON;

3.2 Binary Log刷新

• 数据库重启会刷新binlog，生成新binlog文件
• mysqldump -F 、mysqladmin flush-logs 执行这两条命令会刷新binlog
• 配置binlog文件上限，" show variables like 'max_binlog_size';"

3.3 Binary Log索引文件

        Binary Log索引文件记录binlog文件列表

--- 查看binlog索引文件

show variables like 'log_bin_index';

--- 查看binlog索引文件内容

less /app/mysql5.7/logs/ocean-bin.index

3.4 Binary Log日常维护

        Binary log非常重要，不能随意清除

--- 配置参数自动清除binlog

vi my.cnf

expire_logs_days = NUM

SHOW VARIABLES LIKE 'expire_logs_days';

SET GLOBAL expire_logs_days = NUM;

--- 删除指定位置之前的日志

SHOW BINARY LOGS;

PURGE BINARY LOGS TO 'ocean-bin.000008';

--- 删除指定时间之前的日志

ls -l --time-style=long-iso ocean-bin*

PURGE BINARY LOGS BEFORE '2019-01-01 00:00';

--- 删除所有日志

RESET MASTER;

        常用的binary log参数

参数
binlog_cache_size	该参数控制客户链接分配的内存空间，事务引擎的性能优化参数。默认是32768
max_binlog_size	该参数控制日志容量上限，默认1GB
sync_binlog	该参数控制日志落盘频次。 sync_binlog=0表示事务提交后，不会将cache中的数据落盘，而是文件系统进行同步。（默认值） sync_binlog=n表示每n次事务提交之后，cache中的数据落盘。

        Binary log的模式

模式	说明	优势	短板
STATEMENT	记录每一条被修改数据的SQL语句	磁盘IO低，性能高	记录执行的SQL，特殊SQL无法在从库上正确执行，导致数据不一致
ROW	记录每一行被修改数据的SQL语句	记录数据修改，主从架构的数据一致性好	binary log的量非常大，占用惊人的磁盘空间
MIXED	默认为statement，一些特殊情况下切换至row

注：mixed-based切换条件

• 函数中出现UUID()
• 表的AUTO_INCREMENT更新
• 执行trigger或function
• 执行FOUND_ROWS()、 ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER

--- 永久生效

vi my.cnf

binlog_format = 'STATEMENT | ROW | MIXED'

mysqld restart

--- 临时配置模式

SET GLOBAL binlog_format = 'STATEMENT';

SET GLOBAL binlog_format = 'ROW';

SET GLOBAL binlog_format = 'MIXED';

4 Slow Query Log

        记录执行时间超出阈值（long_query_time）的SQL语句

4.1 Slow Query Log相关参数

参数	说明
slow_query_log	慢查询 ON | OFF，默认OFF
slow_query_log_file	慢查询日志文件，hostname-slow.log
long_query_time	慢查询阈值，默认10s
log_queries_not_using_indexes	慢查询不使用索引的SQL，默认OFF
min_examined_row_limit	结果集大于N行的SQL，默认 0
log_slow_admin_statements	管理的慢查询SQL
log_throttle_queries_not_using_indexes	限制每分钟记录慢查询SQL的数量，默认 0（无限制）

4.2 生产环境示例

--- 开启慢查询

slow_query_log = ON

--- 大于5秒的SQL

long_query_time = 5

--- 未使用索引的SQL

log_queries_not_using_indexes = ON

--- 慢查询日志

slow_query_log_file = /app/mysql5.7/logs/ocean-slow.log

--- 结果集超过500行

min_examined_row_limit = 500

4.3 Slow Query Log刷新

        生产环境中可以使用crontab+scripts来切割日志，便于日志分析

#!/bin/bash
#Author:OceAn
export PATH=/app/mysql5.7/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /app/mysql5.7/logs &&\
mv ocean-slow.log ocean-slow.log.(date +%F) &&\
mysqladmin flush-log


#cut slow log by OceAn at 20190401
0 0 * * * /bin/sh /opt/scripts/cut_slow_log.sh > /dev/null 2>&1

4.4 慢查询分析工具

        慢查询日志分析工具有很多，MySQL官方提供了mysqldumpslow，也有很不错的第三方分析工具mysqlsla、pt-query-diges、myprofi、mysql-explain-slow-log、mysqllogfilter等。下面介绍一下mysqlsla。

--- 获得软件包

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz 

mysqlsla-2.03.tar.gz

--- 安装mysqlsla

yum install -y perl-devel perl-DBI perl-DBD-MySQL

rpm -qa | grep perl-devel perl-DBI perl-DBD-MySQL

tar -zxvf mysqlsla-2.03.tar.gz

cd mysqlsla-2.03

perl Makefile.PL

make

make install

--- mysqlsla分析慢查询

/usr/local/bin/mysqlsla -lt slow /app/mysql5.7/logs/ocean-slow.log.20190401 > /opt/result/ocean-slow-result.log

--- 生产环境中的案例

#!/bin/bash
#Author:OceAn
export PATH=/app/mysql5.7/bin:/sbin:/bin:/usr/sbin:/usr/bin
Date=`date +%F -d -1day`

#analyze slow log
Time=`date +%F`
Path=/usr/local/bin/mysqlsla
cd /app/mysql5.7 &&\
$Path/mysqlsla -lt slow ocean-slow.log.$Date > analyze_slow_$Date.log 2>&1

--- 配置crontab

#analyze slow log by OceAn at 20190401
0 0 * * * /bin/sh /opt/scripts/slow_log_analyze.sh > /dev/null 2>&1

        当然还可以使用时下非常流行的ELK来收集慢查询日志，生成可视化展示界面。博友们有兴趣可以深入研究。

 

相关链接

MySQL日常管理 Part 1：基础篇

MySQL日常管理 Part 2：字符集

 

参考《MySQL Server Logs》