六mysql日志管理

青春须早为，岂能长少年。这篇文章主要讲述六mysql日志管理相关的知识，希望能为你提供帮助。
此处主要记录binlog日志、慢日志和错误日志，如果日志名称没注明，会使用mysql服务器的主机名
一、binlog日志
binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。
1、作用
(1)备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志
2、配置
[mysqld]
server_id=6
log_bin=/data/mysql/data/mysql-bin
binlog_format=row
重启生效

文章图片

3、binlog功能
binlog是SQL层的功能，记录的是变更SQL语句，不记录查询语句。
DDL、DCL、DML
DDL和DCL
以语句的方式，原样记录
DML记录格式(statement、row、mixed),通过binlog_format=row参数控制
(1)SBR:statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML
(2)RBR:row(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
(3)MBR:mixed（混合）MBR(mixed based replication)模式：以上两种模式的混合(由mysql自行判断使用哪种)
SBR和RBR区别
SBR：可读性高，日志量少(比如sql范围更新语句)，可能会产生错误数据(比如sql语句中带now()日期函数)
RBR：可读性差，日志量多，数据安全
数据库默认使用RBR
4、binlog基本操作
(1)查看binlog日志设置
show variables like \'%log_bin%\';

文章图片

(2)查看当前正在使用的binlog
show master status;

文章图片

(3)binlog日志文件切换
flush logs

文章图片

数据库重启
reset master #binlog日志文件名又会从1开始
binlog日志文件达到单个文件最大值
(4)查看binlog单个文件大小设置
show variables like \'max_binlog_size\';

文章图片

(5)binlog文件的清理
自动清理：
show variables like \'expire_logs_days\';

文章图片

0代表不清理，如果要设置，就设置全备周期+1的过期时间
手动清理：
purge binary logs to \'mysql-bin.000008\'; #会把mysql-bin.000008之前的清理掉，不会清理mysql-bin.000008
purge binary logs before \'2021-08-21 10:00:00\'; #根据时间清理
5、查看binlog事件
create database qingchen charset utf8mb4;
use qingchen
create table test1(id int,name varchar(10));
insert into test1 values (1,\'aa\'),(2,\'bb\');
show master status;

文章图片

show binlog events in \'mysql-bin.000001\';

文章图片

event(事件)二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin; 663- 742
DML1742- 805
DML2805- 856
commit; 856- 887
6、binlog文件内容详细查看
mysqlbinlog /data/mysql/data/mysql-bin.000001
mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/data/mysql-bin.000001 #比较友好一点
mysqlbinlog --start-datetime=\'2021-08-21 10:20:29\' --stop-datetime=\'2021-08-21 11:20:29\' /data/mysql/data/mysql-bin.000001
可以通过mysqlbinlog --help查看帮助命令
7、基于position进行日志截取和恢复
drop database qingchen;
show master status;

文章图片

show binlog events in \'mysql-bin.000001\';

文章图片

从创建数据库开始232到drop之前964
mysqlbinlog --start-position=232 --stop-position=964 /data/mysql/data/mysql-bin.000001 > /tmp/qingchen.sql
登录mysql
set sql_Log_bin=0; #暂时关闭binlog日志记录，不然恢复过程的事件也会写进binlog日志文件
source /tmp/qingchen.sql;
set sql_Log_bin=1;
8、binlog的GTID记录模式管理
是对于一个已提交事务的编号，并且是一个全局唯一的编号
对于binlog中的每一个事物都会生成一个GTID号码
DDL和DCL一个event就是一个事物，就会有一个GTID号码
DML语句，begin到commit是一个事物，就会有一个GTID号码
(1)配置
[mysqld]
gtid-mode=on
enforce-gtid-consistency=true #强制GTID一致性
重启生效
(2)查看binlog事件
create database gtid charset utf8mb4;
use gtid
create table test1(id int,name varchar(10));
insert into test1 values (1,\'aa\'),(2,\'bb\');
show master status;

文章图片

insert into test1 values (3,\'cc\');
insert into test1 values (4,\'dd\');
show master status;

文章图片

show binlog events in \'mysql-bin.000004\';

文章图片

(3)GTID日志截取
--include-gtids=\'d133a09b-f889-11eb-8854-000c2924e11c:1-5\'，\'d133a09b-f889-11eb-8854-000c2924e11c:8\' ##截取哪些
--exclude-gtids=\'d133a09b-f889-11eb-8854-000c2924e11c:6\'，\'d133a09b-f889-11eb-8854-000c2924e11c:9\'##排除哪些
GTID幂等性：
开启GTID后，如果拿有GTID的日志去恢复，检查当前系统中是否有相同GTID号，有相同的就会跳过，影响binlog操作和主从复制
需要备份的时候添加--skip-gtids参数
(4)基于GTID进行日志截取与恢复
create table test2(id int);
insert into test1 values (5,\'ee\');

文章图片

文章图片

drop database gtid;

文章图片

文章图片

现在要恢复gtid库和test1表的所有数据，不需要test2
需要gtid 1-7，不包含6
mysqlbinlog --skip-gtids --include-gtids=\'d133a09b-f889-11eb-8854-000c2924e11c:1-7\' --exclude-gtids=\'d133a09b-f889-11eb-8854-000c2924e11c:6\'
/data/mysql/data/mysql-bin.000004 > /tmp/gtid.sql
mysql登录
set sql_Log_bin=0;
source /tmp/gtid.sql;
set sql_Log_bin=1;
二、慢日志
记录慢SQL语句的日志,定位低效SQL语句的工具日志
1、配置
[mysqld]
slow_query_log=1#开启慢日志
slow_query_log_file=/data/mysql/data/slow.log
long_query_time=0.1 #设置慢查询时间
log_queries_not_using_indexes #设置不走索引的语句也记录
重启生效
2、分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/data/slow.log

文章图片

三、错误日志
记录启动\\关闭\\日常运行过程中,状态信息,警告,错误
1、配置
[mysqld]
log_error=/var/log/mysql.log
log_timestamps=system #log_timestamps控制记录日志显示时间的参数
重启生效
show variables like \'log_error\';
2、日志内容查看
【六mysql日志管理】主要关注[ERROR],看上下文