第六章 -- 日志管理
错误日志
作用：
记录MySQL 启动及工作过程中，状态信息/报错/警告
错误日志配置
默认在 /数据路径下/hostname.err
查看错误日志文件位置
select @@log_error;
show variables like 'log_error';
手工设定
vim /etc/my.cnf
···
[mysqld]
log_error=xxxx
'''
如何查看错误日志
主要关注 [ERROR]，看上下文
二进制日志(binlog)
作用
- 数据恢复必备的日志
- 主从复制依赖的日志
binlog 相关查看命令
## 查看binlog是否开启
select @@log_bin;
## 查看日志路径及前缀名
select @@log_bin_basename;
## 服务ID号
select @@server_id;
## 二进制日志格式
select @@binlog_format;
## 控制mysql怎么刷新二进制日志
select @@sync_binlog;
binlog配置
#1.修改配置文件（5.7 版本必须加 server_id）
vim /etc/my.cnf
server-id=6
log_bin=/data/binlog/mysql-bin
-- /data/binlog 存放路径
-- mysql-bin 文件名前缀
-- 日志目录注意权限问题
#2.创建目录并授权
mkdir -p /data/binlog
chown mysql.mysql /data/binlog
#3.重启数据库
/etc/init.d/mysql restart
二进制(binlog)日志记录了什么？
引入
binlog是SQL层的功能。
记录了MySQL数据库所有变更类的SQL语句
记录语句的种类
- DDL(数据定义)
- DCL(数据控制)
- DML(数据操作)
记录SQL语句种类
DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML
DML三种记录方式
-
SBR：statement 5.6 默认
- 做什么记录什么
- 记录的就是SQL语句,可读性较强，日志量相对较少，日志记录可能会不准确
-
RBR：row 5.7默认
- 默认模式，推荐
- 记录数据行的变化
- 可读性不好，日志量大，日志记录准确
-
MRB：mixed
- 自动判断记录模式
binlog events(二进制日志事件)
简介
二进制日志内容以事件为最小记录单元.
- 对于DDL,DCL，一个语句就是一个事件(event)
- 对于DML(标准的事务语句)： 只记录已提交的事务的DML语句
例如以下列子,就被分为了4个event
begin; 事件1
DML1 事件2
DML2 事件3
commit; 事件4
事件的构成
mysqlbinlog mysql-bin.000001
# at 219 -- 事件开始的标识(position)
end_log_pos 319 -- 事件结束的标识(position)
#190814 18:46:35 -- 事件发生的时间
create database oldboy -- 事件内容
二进制日志的基本查看
查看二进制日志的配置信息，
mysql[(none)]>show variables like '%log_bin%';
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| sql_log_bin | ON |
查看当前一共有多少个binlog日志
show binary logs;
查看当前正在使用的二进制文件
mysql[(none)]>show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 319 | | | |
+--------------+----------+--------------+------------------+-------------------+
--- file：当前MySQL正在使用的文件名
--- Position: 最后一个事件的结束位置号
查看二进制日志的事件信息
oldguo[(none)]>show binlog events in 'mysql-bin.000002';
Log_name：binlog文件名
Pos：开始的position *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
日志内容查看和截取
内容查看命令
mysqlbinlog /data/binlog/mysql-bin.000003
mysqlbinlog --base64-output=decode-rows -v /data/binlog/mysql-bin.000003
日志的截取(基于Position号)
核心：找到截取的起点和终点
mysqlbinlog --start-position=xxx --stop-position=xxx /data/binlog/mysql-bin.000005>/data/bin.sql
--start-position 事件起始号(位置)
--stop-position 事件终止号(位置)
日志截取测试
使用binlog日志进行数据恢复
1）准备环境
oldguo[(none)]>create database binlog charset utf8mb4;
oldguo[(none)]>use binlog;
oldguo[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;
oldguo[binlog]>insert into t1 values(1),(2),(3);
oldguo[binlog]>commit;
oldguo[binlog]>insert into t1 values(11),(12),(13);
oldguo[binlog]>commit;
oldguo[binlog]>update t1 set id=10 where id>10;
oldguo[binlog]>commit;
oldguo[binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
(2) 模拟库丢失
oldguo[binlog]>drop database binlog
(3) 确认起点和终点
mysql[(none)]>show master status;
[(none)]>show binlog events in 'mysql-bin.000004';
(4) 恢复
mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
mysql -uroot -p
[(none)]>set sql_Log_bin=0;
[(none)]>source /tmp/bin.sql
2.8 基于gtid的binlog管理
2.8.1 引入
5.6版本以后，binlog加入了行的日志记录方式,GTID。
- 简化binlog 的截取，提供在主从复制中的高级功能
5.7版本中做了加强。
- 主从性能，高可用环境，集群
2.8.2 什么是GTID ？(Global Transaction ID)
是一个已提交事务的编号，事务的唯一编号，并且是一个全局唯一。
GITD 具幂等性
GTID 会和事务一同会记录到 binlog 中，用来标识事务。
它的官方定义：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
--- server_uuid 来源于 auto.cnf
--- GTID 在一组复制中，全局唯一
GTID - 配置
写入my.cnf 文件
gtid_mode=on -- GTID 开关配置
enforce_gtid_consistency=true -- 强制GTID一致性
log_slave_updates -- 主从复制中从库记录binlog，并统一GTID信息
查看gtid日志信息
DDL 一个操作就是一个GTID
DCL 一个完整的事务是一个GTID
基于gtid截取日志
--include-gtids= 截取的的事务
--exclude-gtids= 跳过的事务
--skip-gtids
**截取1-3号事务:**
mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql
**截取 1-10 gtid事务,跳过6号和8号事务.**
mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10' --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8' /data/binlog/mysql-bin.000009>/data/gtid.sql
2.8.6 截取演练
1）准备环境
db03 [(none)]>create database test charset utf8mb4;
db03 [(none)]>use test;
db03 [test]>insert into t1 values(1),(2),(3);
db03 [test]>commit;
db03 [test]>insert into t1 values(11),(22),(33);
db03 [test]>>commit;
db03 [test]>>select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
2）模拟故障
db03 [(none)]>drop database test;
3）找起点和终点(gtid)
查找到在使用的binlog日志
db03 [(none)]>show master status;
查看binlog 日志内容，找到创库到删库之前的GTID号
db03 [(none)]>show binlog events in 'mysql-bin.000010';
dc89e6d3-c262-11e9-8ead-000c2917182a:2-5
4）截取日志(仅供参考)
mysqlbinlog --skip-gtids --include-gtids='dc89e6d3-c262-11e9-8ead-000c2917182a:2-5' /data/binlog/mysql-bin.000010 >/data/gtid.sql
5）数据恢复
进入数据库
db03 [test]>set sql_log_bin=0;
db03 [test]>source /data/gtid.sql
db03 [test]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
二进制日志其他操作
自动清理日志
show variables like '%expire%';
expire_logs_days 0
临时
推荐自动清理时间,是要按照全备周期+1
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.000009';
注意:不要手工 rm binlog文件
my.cnf binlog关闭掉,启动数据库
把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志*reset master; 主从关系中，主库执行此操作，主从环境必崩
binlog的滚动机制
- flush logs;
- 重启mysql也会自动滚动一个新的
- 日志文件达到指定大小，默认1G(max_binlog_size)
- select @@max_binlog_size (查看设置的大小)
- 备份是，加入参数也可以自动新的
慢日志( slow_log )
简介
记录运行较慢的语句记录slowlog中
功能是辅助优化的工具日志
应激性的慢 ----> show processlist;
一段时间慢 ----> slow记录,统计
配置
查看慢日志状态
show variables like '%slow%';
select @@long_query_time;
show variables like '%not_using_indexes%';
配置文件中永久设置
vim /etc/my.cnf
# 慢日志状态
slow_query_log=1
# 文件存放位置与名字
slow_query_log_file=/data/3306/data/db01-slow.log
# 设定慢查询时间(为了模拟)
long_query_time=0.1
# 没走索引的语句也记录
log_queries_not_using_indexes
慢语句模拟
set sql_log_bin=0;
source /tmp/t100w.sql;
set sql_log_bin=1;
select * from
分析处理慢语句
mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log
-t 显示条数
-s 以什么方式排序
常用子参数：
c: 相同查询的查询条数和从大到小排序。
t: 以查询总时间的方式从大到小排序。
扩展
pt-query-digfest
集成：pt-query-digest+Anemometer=WEB方式分析(慢日志,二进制日志,错误日志)