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

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

 

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