06 : mysql 的 binlog 日志 和slow慢日志 详解

时间:2021-10-24 03:59:30

mysql 的 binlog 日志 和slow慢日志 详解

 

mysql一般常用的日志有三种:
1:error错误日志
2: binlog日志
3:slow日志

下面将详细解释这三种日志:

1、错误日志
记录MySQL启动或工作过程中,数据库状态信息,默认就是开启的,数据路径下$hostname.err。
也可以指定错误路径:
log_error=/var/log/mysql3306.log


2、二进制binlog日志
(1)他记录了什么?
记录了所有的数据库修改类的命令:
DDL
DCL
DML

(2)二进制日志记录格式:
DDL:直接以语句模式(statement)
DCL:直接以语句模式(statement)
DML:语句模式(statement)、行模式(rows,推荐模式)、混合模式(mixed)

注:对事务性语句,只会记录已提交事务的二进制日志。


(3)binlog的作用

备份 ---- 恢复
主从 ---- 复制

(4)二进制日志格式有哪些?
statement:语句模式
row:行模式,即数据行的变化过程
mixed:以上两者的混合模式。

我们企业推荐使用row模式,5.6中默认模式statement,5.7中默认row
两种模式有什么优缺点?
statement:
优点:简单明了,容易被看懂,就是sql语句,记录时需要更小的磁盘空间
缺点:记录不够严谨

row 模式:
优点:记录更加严谨
缺点:有可能需要更多的磁盘空间,不太容易读懂


(5)二进制日志配置(一般会和数据文件分开 )
mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql

/data/mysql要事先创建好,有权限。

vim /etc/my.cnf
log_bin=/data/mysql/mysql-bin #打开二进制日志,并设置位置和文件名前缀
binlog_format=row #二进制日志格式设置为row
重启MySQL生效。
/etc/init.d/mysqld restart

(6)binlog常用查询和使用命令:
ls -l /data/mysql/
查询什么?
二进制日志内容:
1、二进制日志对DDL、DCL以语句形式记录
2、DML语句记录是已经commit成功的事务(begin dml1 dml2 commit)
3、binlog以event为最小单元进行记录
对于DDL、DCL类语句,一条语句就是一个event(事件)
对于DML会将事务中每个涉及到的命令和变更,拆解为多个event来记录
二进制日志内容查看:
show binary logs; ---->看所有的二进制日志
show master status ; ---->看当前在使用的二进制日志文件
show binlog events in ‘mysql-bin.000001‘; ---->查看1号二进制日志事件信息
show binlog events in ‘mysql-bin.000004‘ limit 5;
看前五行(binlog里面一条语句和一条事务都是一个事件)
------
fulsh logs; 切割binlog日志
show variables like ‘binlog_format‘; 查看binlog日志格式
mysqlbinlog mysql-bin.log 查看binlog内容
示例:
mysql> create database ygirl;
mysql> show binlog events in ‘mysql-bin.000003‘;
mysql> create database ygirl1;
mysql> show binlog events in ‘mysql-bin.000003‘ limit 5;

二进制日志内容分析
二进制日志是以event方式记录。
event的生命周期 开始位置点 -------> 结束位置点
start-position stop-postion

导出binlog日志:
mysqlbinlog /data/mysql/mysql-bin.000003>/tmp/binlog.sql

将Row格式记录的DML语句进行翻译:
mysqlbinlog --base64-output=DECODE-ROWS -vvv /data/mysql/mysql-bin.000003

截取binlog,开始点和结束点。binlog 文件里面的at:23213 数字就是事件位置点
mysqlbinlog --start-postion=689470 --stop-postion=689835 /data/mysql/mysql-bin.000003


如何截取二进制日志内容:
案例:
1、备份二进制日志
flush logs; 刷新新的二进制日志
打包压缩备份不再被mysql使用的而进制日志

2、数据损坏恢复(对数据损坏,80%以上都是人为的)
(1)数据库损坏情况
物理损坏:
物理磁盘坏
格式化
rm -rf
mv
逻辑损坏:
drop
delete
update
truncate

(2)模拟删库,binlog恢复
drop database world;
只获取和world数据库有关的binlog语句
mysqlbinlog -d world --start-position=420 /data/mysql/mysql-bin.000003 >/tmp/world.sql
mysql> set sql_log_bin=0;(临时关闭当前连接窗口的二进制binlog记录)
mysql> source /tmp/world.sql 导入binlog
mysql> set sql_log_bin=1; (恢复binlog记录)
其实不设置也无所谓,窗口退出就失效了。

 

