mysql_6 日志管理

mysql_6 日志管理

标签（空格分隔）： mysql

---

错误日志

作用

记录了mysql从启动以来，所有的状态，警告，错误。 帮组管理员定位 追踪数据库问题

配置方法

默认开启
位置 /数据文件夹/datadir/hostname.err

自定义 在 my.cnf 里面去写
log-error=/var/log/mariadb/mariadb.log

查看错误日志

[error] 包括error的

双一标准

innodb_flush_log_at_trx_commit=1 每次事务提交 比如log buffer 中 redo 落到磁盘
sync_binlog = 1 每次事务提交 必然保证binlog cache 中的日志落到磁盘

binlog二进制日志

作用：
    数据恢复，主从复制中应用

主要记录数据库变化(DDL,DCL,DML)性质的日志.是逻辑层性质日志。

@@log_bin
 如何开启
    默认：8.0版本以前没有开启
    8.0 之后 自动开启
 配置方法
    server_id=                          #服务 主机编号 binlog需要 主从需要
    log_bin = /data/binlog/mysql-bin    #日志位置+日志名前缀 列如： mysql-bin.000001 
    sync_binlog = 1                     #binlog 日志刷写的策略，双一中的第二个1.每次事务提交立即刷写binlog到磁盘。
    binlog_format=row                   #binlog的记录格式为row模式

binlog 记录的是变更sql语句，不记录查询语句

记录sql语句种类
    DDL 原封不动的记录当前DDL statement语句
    DCL 原封不动的记录当前DCL statement语句
    DML 只记录已经提交的事务DML insert update delete

DML三种记录方式

binlog_format 参数影响
1.statement 5.6默认 SBR 语句模式原封不动的记录当前DML
2.row    5.7      PBR       记录数据行的变换 用户看不懂 需要工具分析
3.mixed 混合 MBR 以上两种模式的混合

row 和 statement 对比

SBR 与 PBR模式的对比
statement 可读性比较高 日志量小 不够严谨
row       可读性比较小 日志量大 足够严谨

update t1 set xxx = xx where id > 100

为什么row模式严谨
id name intime
insert into t1 values(1,'zs',now())

row 记录了时间
statement 只是记录了函数

event 事件 是什么？

事件的简介
二进制日志的最小记录单元

对于DDL DCL 一个语句是就一个event
对于DML语句来讲 只记录已提交的事务

例如以下列子，就被分为了4个event
begin 120 -340
dml1 340- 460
dml2 460- 550
commit 550- 760

event 的组成

三部分构成：

• 时间的开始标识
• 时间内容
• 时间的结束标识

position
开始标识 at 194
结束标识 end_log_pos 254
194 254
某个事件在binlog中的相对位置号

位置号的作用是什么
为了方便我们去截取事件

binlog的查看

文件查看开启情况
select @@log_bin;
select @@log_bin_basename;

文件查看
/etc/my.cnf 写的文件夹 cd 进去 ls就可以看见了

二进制内置查看命令

查看目前有几个日志文件
show binary logs;
查看当前正在使用的日志文件
show master status;
查看二进制日志事件的功能

    +--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
| Log_name           | Pos | Event_type        | Server_id | End_log_pos | Info                                          |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
| mariadb-bin.000001 |   4 | Format_desc       |         1 |         256 | Server ver: 10.5.3-MariaDB-log, Binlog ver: 4 |
| mariadb-bin.000001 | 256 | Gtid_list         |         1 |         285 | []                                            |
| mariadb-bin.000001 | 285 | Binlog_checkpoint |         1 |         330 | mariadb-bin.000001                            |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
    

show binlog events in "前面命令（show master status）查询出来的值"

日志grep

mysql -u root -p -e "show binlog events in 'mysql-bin'" | grep DROP

binlog 如何打开

mysqlbinlog 位置 > /tmp/a.sql      变成sql


# at 219
时间 end_log_pos 338

# at 338


mysqlbinlog --base64-output=decode-rows -vvv 位置 > /tmp/a.sql      变成sql

日志截取回复

flush logs 刷新新的日志文件 用于写入日志 原来的日志就不适用了

通过日志数据恢复

show binlog events in 'mysql-bin'

binlog 维护操作
flush logs; #滚动一个新的日志
select @@max_binlog_size;
mysqladmin -u root -p flush-logs
mysqldump -F

