mysql建立 t日志_MySQL日志管理

最新推荐文章于 2024-07-10 10:16:49 发布

wzluo09

最新推荐文章于 2024-07-10 10:16:49 发布

阅读量188

点赞数

文章标签： mysql建立 t日志

本文链接：https://blog.csdn.net/weixin_33241808/article/details/114346326

版权

第1章错误日志

1.1 作用

记录启动\关闭\日常运行过程中,状态信息,警告,错误

1.2错误的日志配置

1.2.1 说明

默认是开启的：/数据路径/hostname.err

通常和数据分开放(存入日志的路劲一定要有权限)

1.2.2 自定义错误日志

vim /etc/my.cnf

log_error=/data/error/mysql.log

log_timestamps=system

#重启数据库生效

1.3 如何查看日志内容

查看上下文，主要观察[error]的信息

第2章二进制日志

1.1 作用

(1)备份恢复必须依赖二进制日志

(2)主从环境必须依赖二进制日志

1.2 binlog配置参数

1.2.1 说明

1. 5.7版本server_id必须要加

2. MySQL默认是没有开启二进制日志的。

1.2.2 基础参数查看

--1.查看二进制日志是否开启:

[(none)]>select @@log_bin;

0:表示没有开启

1:表示开启了二进制日志

--2.日志路径及名字

[(none)]>select @@log_bin_basename;

--3.服务器ID号:

[(none)]>select @@server_id;

--4.二进制日志格式:

[(none)]>select @@binlog_format;

--5.双一标准之二:

[(none)]>select @@sync_binlog;

1.3 binlog二进制配置

1.3.1 第一个里程:创建存放的日志的目录并授权

mkdir -p /data/binlog

chown -R mysql.mysql /data/binlog

1.3.2 第二个里程:修改配置文件

vim /etc/my.cnf

加入参数

log_bin=1 #此参数在5.7开始可以不用写，默认就是=1

log_bin=/data/binlog/mysql-bin

binlog_format=row #此参数也可以不用写,默认就是row模式

1.3.3 第三个里程: 重启数据库

systemctl restart mysqld

1.4 参数说明

1.4.1 server_id=3306

主要是在主从复制过程中必须要加的,但是在5.7版本中,要开启二进制日志即使是单机也是必加的

1.4.2 log_bin=/data/binlog/mysql-bin

/data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限

mysql-bin : 二进制日志文件名的前缀 (例如: mysql-bin.000001 ,mysql-bin.000002 ......)

1.4.3 binlog_format=row

二进制日志记录的模式

#此参数也可以不用写,默认就是row模式

第3章 binlog记录的是什么

1.1 引入

binlog是SQL层的功能，记录的是变更的SQL语句，但是不记录查询语句

1.2 记录的语句种类

DDL ：原封不动的记录当前DDL(statement语句方式)。

DCL ：原封不动的记录当前DCL(statement语句方式)。

DML ：只记录已经提交的事务DML

1.3 DML记录的三种方式

1.3.1 说明

binlog_format(binlog的记录格式)参数影响{这个参数对于DDL和DCL语句不管设置为什么模式都是没有影响的}

1.3.2 第一种方式：statement

1. 5.6版本默认使用的模式

2. SBR(statement based replication) ：语句模式原封不动的记录当前DML。

1.3.3 第二种方式：ROW

1. 5.7版本默认使用的模式

2. RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)

1.3.4 第三种方式：mixed

1.混合模式

2. MBR(mixed based replication)模式：以上两种模式的混合

#这种模式基本上不用

1.4 SBR与RBR的区别

1.4.1 2者区别文字解释

SBR: 日志量少，可读性高，但是不够严谨

RBR: 日志量多，可读性第，但是够严谨

1.4.2 为什么说RBR模式严谨?

1. 例如有一张表

id name intime

2. 假设我们在10：00整插入了一行数据

insert into t1 values(1,'zs',now( ))

3. 在10:05分的时候数据库突然坏了，要恢复数据

#第一种情况：用的statement模式

1. 这种模式只记录了insert into t1 values(1,'zs',now( ))。这条语句！而且时间是10：00钟的。

2. 此时恢复数据的话，时间那一列恢复到的时间为10:05分跟原来的时间10:00对不上

#第二种情况:用的是RBR模式

1. 这种模式记录的是数据行的变化，记录了10:00中的变化