(7)二进制日志切割的几种方法:
1: flush logs; mysql命令行执行,刷新 binlog 切割。
2: 在[mysqld]节点中增加如下
max_binlog_size = 500M :bin log日志每达到设定大小后,会使用新的bin log日志。如mysql-bin.000002达到500M后,创建并使用mysql-bin.000003文件作为日志记录。
3: F 大F,需要在命令行执行

(8)二进制日志清理方法:
设置日志过期时间,自动清理策略
SET GLOBAL expire_logs_days = 7; 命令行生效

vim /etc/my.cnf
expire_logs_days = 7
注:按照备份策略进行清理,一般是按照全备的周期
一般企业都是保留近期两个完整的全备。所以过期时间至少设置为2个全备周期。
条件允许的话,能多保留则多保留。

根据天数清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

根据文件名删除日志
PURGE BINARY LOGS TO ‘mysql-bin.000010‘;

删除所有二进制日志,从000001开始重新记录。
reset master;

---------------------------------------
3、slow_log(慢日志)

(1)记录查询较慢SQL语句的日志文件。
(2)配置参数:
slow_query_log=1 #开启慢日志开关
slow_query_log_file=/data/mysql/slow.log #定义日志位置和名字
long_query_time=0.1 #定义慢查询时间阈值,超过0.1s的语句记录慢日志
log_queries_not_using_indexes #没走索引的查询,记录慢日志

重启mysql生效:

进入数据库:查看参数是否生效
mysql> show variables like ‘long_query_time‘;


(3)模拟慢查询语句
create table city1 select * from city; 把city表查询到的数据导入到新创建的city1里面
去查看slow.log 会发现里面有这条的记录

insert into city1 select * from city1; 把city1表查询到的数据插入到city1里面
insert into city1 select * from city1;
insert into city1 select * from city1;
commit; (我在配置文件里面关闭了自动事务提交,所以这边需要执行手动commit)
去查看slow.log 会发现里面有这条的记录


一堆查询:where条件 满足countrycode=‘CHN‘ 和 name=‘shanghai‘;
select * from city1 where countrycode=‘CHN‘ and name=‘shanghai‘;
select * from city1 where countrycode=‘CHN‘ and name=‘shanghai‘;
select * from city1 where countrycode=‘CHN‘ and name=‘shanghai‘;
因为没有索引,走的是全表扫描查询。所以耗时会长,表越大查询越慢
我们添加一下索引:
alter table city1 add index idx(countrycode,name);
在查询会发现快很多很多。
select * from city1 where countrycode=‘CHN‘ and name=‘shanghai‘;
查看详细的查询信息:是否走索引了
explain select * from city1 where countrycode=‘CHN‘ and name=‘shanghai‘;
----------------------------------------------------------------------------------
mysqldumpslow:命令

mysqldumpslow -s c -t 10 /data/mysql/slow.log 显示出top10个慢查询语句,
-s c 代表慢查询语句,按出现的次数排序
-t 10 代表top 前10个语句
mysqldumpslow -s t -t 10 /data/mysql/slow.log 按语句执行时间来排序,

-----------------------------------------
扩展分析slow日志工具:

yum -y install perl perl-devel perl-Time-HiRes perl-DBD-MySQL

然后去官网下载这个工具包,解压
tar xf percona-toolkit-2.2.20.tar.gz && cd percona-toolkit-2.2.20/bin
yum -y install perl-Digest-MD5
分析内容会很详细:
./pt-query-diagest /data/mysql/slow.log

#mysql自带的分析慢查询命令
mysqlslap

------------------慢 查询优化思路----------------------

解决的思路:
1、开启慢日志功能,设置慢日志记录标准,将慢查询时间定为0.1秒,将不走索引语句记录下来。
2、收集3天慢日志,进行分析
3、使用pt-query-diagest工具分析3天内的慢日志
4、找到运行次数较多并执行时间较长的SQL,找到此类语句5条。
5、按照一定的优先级,分析SQL语句的执行计划,其中3条,是语句不够规范导致的慢,将语句反馈给开发。
6、其中发现原因2条语句是因为索引建立不合理,进行索引调整。

--------------------附上/etc/my.cnf 目前配置----------------------

[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/data/mysql.sock
server_id=3306
port=3306
log_error=/application/mysql/data/mysql.log
log_bin=/application/mysql/data/mysql-bin
binlog_format=row
default-storage-engine=InnoDB   #使用innoDB引擎
autocommit=0                              # 关闭自动提交事务
max_binlog_size = 500M
expire_logs_days = 15
slow_query_log=1
slow_query_log_file=/application/mysql/data/slow.log
long_query_time=0.1
log_queries_not_using_indexes

 

[client]
socket=/application/mysql/data/mysql.sock

 

--------------------------------------------------------------------------------------------