日志管理

常用的日志

 

错误日志

作用

• 排查MySQL运行过程的故障.

查看

select @@log_error;

　　

配置

vim /etc/my.cnf
log_error=/tmp/mysql3306.log
systemctl restart mysqld

二进制日志(binlog)

作用

• (1) 主从要依赖二进制日志

• (2) 数据恢复时需要依赖于二进制日志

binlog配置 /etc/my.cnf

server_id=6
log_bin=/data/binlog/mysql-bi
binlog_format=row

说明:

• /data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限

• mysql-bin : 二进制日志文件名的前缀

• 例如: mysql-bin.000001 ,mysql-bin.000002 ......

• binlog_format=row ---> 5.7版本默认配置是row,可以省略.

创建目录和授权

mkdir -p /data/binlog/
chown -R mysql.mysql /data

重启生效

systemctl restart mysqld

binlog记录了什么

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

记录SQL语句种类

• DDL ：原封不动的记录当前

• DDL(statement语句方式)。

• DCL ：原封不动的记录当前DCL(statement语句方式)。

• DML ：只记录已经提交的事务DML

DML记录方式

(1) 他记录的已提交的事务
(2) DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
说明:
	statement:SBR,语句模式记录日志,做什么命令,记录什么命令.
	row		 :RBR,行模式,数据行的变化
	mixed	 :MBR,混合模式

SBR和RBR什么区别?怎么选择?（面试）

SBR:  可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
RBR:  可读性较弱,对于范围操作日志大,不会出现记录错误.
	  高可用环境中的新特性要依赖于RBR
我们公司对数据的严谨性要求较高,也用用到了新型的架构,所以选择RBR

event（事件）

事件的简介

二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

event的组成

三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么？
为了方便我们截取事件

日志文件查看

查看二进制日志位置

show variables like '%log_bin%';

查看所有已存在的二进制日志

show binary logs;

flush logs;
show binary logs;

查看正在使用的二进制日志

• file：当前MySQL正在使用的文件名

• Position：最后一个事件的结束位置号

show master status;

event查看

create database binlog charset utf8mb4;
use binlog
create table t1(id int);
insert into t1 values(1);
show binlog events in 'mysql-bi.000004';　

 说明

Log_name：binlog文件名
Pos：开始的position    *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
补充:
SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

binlog文件内容详细查看

mysqlbinlog mysql-bin.000004
mysqlbinlog --base64-output=decode-rows -vvv mysql-bi.000004
# 只查看某个库的二进制文件
mysqlbinlog -d haoge mysql-bi.000004

截取二进制日志

mysqlbinlog --start-position=219 --stop-position=335 /data/binlog/mysql-bi.000004 >/tmp/a.sql

案例：通过binlog恢复数据

模拟数据

create database haoge charset utf8mb4;
use haoge;
create table t1(id int);
insert into t1 values(1);
commit;

模拟故障

drop database haoge;

分析

# 确认使用的是哪一个日志
show master status;
# 查看事件
show binlog events in 'mysql-bi.000004';

截取binlog（命令行）

mysqlbinlog --start-position=823 --stop-position=1420 /data/binlog/mysql-bi.000004 >/tmp/bin.sql

恢复binlog

# 临时关闭恢复时产生的新日志
set sql_log_bin=0;
source /tmp/bin.sql
#改回来
set sql_log_bin=1;

binlog日志的GTID新特性

GTID介绍

• 对于binlog中的每一个事务,都会生成一个GTID号码

• DDL ,DCL 一个event就是一个事务,就会有一个GTID号.

• DML语句来讲,begin到commit,是一个事务,就是一个GTID号

GTID的组成

severi_uuid:TID
severi_uuid? 
cat /data/mysql/data/auto.cnf
TID是一个:自增长的数据,从1开始
server-uuid=245034f3-5dcd-11ea-b87b-00163e09c1f2:1-15　

GTID的幂等性

• 如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过会影响到binlog恢复和主从复制.

GTID的开启和配置

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld

查看GTID信息

create database gtid charset utf8mb4;
show master status;
use gtid;
create table t1(id int);
show master status;

insert into t1 values(1);
commit;
show master status;
drop database gtid;

 基于GTID,binlog恢复

截取日志

mysqlbinlog --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' /data/binlog/mysql-bi.000005 >/tmp/gtid.sql

 恢复

set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;

报错

为什么报错?

因为幂等性的检查,1-3事务已经做过了.

正确的做法

mysqlbinlog --skip-gtids --include-gtids='245034f3-5dcd-11ea-b87b-00163e09c1f2:1-3' /data/binlog/mysql-bi.000005 >/tmp/gtid.sql

恢复　

set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;
show databases;

GTID相关的参数

• --skip-gtids

• --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

• --exclude-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

二进制日志其他操作

自动清理日志

# 查看过期时间
show variables like '%expire%';

设置过期时间

# 临时生效
set global expire_logs_days=8;
永久生效:
my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog

手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
注意:不要手工 rm binlog文件 1. my.cnf binlog关闭掉,启动数据库 2.把数据库关闭,开启binlog,启动数据库 删除所有binlog,并从000001开始重新记录日志

reset master; 主从关系中，主库执行此操作，主从环境必崩　　

 日志是怎么滚动

flush logs; 
重启mysql也会自动滚动一个新的
日志文件达到1G大小(max_binlog_size)
| max_binlog_size                          | 1073741824     
备份时,加入参数也可以自动滚动

慢日志(slow-log)

作用

• 记录运行较慢的语句,优化过程中常用的工具日志.

配置参数说明

## 开关
slow_query_log=1 
## 文件位置及名字 
slow_query_log_file=/data/mysql/slow.log
## 设定慢查询时间
long_query_time=0.1
## 没走索引的语句也记录
log_queries_not_using_indexes

配置

vim /etc/my.cnf
slow_query_log=1 
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld

模拟慢查询

select * from city;
select * from city where name!='ds';

分析慢查询

mysqldumpslow参数说明

• -s排序

• -c数量

• -t top 获取前几个

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

第三方工具分析慢日志

https://www.percona.com/downloads/percona-toolkit/LATEST/
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
toolkit工具包中的命令:
pt-query-diagest  /data/mysql/slow.log