2. 此时恢复数据，可以恢复到10:00那个时刻的状态

我们建议使用：row记录模式

第4章 event 是什么

1.1 event简介

二进制日志的最小记录单元

对于DDL,DCL,一个语句就是一个event

对于DML语句来讲:只记录已提交的事务。

例如以下例子,就被分为了4个event

begin; 120 - 340

DML1 340 - 460

DML2 460 - 550

commit; 550 - 760

补充：如果要截取一个事务，必须是从begin到commit.才算一个完整的事务

1.2 event的组成

三部分构成:

(1) 事件的开始标识

(2) 事件内容

(3) 事件的结束标识

Position:

开始标识: at 194

结束标识: end_log_pos 254

#补充：一个事件和下一个事件号码是连续的(上一个结束就是下一个开始)

194? 254?

某个事件在binlog中的相对位置号

位置号的作用是什么？

为了方便我们截取事件

第5章二进制日志管理

1.1 查看二进制日志是否开启

mysql> select @@log_bin;

+-----------+

| @@log_bin |

+-----------+

| 1 |

1：表示开启了

0：表示未开启

1.2 查看二进制日志文件位置

mysql> select @@log_bin_basename;

+------------------------+

| @@log_bin_basename |

+------------------------+

| /data/binlog/mysql-bin |

+------------------------+

1.3 查看所有已存在的二进制文件

mysql> show binary logs;

1.4 查看正在使用的二进制文件

--说明MySQL只能使用一个二进制文件

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 414 | | | |

+------------------+----------+--------------+------------------+-------------------+

--参数说明

File:表示使用的二进制文件名

Position:表示最后一个事件结束的位置号

1.5 查看二进制事件

1.5.1 第一步：先确定正在使用的是哪一个日志文件

mysql>show master status;

1.5.2 第二步：再查看事件

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

mysql> show binlog events in 'mysql-bin.000004' limit 5;(看前5行)

1.6 查看二进制内容

