linux运维、架构之路-MySQL日志(三)

一、MySQL日志

1、错误日志

①配置方法

[mysqld]
log-error=/data/mysql/mysql.log

②查看配置方式

mysql> show variables like '%log%error%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| binlog_error_action | IGNORE_ERROR                     |
| log_error           | /application/mysql/data/db01.err |
+---------------------+----------------------------------+
2 rows in set (0.00 sec)

③作用

记录MySQL数据库的一般状态信息及报错信息，是数据库常规报错处理的常用日志

2、一般查询日志

①配置方法

[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log

②查看配置方式

mysql> show variables like '%gen%';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /application/mysql/data/db01.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)

③作用

记录所有的已执行成功的操作,可以作为审计功能,一般情况下不会开，除非特殊要求:ELK日志收集

3、二进制日志

①二进制三种模式

row:行模式，数据行的变化过程 #推荐的格式
statement:语句模式
mixed:以上两者混合模式　

②开启二进制日志

[mysqld]
log-bin=/application/mysql/data/mysql-bin

③设置二进制的日志格式

[mysqld]
binlog-format=row

命令行修改日志格式

set global binlog_format = 'ROW';

④查看binlog使用的日志格式

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | STATEMENT         |
| date_format              | %Y-%m-%d          |
| datetime_format          | %Y-%m-%d %H:%i:%s |
| default_week_format      | 0                 |
| innodb_file_format       | Antelope          |
| innodb_file_format_check | ON                |
| innodb_file_format_max   | Antelope          |
| time_format              | %H:%i:%s          |
+--------------------------+-------------------+
8 rows in set (0.00 sec)

⑤查看binlog设置

show variables like '%binlog%';

⑥查看binlog及位置点

show binary logs;
show master status;

⑦刷新binlog日志

mysqladmin flush logs

⑧截取binlog日志

mysqlbinlog --start-position=120 --stop-position=339 mysql-bin.000003 >/tmp/inc.sql

⑨使用source恢复

mysql> set sql_log_bin=0; #在当前恢复窗口临时关闭二进制日志记录。

⑩binlog日志作用

1、记录更改的二进制SQL语句
2、主从复制
3、增量数据备份　

4、二进制日志管理

二进制日志的删除,默认情况下，不会删除旧的日志文件

[mysqld]
SET GLOBAL expire_logs_days = 7;#保留7天

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

PURGE BINARY LOGS TO 'mysql-bin.000010';#根据文件名删除日志：
reset master;#清除binlog日志

5、慢查询日志管理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#不使用索引的慢查询日志是否记录到索引　　

③开启数据库的慢查询

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

④切割慢查询日志

cd /data/3306 &&\
/bin/mv show.log slow.log.$(date +%F) &&\
mysqladmin -uroot -p123456 flush-log #零点刷新

⑤慢查询分析工具

pt-query-digest