# Day10 LOG 日志管理

# 日志管理-general_log

一般不开启,因为log的量非常大,个别情况下开启一会以供调试,如检测mysqldump备份过程

-- 查询与配置
show variables like 'general_log';       -- 查看日志是否开启 
show variables like 'general_log_file';  -- 看看日志文件保存位置 
show variables like 'log_output'; -- 看看日志输出类型 table或file 

set global general_log=on;          -- 开启日志功能 
set global general_log_file='/data/3306/log/general_log'; -- 设置日志文件保存位置 
set global log_output='table';      -- 设置输出类型为 table 
set global log_output='file';       -- 设置输出类型为 file 
set global log_output='table,file'; -- 设置多位置保存

# 日志管理-bin_log

记录系统变更类数据,二进制日志,可用于
- 备份恢复,如从某个时间点开始恢复
- 主从复制依赖该二进制文件
- 分析大事务

-- 配置重要参数
select @@log_bin;           -- 1
select @@log_bin_basename;  -- /data/3306/log/binlog
select @@server_id;         -- 51 v8.0要求
select @@binlog_format;     -- 比较重要
    - ROW
        * DDL 记录语句 create database oooo;
        * DML 记录数据 insertupdatedelete 
    - STATEMENT   语句 random
    - MIXED
select @@sync_binlog;       -- 统一刷新到磁盘事务的个数:0表示先刷到OS缓存,由OS控制;1或大于1表示事务的个数就开始刷
  • 日志的查看
-- 操作系统
ls -l /data/3306/log

-- 所有的log文件
mysql> show binary logs;

-- 当前在用,反应当前文件偏移量
mysql> show master status;
+---------------+----------+
| File          | Position |
+---------------+----------+
| binlog.000012 |      503 |
+---------------+----------+

# event  --> 记录单元
# 每个event都有开始和结束位置,用Position来标识
-- 查看日志的起点和终点
show binlog events in 'binlog.000012';
create database oooo;
create table t1(id int);
insert into t1 vlaues(1);
show binlog events in 'binlog.000012';

-- OS解码查看日志信息
mysqlbinlog --base64-output=decode-rows -vv binlog.000012

mysqlbinlog 参数说明
--start-datetime='2022-01-01 00:00:00'
--stop-datetime='2022-01-01 00:00:01'
--start-position=1
--stop-position=100 
--base64-output=decode-rows -vv
  • 日志截取
-- 备份
mysqlbinlog --base64-output=decode-rows -vv --start-position=2185 --stop-position=2374 binlog.000012 > /tmp/bin.sql

-- 恢复
set sql_log_bin=0;
source /tmp/bin.sql;
set sql_log_bin=1;

-- 截取日志的痛点
1. 日志跨度大,量大             解决:备份+日志
2. 跨多个文件                  解决:GTID,全局事务唯一号,按事务编号截取
3. 日志记录了所有库表的日志      解决:my2sql基于库表过滤
  • GTID
对一个已提交事务的编号,并且是一个全局唯一的编号,它的官方定义如下:
GTID = server_uuid:transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:1-1000

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| dd07cc10-4d07-11ed-9e7c-00163e160f39 |
+--------------------------------------+

> cat /data/3306/data/auto.cnf
server-uuid=dd07cc10-4d07-11ed-9e7c-00163e160f39


vim /etc/my.cnf
gtid-mode=on                    -- 开启
enforce-gtid-consistency=true   -- 强制一致性,主从中
log-slave-updates=1             -- 主从中,强制写入从库bin_log

- 重启服务 修改数据
mysql> create database gtid;
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000013 |      341 |              |                  | dd07cc10-4d07-11ed-9e7c-00163e160f39:1 |
+---------------+----------+--------------+------------------+----------------------------------------+

具备GTID后,截取查看某些事务日志: 
mysqlbinlog \
--include-gtids='dd07cc10-4d07-11ed-9e7c-00163e160f39:1' \
--exclude-gtids='dd07cc10-4d07-11ed-9e7c-00163e160f39:100' \
--skip-gtids \
binlog.000013 > /tmp/bin.sql

-- 恢复
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;
  • 删除日志
-- 自动清理日志
mysql> show variables like '%expire%';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_seconds     | 2592000 | -- v8.0 默认30天,推荐选项
| expire_logs_days               | 0       | -- v5.7 默认不清理
+--------------------------------+---------+

企业建议:至少保留两个全备周期+1的binlog

-- 删除日志
purge binary logs to 'binlog.000004';
reset master;  -- 重置,主从必崩,慎用
  • 轮动日志
show binary logs;

1. 重启
2. mysql> flush logs;
3. 达到最大值
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+

# 日志管理-slog_log

记录慢SQL语句的日志,定位低效的SQL语句

# 开关
slow_query_log=ON
# 文件位置及名字
slow_query_log_file=/data/3306/log/slow_log
# 设定慢查询时间
long_query_time=0.5
没走索引的语句也记录,最大1000条
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000
  • 慢语句分析
# -s c 排序 按照次数,-t 取top数
mysqldumpslow -s c -t 10 /data/3306/log/slow_log


# pt-query-digest
## 安装依赖
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL

## 源码下载解压
wget https://downloads.percona.com/downloads/percona-distribution-mysql-ps/percona-distribution-mysql-ps-8.0.26/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz

vim /etc/profile
export PATH=/usr/local/percona/bin:$PATH
source /etc/profile

## 异常 you may need to install the Time::HiRes module
yum install perl-Time-HiRes;

## 分析日志
pt-query-digest slow_log

## 图形化 anemometer + pt-query-digest
上次更新: 10/31/2022, 5:19:53 PM