[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000004

二进制是按照事件最小单元去记录的，(从一个at到下一个at就是一个事件)可以看到里面创建库和表的语句是直接能看懂的

#说明

当我们查看DML语句时可以看到里面的操作我们根本就看不懂[这就是RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)

[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004

(大概翻译成我们能看懂的)

#在二进制文件里产看某个库下有什么操作

[root@db01 binlog]# mysqlbinlog -d yfc mysql-bin.000004

1.7 截取二级制日志

[root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql

从哪里开始到哪里结束，然后在导出到一个文件，这样我们就可以恢复数据了

第6章通过binlog恢复数据流程

1.1 环境准备

mysql> create database yfc charset utf8mb4;

mysql> use yfc

mysql> create table t1(id int,name varchar(20));

mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c');

mysql> commit;

1.2 模拟故障

mysql> drop database yfc;

1.3 恢复流程

1.3.1 第一个里程：分析和截取二进制日志

1. 查看正在使用哪一个二进制日志

mysql> show master status;

2. 查看事件信息(找到起点和终点)

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

把事件截取出来，导出到文件

[root@db02 ~]# mysqlbinlog --start-position=820 --stop-position=1439 /data/binlog/mysql-bin.000001 >/tmp/a.sql

1.3.2第二个里程：恢复数据

mysql> set sql_log_bin=0;

#临时关闭恢复时产生的新日志(因为运行SQL语句必定会产生日志，而这些语句又是从binlog里面拿出来的，所以没必要产生日志，同时也提高了性能)

mysql> source /tmp/a.sql

mysql> set sql_log_bin=1;

#改回来这一步一定要记住

第7章 binlog的GTID新特性

1.1 GTID介绍

5.6 版本新加的特性,5.7中做了加强

5.6 中不开启,没有这个功能.

5.7 中的GTID,即使不开也会有自动生成

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

对于binlog中的每一个事务,都会生成一个GTID号码

DDL ,DCL 一个event就是一个事务,就会有一个GTID号.

DML语句来讲,begin到commit,是一个事务,就是一个GTID号

1.2 GTID的组成

severi_uuid:TID

第一部分：sever_uuid:

第二部分：TID

============

sever_uuid:

#初始化完成第一次启动数据库就会生成(如果删除了，重启的时候，会生成一个新的！但是，不要乱删除)

#所在路劲信息：

[root@db01 /data/mysql/data]# cat auto.cnf

[auto]

server-uuid=e93904b6-cef6-11e9-8d9b-000c29d4e216

=======

#TID是一个自增长的数字，从1开始

1.3 GTID的幂等性

1. 如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过

2. 会影响到binlog恢复和主从复制

3. 开启了GTID，数据库后续的二进制日志都会记录GTID号码，但是之前的是不会记录的

1.4 GTID的开启和配置

vim /etc/my.cnf

gtid-mode=on

enforce-gtid-consistency=true

#说明

GTID开启，一定要是RBR模式

第8章基于GTID的恢复

1.1 模拟数据

mysql> create database yu charset utf8mb4;

mysql> create table t1(id int,name varchar(20));

mysql> insert into t1 values(1,"a"),(2,"b"),(3,"c");

mysql> commit;

1.2 故障模拟

mysql> drop database yu;

1.3 恢复流程

1.3.1 分析和截取二进制日志

1. 先查看正在使用的二进制日志

mysql> show master status;

2. 查看事件(找到起点和终点进行截取)

cd /data/binlog

#把事件截取出来，导出到文件

mysqlbinlog --skip-gtids --include-gtids='be150990-174c-11ea-9182-000c29191628:1-3' mysql-bin.000007 >/tmp/gtid.sql

#说明

--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

1.3.2 恢复数据

mysql> set sql_log_bin=0;

mysql> source /tmp/gtid.sql

mysql> set sql_log_bin=1;

1.3.3 验证数据

mysql> show databases;

mysql> use yu

mysql> select * from t1;

1.4 GTID相关参数

1.4.1 --skip-gtids

在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

1.4.2 --include-gtids

--include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

#表示包含6和8

--include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6-8'

#表示6到8

1.4.3 --exclude-gtids

--exclude-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

(排除掉哪个)

1.4.4 说明

开启了GTID就算用position去恢复数据也要加参数--skip-gtids

第9章二进制日志的其他操作

1.1 自动清理日志

参数为

expire_logs_days

自动清理时间,是要按照全备周期+1

set global expire_logs_days=8;

永久生效:

my.cnf

expire_logs_days=15;

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

1.2 手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

PURGE BINARY LOGS TO 'mysql-bin.000010';

#注意:不要手工 rm binlog文件

1. my.cnf binlog关闭掉,启动数据库

2.把数据库关闭,开启binlog,启动数据库

删除所有binlog,并从000001开始重新记录日志

*****************警告*****************************

reset master; 主从关系中，主库执行此操作，主从环境必崩

**************************************************

1.3 二进制日志是怎么滚动的

flush logs;

重启mysql也会自动滚动一个新的

日志文件达到1G大小(max_binlog_size)

| max_binlog_size | 1073741824

备份时,加入参数也可以自动滚动

#说明

max_binlog_size= 512M

指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除

第10章 slow log(慢日志)

1.1 作用

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

1.2 开启慢日志

vim /etc/my.cnf

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

long_query_time=0.1

log_queries_not_using_indexes

#参数说明

开关:

slow_query_log=1

文件位置及名字

slow_query_log_file=/data/mysql/slow.log

设定慢查询时间:

long_query_time=0.1

没走索引的语句也记录:

log_queries_not_using_indexes

重启生效

systemctl restart mysqld

1.3 MySQL自带的慢日志分析工具(mysqldumpslow)

mysqldumpslow -s -c -t 10 /data/mysql/slow.log

#参数说明

-s(sort):表示排序

-c(count):表示次数

-t(top):前十个

1.4 第三方工具

下载地址

https://www.percona.com/downloads/percona-toolkit/LATEST/

系统要安装依赖包

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 -y

toolkit工具包中的命令:

pt-query-digest /data/mysql/slow.log

Anemometer基于pt-query-digest将MySQL慢查询可视化

第11章 binlog 日志突然爆增怎么分析

1.1 场景：日志突然增大，分析为什么？

进入到二进制目录或者是写绝对路劲

[root@db02 binlog]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000021| awk '/UPDATE|INSERT|DELETE/{gsub("###","");gsub("INSERT.*INTO","INSERT");gsub("DELETE.*FROM","DELETE");count[$1" "$2]++}END{for(i in count)print i,"\t",count[i]}' |sort -k3nr|head -n 10