mysql日志管理

错误日志

mysql> select @@log_error;

二进制日志

作用

记录MySQL运行过程中，变更类的语句（DDL，DML，DCL…）
可以用来做数据恢复和主从复制。

配置

mysql> select @@server_id;         # 主机ID，用来主从复制的不同节点
mysql> select @@log_bin;           # 二进制日志开关，也可以设置binlog存放位置和名称
mysql> select @@log_bin_basename;  # 设置binlog存放位置和名称，可省略
mysql> select @@binlog_format;     # 二进制日志记录格式。保持默认row即可。
mysql> select @@sync_binlog;       # binlog刷新策略。保持默认为1即可。

log_bin=/data/3306/binlog/mysql-bin #文件名称前缀

日志的格式

DDL 、DCL ： 记录MySQL语句
DML       ： insert、update、delete。
--- 1. 只会记录已经提交的事务语句。
--- 2. 记录格式受到binlog_format影响。
--- binlog_format=格式
--- RBR : 行模式。默认模式。row
--- SBR ：语句模式statement
--- MBR ：混合模式mixed

RBR和SBR区别

RBR 行模式记录binlog。记录的是9个数据行每行变化日志。日志量相对较大。记录准确。
SBR 语句模式记录binlog。记录的语句本身。SBR，日志量相对较小。记录可能不准确。

记录单元

event(事件)binlog 记录的最小单元。
DDL ， DCL ： 一条语句就是一个event。
DML        ： 一个事务型操作有多个event构成
begin;      e1
DML1        e2
DML2        e3
COMMIT;     e4

日志的查看

show binary logs; #查看日志的大小
max_binlog_size; #设置日志的大小
show master status ; #查看当前所在的日志
flush logs; #创建一个新日志，切换过去，重启也触发这个效果
show binlog events in 'mysql-bin.000003'; #查看日志里发生的事件，pos（position）标记

普通查看
mysqlbinlog /data/3306/binlog/mysql-bin.000004
基于row记录查看
mysqlbinlog --base64-output=decode-rows -v  /data/3306/binlog/mysql-bin.000004
过滤数据库
mysqlbinlog -d crm mysqld-bin.000001

日志的清理

PURGE BINARY LOGS TO ‘mysql-bin.000004’ 清理之前的日志
reset master 全部清理

日志实现恢复数据

1. 查看创建库的起点和删除库的起点

mysql -e "show binlog events in 'mysql-bin.000001';" |grep -Ei "drop database test|create database test"

2. 截取日志

mysqlbinlog --start-position=1085 --stop-position=1949 mysql-bin.000004  >/tmp/bin.sql

3. 恢复数据

mysql> set sql_log_bin=0; #恢复的操作不用写入日志
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

gtid

pos号的方式因为每个文件的pos号不是唯一的，如果文件过多，截取次数就会变多

所有GTID号码是从1开始有序生成的。
GTID模式下的日志，是具备幂等性的(重复的事情不会重复执行)。

server_uuid:ID
server_uuid介绍：
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 09a8c1af-6e45-11ea-a381-000c29248f69 |
+--------------------------------------+
在第一次初始化，启动数据库时，自动生成的。存在/data/3306/data/auto.cnf文件中。
一旦被删除，被修改，下次重启重置。

ID值的介绍： 
	DDL ，DML ：一个事件记录一个ID 
	DML       ：一个完整的事务，记录一个ID

开启方式

select @@gtid_mode;
select @@enforce_gtid_consistency; #幂等性

恢复数据

mysqlbinlog --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:1-3' /data/3306/binlog/mysql-bin.000007 >/tmp/gtid1.sql
#恢复1 2 3 id的事件，--skip-gtids忽略幂等性，每次执行都需要加上

截取时排除某些id

mysqlbinlog --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:1-10' --exclude-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:4','09a8c1af-6e45-11ea-a381-000c29248f69:7' /data/3306/binlog/mysql-bin.000007 >/tmp/gtid2.sql

多文件截取

mysqlbinlog --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:1-48' /data/3306/binlog/mysql-bin.000007 /data/3306/binlog/mysql-bin.000008>/tmp/gtid1.sql

慢日志

配置

mysql> select @@slow_query_log;
mysql> select @@slow_query_log_file;
mysql> select @@long_query_time; #执行的语句达到多少时间才记录，建议每月逐渐降低指标
mysql> select @log_queries_not_using_indexes; #不走索引的语句也记录

分析

mysqldumpslow  -s c -t 3 db01-slow.log
#s排序 c按照访问次数排序 t head的意思