日志的删除
    不要使用rm命令 删除日志
    自动删除机制
    select @@expire_logs_days;
    默认是0 单位是天 代表永不删除.

截取日志
    mysqlbinlog --start-position=开始点 --stop-position 结束点 /data/binlog/mysql-bin.00005 > /tmp/bin.sql

恢复日志
    set sql_log_bin=0
    source /tmp/bin.sql
    set sql_log_bin=1

问题：
    1.数据多了怎么办
    2.binlog记录的不仅仅是想要回复的库的操作记录，还有其他库的操作。
         mysqlbinlog -d bindb 指定 只需要的库
    3.数据库创建了几年，bin_log 不够怎么办 
        
    4.binlog 有删数据数据的操作
    5.断断续续的截取
    
    6.需要的日志文件在多个文件中分布
        起点 加入在1号 mysql-bin.00001    4600
        终点 一般是最后一个文件 10号  mysql-bin.000010 890
        
        mysqlbinlog --start-datetime= --stop-datetime=  mysql-bin.00001 mysql-bin.00002
        
    7.数据库创建了几年，bin_log 太多了 数据也多
        假设： 每周六做全备份 binlog 每天备份2次。 
        
        binlog 实际上使我们数据恢复时 配合备份一起恢复数据的手段
        
        所以还是需要备份手段的 可以使用docker 给快照 raid 硬件 。

如何恢复

1.滚动一个新的日志
2.分析binlog

确认起点 终点 找到你需要的
导出为sql
mysqlbinlog --start-position=开始点 --stop-position 结束点 /data/binlog/mysql-bin.00005 > /tmp/bin.sql

恢复日志
set sql_log_bin=0
source /tmp/bin.sql
set sql_log_bin=1

手动删除binlog

purge binary logs to 'mysql-bin.0000010';
purge binary logs before '2020-08-17'

全部binlog清空
reset master
主 从 比崩

查看锁情况

show engine innodb status
show variables like '%deadlock%'
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
得到 trx_id thread id 还有sql 开始解决死锁

show master status ;
查看binlog 地点

MGR PAXOS 分布式一致性协议
Redis 事务
mongodb replication set

如何查看binlog 变成sql

mysqlbinlog mysql-bin.0000002 > /tmp/a.sql
mysqlbinlog --base64-outpu=decode-rows -vvv mysql-bin.0000002 > /tmp/a.sql

mysqlbinlog --help

binlog 的GTID模式管理

GTID介绍

5.6版本 新加的特性 5.7 8.0中做了加强
5.6 中不开启 没有这个功能
5.7 中的GTID 即使不开也会有自动生成
set @@SESSION.GTID_NEXT = "anonymous"

是对于一个已提交事务的编号，并且是一个全局唯一的编号
他的官方定义:
GTID = server_uuid : transaction_id
7474848SERF-65E4-ERF4-1C16QWUTYJGH

重要参数介绍

vim /etc/my.cnf
gtid-mode= on
enforce-gtid-consistency = true

show master status
show binlog events in "binlog名字"

GTID 截取binlog

--include-gtids
--exclude-gitds
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" /data/binlog/mysql-bin.000510

截取多个文件
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql

source recover.sql

GTID的幂特性

开启GTID后 mysql恢复binlog时，重复的gtid的事务不会执行了
想要恢复加参数

--skip-gtids
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql
set sql_log_bin=0;
source recover.sql
set sql_log_bin=1;

mysqlbinlog --include-gtids= "xxx:gtid1-14" --exclude-gtids="xxx:gtid6-7" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql

slowlog 慢日志

作用

记录mysql运行过程中 较慢的语句
帮组我们进行语句优化的工具日志

如何配置

是否开启
select @@slow_query_log

存放的位置
select @@slow_query_log_file

多慢算满sql
select @@long_query_time

my.cnf 中写入

slow_query_log = 1
slow_query_log_file = /data/xxx/db1slow.log
long_query_time = 5
log_queries_not_using_indexes = 1

mysqldumpslow

mysqldumpslow
-s
-c 统计次数
-t 5 前5条

mysqldumpslow -s -c -t 5 /data/slow.log

扩展
可视化展示slow-log
pt-query-digest + Amemometer